OSDN > Entwickler > fujii_masao > Kammer > pg_dbms_stats > Commit

pg_dbms_stats
Fork

Fork Herkunft: pgdbmsstats / pg_dbms_stats

R/O
HTTP
SSH
HTTPS

Commit

Commit MetaInfo

Revision	733cff0c96574fd87b6a14fd009a1a7226ef73b6 (tree)
Zeit	2016-01-15 16:57:01
Autor	Kyotaro Horiguchi <horiguchi.kyotaro@lab....>
Commiter	Kyotaro Horiguchi

Log Message

Support PostgreSQL 9.5.0.

Added support for PostgreSQL 9.5.0.

Ändern Zusammenfassung

modified: Makefile (diff)
add: SPECS/pg_dbms_stats95.spec (diff)
add: doc/export_effective_stats-9.5.sql.sample (diff)
add: doc/export_plain_stats-9.5.sql.sample (diff)
add: ext_scripts/pg_dbms_stats--1.3.6-9.5.sql (diff)

Diff

--- a/Makefile

+++ b/Makefile

		@@ -1,7 +1,7 @@
1	1	# pg_dbms_stats/Makefile
2	2
3	3	DBMSSTATSVER = 1.3.6
4		-PGVERS = 91 92 93 94
	4	+PGVERS = 91 92 93 94 95
5	5	IS_PRE_95 = $(filter 0,$(shell test "$(MAJORVERSION)" \< "9.5"; echo $$?))
6	6
7	7	MODULE_big = pg_dbms_stats

--- /dev/null

+++ b/SPECS/pg_dbms_stats95.spec

		@@ -0,0 +1,82 @@
	1	+# SPEC file for pg_dbms_stats95
	2	+# Copyright(C) 2012-2016 NIPPON TELEGRAPH AND TELEPHONE CORPORATION
	3	+
	4	+%define _pgdir /usr/pgsql-9.5
	5	+%define _bindir %{_pgdir}/bin
	6	+%define _libdir %{_pgdir}/lib
	7	+%define _datadir %{_pgdir}/share
	8	+%define _docdir /usr/share/doc/pgsql
	9	+%if "%(echo ${MAKE_ROOT})" != ""
	10	+ %define _rpmdir %(echo ${MAKE_ROOT})/RPMS
	11	+ %define _sourcedir %(echo ${MAKE_ROOT})
	12	+%endif
	13	+
	14	+## Set general information for pg_dbms_stats.
	15	+Summary: Plan Stabilizer for PostgreSQL 9.5
	16	+Name: pg_dbms_stats95
	17	+Version: 1.3.6
	18	+Release: 1%{?dist}
	19	+License: BSD
	20	+Group: Applications/Databases
	21	+Source: %{name}-%{version}.tar.gz
	22	+URL: http://sourceforge.jp/projects/pgdbmsstats/
	23	+BuildRoot: %{buildroot}
	24	+Vendor: NIPPON TELEGRAPH AND TELEPHONE CORPORATION
	25	+
	26	+## postgresql-devel package required
	27	+BuildRequires: postgresql95-devel
	28	+Requires: postgresql95-libs
	29	+
	30	+## Description for "pg_dbms_stats"
	31	+%description
	32	+pg_dbms_stats disguises database statistics with a prevously taken
	33	+snapshot so that the planner won't change its behavior even after
	34	+ANALYZE updates the statistics.
	35	+
	36	+pg_dbms_stats also provides following features:
	37	+ - backup multiple generations of planner statistics to reuse plans afterwards
	38	+ - import planner statistics from another system for tuning or testing.
	39	+
	40	+Note that this package is available for only PostgreSQL 9.5.
	41	+
	42	+## pre work for build pg_dbms_stats
	43	+%prep
	44	+PATH=/usr/pgsql-9.5/bin:$PATH
	45	+if [ ! -d %{_rpmdir} ]; then mkdir -p %{_rpmdir}; fi
	46	+%setup -q
	47	+
	48	+## Set variables for build environment
	49	+%build
	50	+PATH=/usr/pgsql-9.5/bin:$PATH
	51	+make USE_PGXS=1 %{?_smp_mflags}
	52	+
	53	+## Set variables for install
	54	+%install
	55	+rm -rf %{buildroot}
	56	+install -d %{buildroot}%{_libdir}
	57	+install -m 755 pg_dbms_stats.so %{buildroot}%{_libdir}/pg_dbms_stats.so
	58	+install -d %{buildroot}%{_datadir}/extension
	59	+install -m 644 pg_dbms_stats--1.3.6.sql %{buildroot}%{_datadir}/extension/pg_dbms_stats--1.3.6.sql
	60	+install -m 644 pg_dbms_stats.control %{buildroot}%{_datadir}/extension/pg_dbms_stats.control
	61	+install -d %{buildroot}%{_docdir}/extension
	62	+install -m 644 doc/export_effective_stats-9.5.sql.sample %{buildroot}%{_docdir}/extension/export_effective_stats-9.5.sql.sample
	63	+install -m 644 doc/export_plain_stats-9.5.sql.sample %{buildroot}%{_docdir}/extension/export_plain_stats-9.5.sql.sample
	64	+
	65	+%clean
	66	+rm -rf %{buildroot}
	67	+
	68	+%files
	69	+%defattr(0755,root,root)
	70	+%{_libdir}/pg_dbms_stats.so
	71	+%defattr(0644,root,root)
	72	+%{_datadir}/extension/pg_dbms_stats--1.3.6.sql
	73	+%{_datadir}/extension/pg_dbms_stats.control
	74	+%{_docdir}/extension/export_effective_stats-9.5.sql.sample
	75	+%{_docdir}/extension/export_plain_stats-9.5.sql.sample
	76	+
	77	+# History of pg_dbms_stats.
	78	+%changelog
	79	+* Mon Jan 14 2016 Kyotaro Horiguchi
	80	+- pg_dbms_stats95 v1.3.6 release
	81	+
	82	+

--- /dev/null

+++ b/doc/export_effective_stats-9.5.sql.sample

		@@ -0,0 +1,68 @@
	1	+/*
	2	+ * If you want the statistics of per-relation or per-column, please modify
	3	+ * nspname, relname, and attname in 'WHERE' clause.
	4	+ */
	5	+
	6	+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	7	+/*
	8	+ * If you want to chage the output file name, please modify the following name.
	9	+ */
	10	+\o export_stats.dmp
	11	+
	12	+COPY (SELECT n2.nspname,
	13	+ cl.relname,
	14	+ r.relpages,
	15	+ r.reltuples,
	16	+ r.relallvisible,
	17	+ r.curpages,
	18	+ r.last_analyze,
	19	+ r.last_autoanalyze,
	20	+ COALESCE(a.attname, ''),
	21	+ n1.nspname AS schemaname_of_atttype,
	22	+ t.typname,
	23	+ a.atttypmod,
	24	+ co.stainherit,
	25	+ co.stanullfrac,
	26	+ co.stawidth,
	27	+ co.stadistinct,
	28	+ co.stakind1,
	29	+ co.stakind2,
	30	+ co.stakind3,
	31	+ co.stakind4,
	32	+ co.stakind5,
	33	+ co.staop1,
	34	+ co.staop2,
	35	+ co.staop3,
	36	+ co.staop4,
	37	+ co.staop5,
	38	+ co.stanumbers1,
	39	+ co.stanumbers2,
	40	+ co.stanumbers3,
	41	+ co.stanumbers4,
	42	+ co.stanumbers5,
	43	+ co.stavalues1,
	44	+ co.stavalues2,
	45	+ co.stavalues3,
	46	+ co.stavalues4,
	47	+ co.stavalues5
	48	+ FROM dbms_stats.column_stats_effective co
	49	+ JOIN pg_attribute a
	50	+ ON (co.starelid = a.attrelid AND co.staattnum = a.attnum)
	51	+ JOIN pg_type t
	52	+ ON a.atttypid = t.oid
	53	+ JOIN pg_namespace n1
	54	+ ON t.typnamespace = n1.oid
	55	+ RIGHT JOIN dbms_stats.relation_stats_effective r
	56	+ ON co.starelid = r.relid
	57	+ JOIN pg_catalog.pg_class cl
	58	+ ON r.relid = cl.oid
	59	+ JOIN pg_catalog.pg_namespace n2
	60	+ ON cl.relnamespace = n2.oid
	61	+ -- WHERE n2.nspname = 'public'
	62	+ -- AND cl.relname = 'test'
	63	+ -- AND a.attname = 'id'
	64	+ ORDER BY starelid, staattnum)
	65	+TO STDOUT
	66	+(FORMAT 'binary');
	67	+\o
	68	+COMMIT;

--- /dev/null

+++ b/doc/export_plain_stats-9.5.sql.sample

		@@ -0,0 +1,74 @@
	1	+/*
	2	+ * If you want the statistics of per-relation or per-column, please modify
	3	+ * nspname, relname, and attname in 'WHERE' clause.
	4	+ */
	5	+
	6	+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	7	+/*
	8	+ * If you want to chage the output file name, please modify the following name.
	9	+ */
	10	+\o export_stats.dmp
	11	+
	12	+COPY (SELECT n2.nspname,
	13	+ c.relname,
	14	+ c.relpages,
	15	+ c.reltuples,
	16	+ c.relallvisible,
	17	+ (pg_relation_size(c.oid) / current_setting('block_size')::int4)::int4
	18	+ AS curpages,
	19	+ pg_catalog.pg_stat_get_last_analyze_time(c.oid)
	20	+ AS last_analyze,
	21	+ pg_catalog.pg_stat_get_last_autoanalyze_time(c.oid)
	22	+ AS last_autoanalyze,
	23	+ COALESCE(a.attname, ''),
	24	+ n1.nspname AS schemaname_of_atttype,
	25	+ t.typname,
	26	+ a.atttypmod,
	27	+ s.stainherit,
	28	+ s.stanullfrac,
	29	+ s.stawidth,
	30	+ s.stadistinct,
	31	+ s.stakind1,
	32	+ s.stakind2,
	33	+ s.stakind3,
	34	+ s.stakind4,
	35	+ s.stakind5,
	36	+ s.staop1,
	37	+ s.staop2,
	38	+ s.staop3,
	39	+ s.staop4,
	40	+ s.staop5,
	41	+ s.stanumbers1,
	42	+ s.stanumbers2,
	43	+ s.stanumbers3,
	44	+ s.stanumbers4,
	45	+ s.stanumbers5,
	46	+ s.stavalues1,
	47	+ s.stavalues2,
	48	+ s.stavalues3,
	49	+ s.stavalues4,
	50	+ s.stavalues5
	51	+ FROM pg_statistic s
	52	+ JOIN pg_attribute a
	53	+ ON (s.starelid = a.attrelid AND s.staattnum = a.attnum)
	54	+ JOIN pg_type t
	55	+ ON a.atttypid = t.oid
	56	+ JOIN pg_namespace n1
	57	+ ON t.typnamespace = n1.oid
	58	+ RIGHT JOIN pg_catalog.pg_class c
	59	+ ON s.starelid = c.oid
	60	+ JOIN pg_catalog.pg_namespace n2
	61	+ ON c.relnamespace = n2.oid
	62	+ WHERE c.relkind IN ('r', 'i', 'f', 'm')
	63	+ AND NOT n2.nspname IN ('pg_catalog',
	64	+ 'pg_toast',
	65	+ 'information_schema',
	66	+ 'dbms_stats')
	67	+ -- AND n2.nspname = 'public'
	68	+ -- AND c.relname = 'test'
	69	+ -- AND a.attname = 'id'
	70	+ ORDER BY starelid, staattnum)
	71	+TO STDOUT
	72	+(FORMAT 'binary');
	73	+\o
	74	+COMMIT;

--- /dev/null

+++ b/ext_scripts/pg_dbms_stats--1.3.6-9.5.sql

		@@ -0,0 +1,1507 @@
	1	+/* pg_dbms_stats/pg_dbms_stats--1.3.5.sql */
	2	+
	3	+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
	4	+\echo Use "CREATE EXTENSION pg_dbms_stats" to load this file. \quit
	5	+
	6	+-- define alias of anyarray type because parser does not allow to use
	7	+-- anyarray in type definitions.
	8	+--
	9	+CREATE FUNCTION dbms_stats.anyarray_in(cstring) RETURNS dbms_stats.anyarray
	10	+ AS 'anyarray_in' LANGUAGE internal STRICT IMMUTABLE;
	11	+CREATE FUNCTION dbms_stats.anyarray_out(dbms_stats.anyarray) RETURNS cstring
	12	+ AS 'anyarray_out' LANGUAGE internal STRICT IMMUTABLE;
	13	+CREATE FUNCTION dbms_stats.anyarray_recv(internal) RETURNS dbms_stats.anyarray
	14	+ AS 'MODULE_PATHNAME', 'dbms_stats_array_recv' LANGUAGE C STRICT IMMUTABLE;
	15	+CREATE FUNCTION dbms_stats.anyarray_send(dbms_stats.anyarray) RETURNS bytea
	16	+ AS 'anyarray_send' LANGUAGE internal STRICT IMMUTABLE;
	17	+CREATE TYPE dbms_stats.anyarray (
	18	+ INPUT = dbms_stats.anyarray_in,
	19	+ OUTPUT = dbms_stats.anyarray_out,
	20	+ RECEIVE = dbms_stats.anyarray_recv,
	21	+ SEND = dbms_stats.anyarray_send,
	22	+ INTERNALLENGTH = VARIABLE,
	23	+ ALIGNMENT = double,
	24	+ STORAGE = extended,
	25	+ CATEGORY = 'P'
	26	+);
	27	+
	28	+--
	29	+-- User defined stats tables
	30	+--
	31	+
	32	+CREATE TABLE dbms_stats.relation_stats_locked (
	33	+ relid oid NOT NULL,
	34	+ relname text NOT NULL,
	35	+ relpages int4,
	36	+ reltuples float4,
	37	+ relallvisible int4,
	38	+ curpages int4,
	39	+ last_analyze timestamp with time zone,
	40	+ last_autoanalyze timestamp with time zone,
	41	+ PRIMARY KEY (relid)
	42	+);
	43	+
	44	+CREATE TABLE dbms_stats.column_stats_locked (
	45	+ starelid oid NOT NULL,
	46	+ staattnum int2 NOT NULL,
	47	+ stainherit bool NOT NULL,
	48	+ stanullfrac float4,
	49	+ stawidth int4,
	50	+ stadistinct float4,
	51	+ stakind1 int2,
	52	+ stakind2 int2,
	53	+ stakind3 int2,
	54	+ stakind4 int2,
	55	+ stakind5 int2,
	56	+ staop1 oid,
	57	+ staop2 oid,
	58	+ staop3 oid,
	59	+ staop4 oid,
	60	+ staop5 oid,
	61	+ stanumbers1 float4[],
	62	+ stanumbers2 float4[],
	63	+ stanumbers3 float4[],
	64	+ stanumbers4 float4[],
	65	+ stanumbers5 float4[],
	66	+ stavalues1 dbms_stats.anyarray,
	67	+ stavalues2 dbms_stats.anyarray,
	68	+ stavalues3 dbms_stats.anyarray,
	69	+ stavalues4 dbms_stats.anyarray,
	70	+ stavalues5 dbms_stats.anyarray,
	71	+ PRIMARY KEY (starelid, staattnum, stainherit),
	72	+ FOREIGN KEY (starelid) REFERENCES dbms_stats.relation_stats_locked (relid) ON DELETE CASCADE
	73	+);
	74	+
	75	+--
	76	+-- Statistics backup tables
	77	+--
	78	+
	79	+CREATE TABLE dbms_stats.backup_history (
	80	+ id serial8 PRIMARY KEY,
	81	+ time timestamp with time zone NOT NULL,
	82	+ unit char(1) NOT NULL,
	83	+ comment text
	84	+);
	85	+
	86	+CREATE TABLE dbms_stats.relation_stats_backup (
	87	+ id int8 NOT NULL,
	88	+ relid oid NOT NULL,
	89	+ relname text NOT NULL,
	90	+ relpages int4 NOT NULL,
	91	+ reltuples float4 NOT NULL,
	92	+ relallvisible int4 NOT NULL,
	93	+ curpages int4 NOT NULL,
	94	+ last_analyze timestamp with time zone,
	95	+ last_autoanalyze timestamp with time zone,
	96	+ PRIMARY KEY (id, relid),
	97	+ FOREIGN KEY (id) REFERENCES dbms_stats.backup_history (id) ON DELETE CASCADE
	98	+);
	99	+
	100	+CREATE TABLE dbms_stats.column_stats_backup (
	101	+ id int8 NOT NULL,
	102	+ statypid oid NOT NULL,
	103	+ starelid oid NOT NULL,
	104	+ staattnum int2 NOT NULL,
	105	+ stainherit bool NOT NULL,
	106	+ stanullfrac float4 NOT NULL,
	107	+ stawidth int4 NOT NULL,
	108	+ stadistinct float4 NOT NULL,
	109	+ stakind1 int2 NOT NULL,
	110	+ stakind2 int2 NOT NULL,
	111	+ stakind3 int2 NOT NULL,
	112	+ stakind4 int2 NOT NULL,
	113	+ stakind5 int2 NOT NULL,
	114	+ staop1 oid NOT NULL,
	115	+ staop2 oid NOT NULL,
	116	+ staop3 oid NOT NULL,
	117	+ staop4 oid NOT NULL,
	118	+ staop5 oid NOT NULL,
	119	+ stanumbers1 float4[],
	120	+ stanumbers2 float4[],
	121	+ stanumbers3 float4[],
	122	+ stanumbers4 float4[],
	123	+ stanumbers5 float4[],
	124	+ stavalues1 dbms_stats.anyarray,
	125	+ stavalues2 dbms_stats.anyarray,
	126	+ stavalues3 dbms_stats.anyarray,
	127	+ stavalues4 dbms_stats.anyarray,
	128	+ stavalues5 dbms_stats.anyarray,
	129	+ PRIMARY KEY (id, starelid, staattnum, stainherit),
	130	+ FOREIGN KEY (id) REFERENCES dbms_stats.backup_history (id) ON DELETE CASCADE,
	131	+ FOREIGN KEY (id, starelid) REFERENCES dbms_stats.relation_stats_backup (id, relid) ON DELETE CASCADE
	132	+);
	133	+
	134	+--
	135	+-- Functions
	136	+--
	137	+
	138	+CREATE FUNCTION dbms_stats.relname(nspname text, relname text)
	139	+RETURNS text AS
	140	+$$SELECT quote_ident($1) \|\| '.' \|\| quote_ident($2)$$
	141	+LANGUAGE sql STABLE STRICT;
	142	+
	143	+CREATE FUNCTION dbms_stats.is_system_schema(schemaname text)
	144	+RETURNS boolean AS
	145	+'MODULE_PATHNAME', 'dbms_stats_is_system_schema'
	146	+LANGUAGE C IMMUTABLE STRICT;
	147	+
	148	+CREATE FUNCTION dbms_stats.is_system_catalog(relid regclass)
	149	+RETURNS boolean AS
	150	+'MODULE_PATHNAME', 'dbms_stats_is_system_catalog'
	151	+LANGUAGE C STABLE;
	152	+
	153	+CREATE FUNCTION dbms_stats.is_target_relkind(relkind "char")
	154	+RETURNS boolean AS
	155	+$$SELECT $1 IN ('r', 'i', 'f', 'm')$$
	156	+LANGUAGE sql STABLE;
	157	+
	158	+CREATE FUNCTION dbms_stats.merge(
	159	+ lhs dbms_stats.column_stats_locked,
	160	+ rhs pg_catalog.pg_statistic
	161	+) RETURNS dbms_stats.column_stats_locked AS
	162	+'MODULE_PATHNAME', 'dbms_stats_merge'
	163	+LANGUAGE C STABLE;
	164	+
	165	+CREATE VIEW dbms_stats.relation_stats_effective AS
	166	+ SELECT
	167	+ c.oid AS relid,
	168	+ dbms_stats.relname(nspname, c.relname) AS relname,
	169	+ COALESCE(v.relpages, c.relpages) AS relpages,
	170	+ COALESCE(v.reltuples, c.reltuples) AS reltuples,
	171	+ COALESCE(v.relallvisible, c.relallvisible) AS relallvisible,
	172	+ COALESCE(v.curpages,
	173	+ (pg_relation_size(c.oid) / current_setting('block_size')::int4)::int4)
	174	+ AS curpages,
	175	+ COALESCE(v.last_analyze,
	176	+ pg_catalog.pg_stat_get_last_analyze_time(c.oid))
	177	+ AS last_analyze,
	178	+ COALESCE(v.last_autoanalyze,
	179	+ pg_catalog.pg_stat_get_last_autoanalyze_time(c.oid))
	180	+ AS last_autoanalyze
	181	+ FROM pg_catalog.pg_class c
	182	+ JOIN pg_catalog.pg_namespace n
	183	+ ON c.relnamespace = n.oid
	184	+ LEFT JOIN dbms_stats.relation_stats_locked v
	185	+ ON v.relid = c.oid
	186	+ WHERE dbms_stats.is_target_relkind(c.relkind)
	187	+ AND NOT dbms_stats.is_system_schema(nspname);
	188	+
	189	+CREATE VIEW dbms_stats.column_stats_effective AS
	190	+ SELECT * FROM (
	191	+ SELECT (dbms_stats.merge(v, s)).*
	192	+ FROM pg_catalog.pg_statistic s
	193	+ FULL JOIN dbms_stats.column_stats_locked v
	194	+ USING (starelid, staattnum, stainherit)
	195	+ WHERE NOT dbms_stats.is_system_catalog(starelid)
	196	+ AND EXISTS (
	197	+ SELECT NULL
	198	+ FROM pg_attribute a
	199	+ WHERE a.attrelid = starelid
	200	+ AND a.attnum = staattnum
	201	+ AND a.attisdropped = false
	202	+ )
	203	+ ) m
	204	+ WHERE starelid IS NOT NULL;
	205	+
	206	+--
	207	+-- Note: This view is copied from pg_stats in
	208	+-- src/backend/catalog/system_views.sql in core source tree of version
	209	+-- 9.4, and customized for pg_dbms_stats. Changes from orignal one are:
	210	+-- - rename from pg_stats to dbms_stats.stats by a view name.
	211	+-- - changed the table name from pg_statistic to dbms_stats.column_stats_effective.
	212	+--
	213	+CREATE VIEW dbms_stats.stats AS
	214	+ SELECT
	215	+ nspname AS schemaname,
	216	+ relname AS tablename,
	217	+ attname AS attname,
	218	+ stainherit AS inherited,
	219	+ stanullfrac AS null_frac,
	220	+ stawidth AS avg_width,
	221	+ stadistinct AS n_distinct,
	222	+ CASE
	223	+ WHEN stakind1 = 1 THEN stavalues1
	224	+ WHEN stakind2 = 1 THEN stavalues2
	225	+ WHEN stakind3 = 1 THEN stavalues3
	226	+ WHEN stakind4 = 1 THEN stavalues4
	227	+ WHEN stakind5 = 1 THEN stavalues5
	228	+ END AS most_common_vals,
	229	+ CASE
	230	+ WHEN stakind1 = 1 THEN stanumbers1
	231	+ WHEN stakind2 = 1 THEN stanumbers2
	232	+ WHEN stakind3 = 1 THEN stanumbers3
	233	+ WHEN stakind4 = 1 THEN stanumbers4
	234	+ WHEN stakind5 = 1 THEN stanumbers5
	235	+ END AS most_common_freqs,
	236	+ CASE
	237	+ WHEN stakind1 = 2 THEN stavalues1
	238	+ WHEN stakind2 = 2 THEN stavalues2
	239	+ WHEN stakind3 = 2 THEN stavalues3
	240	+ WHEN stakind4 = 2 THEN stavalues4
	241	+ WHEN stakind5 = 2 THEN stavalues5
	242	+ END AS histogram_bounds,
	243	+ CASE
	244	+ WHEN stakind1 = 3 THEN stanumbers1[1]
	245	+ WHEN stakind2 = 3 THEN stanumbers2[1]
	246	+ WHEN stakind3 = 3 THEN stanumbers3[1]
	247	+ WHEN stakind4 = 3 THEN stanumbers4[1]
	248	+ WHEN stakind5 = 3 THEN stanumbers5[1]
	249	+ END AS correlation,
	250	+ CASE
	251	+ WHEN stakind1 = 4 THEN stavalues1
	252	+ WHEN stakind2 = 4 THEN stavalues2
	253	+ WHEN stakind3 = 4 THEN stavalues3
	254	+ WHEN stakind4 = 4 THEN stavalues4
	255	+ WHEN stakind5 = 4 THEN stavalues5
	256	+ END AS most_common_elems,
	257	+ CASE
	258	+ WHEN stakind1 = 4 THEN stanumbers1
	259	+ WHEN stakind2 = 4 THEN stanumbers2
	260	+ WHEN stakind3 = 4 THEN stanumbers3
	261	+ WHEN stakind4 = 4 THEN stanumbers4
	262	+ WHEN stakind5 = 4 THEN stanumbers5
	263	+ END AS most_common_elem_freqs,
	264	+ CASE
	265	+ WHEN stakind1 = 5 THEN stanumbers1
	266	+ WHEN stakind2 = 5 THEN stanumbers2
	267	+ WHEN stakind3 = 5 THEN stanumbers3
	268	+ WHEN stakind4 = 5 THEN stanumbers4
	269	+ WHEN stakind5 = 5 THEN stanumbers5
	270	+ END AS elem_count_histogram
	271	+ FROM dbms_stats.column_stats_effective s JOIN pg_class c ON (c.oid = s.starelid)
	272	+ JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
	273	+ LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
	274	+ WHERE NOT attisdropped AND has_column_privilege(c.oid, a.attnum, 'select');
	275	+
	276	+--
	277	+-- Utility functions
	278	+--
	279	+
	280	+CREATE FUNCTION dbms_stats.invalidate_relation_cache()
	281	+ RETURNS trigger AS
	282	+ 'MODULE_PATHNAME', 'dbms_stats_invalidate_relation_cache'
	283	+ LANGUAGE C;
	284	+
	285	+-- Invalidate cached plans when dbms_stats.relation_stats_locked is modified.
	286	+CREATE TRIGGER invalidate_relation_cache
	287	+ BEFORE INSERT OR DELETE OR UPDATE
	288	+ ON dbms_stats.relation_stats_locked
	289	+ FOR EACH ROW EXECUTE PROCEDURE dbms_stats.invalidate_relation_cache();
	290	+
	291	+CREATE FUNCTION dbms_stats.invalidate_column_cache()
	292	+ RETURNS trigger AS
	293	+ 'MODULE_PATHNAME', 'dbms_stats_invalidate_column_cache'
	294	+ LANGUAGE C;
	295	+
	296	+-- Invalidate cached plans when dbms_stats.column_stats_locked is modified.
	297	+CREATE TRIGGER invalidate_column_cache
	298	+ BEFORE INSERT OR DELETE OR UPDATE
	299	+ ON dbms_stats.column_stats_locked
	300	+ FOR EACH ROW EXECUTE PROCEDURE dbms_stats.invalidate_column_cache();
	301	+
	302	+--
	303	+-- BACKUP_STATS: Statistics backup functions
	304	+--
	305	+
	306	+CREATE FUNCTION dbms_stats.backup(
	307	+ backup_id int8,
	308	+ relid regclass,
	309	+ attnum int2
	310	+) RETURNS int8 AS
	311	+$$
	312	+/* Lock the backup id */
	313	+SELECT * from dbms_stats.backup_history
	314	+ WHERE id = $1 FOR UPDATE;
	315	+
	316	+INSERT INTO dbms_stats.relation_stats_backup
	317	+ SELECT $1, v.relid, v.relname, v.relpages, v.reltuples, v.relallvisible,
	318	+ v.curpages, v.last_analyze, v.last_autoanalyze
	319	+ FROM pg_catalog.pg_class c,
	320	+ dbms_stats.relation_stats_effective v
	321	+ WHERE c.oid = v.relid
	322	+ AND dbms_stats.is_target_relkind(relkind)
	323	+ AND NOT dbms_stats.is_system_catalog(v.relid)
	324	+ AND (v.relid = $2 OR $2 IS NULL);
	325	+
	326	+INSERT INTO dbms_stats.column_stats_backup
	327	+ SELECT $1, atttypid, s.*
	328	+ FROM pg_catalog.pg_class c,
	329	+ dbms_stats.column_stats_effective s,
	330	+ pg_catalog.pg_attribute a
	331	+ WHERE c.oid = starelid
	332	+ AND starelid = attrelid
	333	+ AND staattnum = attnum
	334	+ AND dbms_stats.is_target_relkind(relkind)
	335	+ AND NOT dbms_stats.is_system_catalog(c.oid)
	336	+ AND ($2 IS NULL OR starelid = $2)
	337	+ AND ($3 IS NULL OR staattnum = $3);
	338	+
	339	+SELECT $1;
	340	+$$
	341	+LANGUAGE sql;
	342	+
	343	+CREATE FUNCTION dbms_stats.backup(
	344	+ relid regclass DEFAULT NULL,
	345	+ attname text DEFAULT NULL,
	346	+ comment text DEFAULT NULL
	347	+) RETURNS int8 AS
	348	+$$
	349	+DECLARE
	350	+ backup_id int8;
	351	+ backup_relkind "char";
	352	+ set_attnum int2;
	353	+ unit_type char;
	354	+BEGIN
	355	+ IF $1 IS NULL AND $2 IS NOT NULL THEN
	356	+ RAISE EXCEPTION 'relation required';
	357	+ END IF;
	358	+ IF $1 IS NOT NULL THEN
	359	+ SELECT relkind INTO backup_relkind
	360	+ FROM pg_catalog.pg_class WHERE oid = $1 FOR SHARE;
	361	+ IF NOT FOUND THEN
	362	+ RAISE EXCEPTION 'relation "%" not found', $1;
	363	+ END IF;
	364	+ IF NOT dbms_stats.is_target_relkind(backup_relkind) THEN
	365	+ RAISE EXCEPTION 'relation of relkind "%" cannot have statistics to backup: "%"',
	366	+ backup_relkind, $1
	367	+ USING HINT = 'Only tables(r), materialized views(m), foreign tables(f) and indexes(i) are allowed.';
	368	+ END IF;
	369	+ IF dbms_stats.is_system_catalog($1) THEN
	370	+ RAISE EXCEPTION 'backing up statistics is inhibited for system catalogs: "%"', $1;
	371	+ END IF;
	372	+ IF $2 IS NOT NULL THEN
	373	+ SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
	374	+ WHERE a.attrelid = $1 AND a.attname = $2 FOR SHARE;
	375	+ IF set_attnum IS NULL THEN
	376	+ RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
	377	+ END IF;
	378	+ IF NOT EXISTS(SELECT * FROM dbms_stats.column_stats_effective WHERE starelid = $1 AND staattnum = set_attnum) THEN
	379	+ RAISE EXCEPTION 'no statistics available for column "%" of relation "%"', $2, $1;
	380	+ END IF;
	381	+ unit_type = 'c';
	382	+ ELSE
	383	+ unit_type = 't';
	384	+ END IF;
	385	+ ELSE
	386	+ unit_type = 'd';
	387	+ END IF;
	388	+
	389	+ INSERT INTO dbms_stats.backup_history(time, unit, comment)
	390	+ VALUES (current_timestamp, unit_type, $3)
	391	+ RETURNING dbms_stats.backup(id, $1, set_attnum) INTO backup_id;
	392	+ RETURN backup_id;
	393	+END;
	394	+$$
	395	+LANGUAGE plpgsql;
	396	+
	397	+CREATE FUNCTION dbms_stats.backup_database_stats(
	398	+ comment text
	399	+) RETURNS int8 AS
	400	+$$
	401	+SELECT dbms_stats.backup(NULL, NULL, $1)
	402	+$$
	403	+LANGUAGE sql;
	404	+
	405	+CREATE FUNCTION dbms_stats.backup_schema_stats(
	406	+ schemaname text,
	407	+ comment text
	408	+) RETURNS int8 AS
	409	+$$
	410	+DECLARE
	411	+ backup_id int8;
	412	+BEGIN
	413	+ IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1 FOR SHARE)
	414	+ THEN
	415	+ RAISE EXCEPTION 'schema "%" not found', $1;
	416	+ END IF;
	417	+ IF dbms_stats.is_system_schema($1) THEN
	418	+ RAISE EXCEPTION 'backing up statistics is inhibited for system schemas: "%"', $1;
	419	+ END IF;
	420	+
	421	+ INSERT INTO dbms_stats.backup_history(time, unit, comment)
	422	+ VALUES (current_timestamp, 's', comment)
	423	+ RETURNING id INTO backup_id;
	424	+
	425	+ PERFORM dbms_stats.backup(backup_id, cn.oid, NULL)
	426	+ FROM (SELECT c.oid
	427	+ FROM pg_catalog.pg_class c,
	428	+ pg_catalog.pg_namespace n
	429	+ WHERE n.nspname = schemaname
	430	+ AND c.relnamespace = n.oid
	431	+ AND dbms_stats.is_target_relkind(c.relkind)
	432	+ ORDER BY c.oid
	433	+ ) cn;
	434	+
	435	+ RETURN backup_id;
	436	+END;
	437	+$$
	438	+LANGUAGE plpgsql;
	439	+
	440	+CREATE FUNCTION dbms_stats.backup_table_stats(
	441	+ relid regclass,
	442	+ comment text
	443	+) RETURNS int8 AS
	444	+$$
	445	+SELECT dbms_stats.backup($1, NULL, $2)
	446	+$$
	447	+LANGUAGE sql;
	448	+
	449	+CREATE FUNCTION dbms_stats.backup_table_stats(
	450	+ schemaname text,
	451	+ tablename text,
	452	+ comment text
	453	+) RETURNS int8 AS
	454	+$$
	455	+SELECT dbms_stats.backup(dbms_stats.relname($1, $2)::regclass, NULL, $3)
	456	+$$
	457	+LANGUAGE sql;
	458	+
	459	+CREATE FUNCTION dbms_stats.backup_column_stats(
	460	+ relid regclass,
	461	+ attname text,
	462	+ comment text
	463	+) RETURNS int8 AS
	464	+$$
	465	+SELECT dbms_stats.backup($1, $2, $3)
	466	+$$
	467	+LANGUAGE sql;
	468	+
	469	+CREATE FUNCTION dbms_stats.backup_column_stats(
	470	+ schemaname text,
	471	+ tablename text,
	472	+ attname text,
	473	+ comment text
	474	+) RETURNS int8 AS
	475	+$$
	476	+SELECT dbms_stats.backup(dbms_stats.relname($1, $2)::regclass, $3, $4)
	477	+$$
	478	+LANGUAGE sql;
	479	+
	480	+--
	481	+-- RESTORE_STATS: Statistics restore functions
	482	+--
	483	+CREATE FUNCTION dbms_stats.restore(
	484	+ backup_id int8,
	485	+ relid regclass DEFAULT NULL,
	486	+ attname text DEFAULT NULL
	487	+) RETURNS SETOF regclass AS
	488	+$$
	489	+DECLARE
	490	+ restore_id int8;
	491	+ restore_relid regclass;
	492	+ restore_attnum int2;
	493	+ set_attnum int2;
	494	+ restore_attname text;
	495	+ restore_type regtype;
	496	+ cur_type regtype;
	497	+BEGIN
	498	+ IF $1 IS NULL THEN
	499	+ RAISE EXCEPTION 'backup id required';
	500	+ END IF;
	501	+ IF $2 IS NULL AND $3 IS NOT NULL THEN
	502	+ RAISE EXCEPTION 'relation required';
	503	+ END IF;
	504	+ IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
	505	+ WHERE id <= $1 FOR SHARE) THEN
	506	+ RAISE EXCEPTION 'backup id % not found', $1;
	507	+ END IF;
	508	+ IF $2 IS NOT NULL THEN
	509	+ IF NOT EXISTS(SELECT * FROM pg_catalog.pg_class
	510	+ WHERE oid = $2 FOR SHARE) THEN
	511	+ RAISE EXCEPTION 'relation "%" not found', $2;
	512	+ END IF;
	513	+ -- Grabbing all backups for the relation which is not used in restore.
	514	+ IF NOT EXISTS(SELECT * FROM dbms_stats.relation_stats_backup b
	515	+ WHERE b.id <= $1 AND b.relid = $2 FOR SHARE) THEN
	516	+ RAISE EXCEPTION 'statistics of relation "%" not found in any backups before backup id = %', $2, $1;
	517	+ END IF;
	518	+ IF $3 IS NOT NULL THEN
	519	+ SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
	520	+ WHERE a.attrelid = $2 AND a.attname = $3;
	521	+ IF set_attnum IS NULL THEN
	522	+ RAISE EXCEPTION 'column "%" not found in relation %', $3, $2;
	523	+ END IF;
	524	+ IF NOT EXISTS(SELECT * FROM dbms_stats.column_stats_backup WHERE id <= $1 AND starelid = $2 AND staattnum = set_attnum) THEN
	525	+ RAISE EXCEPTION 'statistics of column "%" of relation "%" are not found in any backups before backup id = %',$3, $2, $1;
	526	+ END IF;
	527	+ END IF;
	528	+ PERFORM * FROM dbms_stats.relation_stats_locked r
	529	+ WHERE r.relid = $2 FOR UPDATE;
	530	+ ELSE
	531	+ /* Lock the whole relation stats if relation is not specified.*/
	532	+ LOCK dbms_stats.relation_stats_locked IN EXCLUSIVE MODE;
	533	+ END IF;
	534	+
	535	+ FOR restore_id, restore_relid IN
	536	+ SELECT max(id), coid FROM
	537	+ (SELECT b.id as id, c.oid as coid
	538	+ FROM pg_class c, dbms_stats.relation_stats_backup b
	539	+ WHERE (c.oid = $2 OR $2 IS NULL)
	540	+ AND c.oid = b.relid
	541	+ AND dbms_stats.is_target_relkind(c.relkind)
	542	+ AND NOT dbms_stats.is_system_catalog(c.oid)
	543	+ AND b.id <= $1
	544	+ FOR SHARE) t
	545	+ GROUP BY coid
	546	+ ORDER BY coid::regclass::text
	547	+ LOOP
	548	+ UPDATE dbms_stats.relation_stats_locked r
	549	+ SET relid = b.relid,
	550	+ relname = b.relname,
	551	+ relpages = b.relpages,
	552	+ reltuples = b.reltuples,
	553	+ relallvisible = b.relallvisible,
	554	+ curpages = b.curpages,
	555	+ last_analyze = b.last_analyze,
	556	+ last_autoanalyze = b.last_autoanalyze
	557	+ FROM dbms_stats.relation_stats_backup b
	558	+ WHERE r.relid = restore_relid
	559	+ AND b.id = restore_id
	560	+ AND b.relid = restore_relid;
	561	+ IF NOT FOUND THEN
	562	+ INSERT INTO dbms_stats.relation_stats_locked
	563	+ SELECT b.relid,
	564	+ b.relname,
	565	+ b.relpages,
	566	+ b.reltuples,
	567	+ b.relallvisible,
	568	+ b.curpages,
	569	+ b.last_analyze,
	570	+ b.last_autoanalyze
	571	+ FROM dbms_stats.relation_stats_backup b
	572	+ WHERE b.id = restore_id
	573	+ AND b.relid = restore_relid;
	574	+ END IF;
	575	+ RETURN NEXT restore_relid;
	576	+ END LOOP;
	577	+
	578	+ FOR restore_id, restore_relid, restore_attnum, restore_type, cur_type IN
	579	+ SELECT t.id, t.oid, t.attnum, b.statypid, a.atttypid
	580	+ FROM pg_attribute a,
	581	+ dbms_stats.column_stats_backup b,
	582	+ (SELECT max(b.id) AS id, c.oid, a.attnum
	583	+ FROM pg_class c, pg_attribute a, dbms_stats.column_stats_backup b
	584	+ WHERE (c.oid = $2 OR $2 IS NULL)
	585	+ AND c.oid = a.attrelid
	586	+ AND c.oid = b.starelid
	587	+ AND (a.attnum = set_attnum OR set_attnum IS NULL)
	588	+ AND a.attnum = b.staattnum
	589	+ AND NOT a.attisdropped
	590	+ AND dbms_stats.is_target_relkind(c.relkind)
	591	+ AND b.id <= $1
	592	+ GROUP BY c.oid, a.attnum) t
	593	+ WHERE a.attrelid = t.oid
	594	+ AND a.attnum = t.attnum
	595	+ AND b.id = t.id
	596	+ AND b.starelid = t.oid
	597	+ AND b.staattnum = t.attnum
	598	+ LOOP
	599	+ IF restore_type <> cur_type THEN
	600	+ SELECT a.attname INTO restore_attname
	601	+ FROM pg_catalog.pg_attribute a
	602	+ WHERE a.attrelid = restore_relid
	603	+ AND a.attnum = restore_attnum;
	604	+ RAISE WARNING 'data type of column "%.%" is inconsistent between database(%) and backup (%). Skip.',
	605	+ restore_relid, restore_attname, cur_type, restore_type;
	606	+ ELSE
	607	+ DELETE FROM dbms_stats.column_stats_locked
	608	+ WHERE starelid = restore_relid
	609	+ AND staattnum = restore_attnum;
	610	+ INSERT INTO dbms_stats.column_stats_locked
	611	+ SELECT starelid, staattnum, stainherit,
	612	+ stanullfrac, stawidth, stadistinct,
	613	+ stakind1, stakind2, stakind3, stakind4, stakind5,
	614	+ staop1, staop2, staop3, staop4, staop5,
	615	+ stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
	616	+ stavalues1, stavalues2, stavalues3, stavalues4, stavalues5
	617	+ FROM dbms_stats.column_stats_backup
	618	+ WHERE id = restore_id
	619	+ AND starelid = restore_relid
	620	+ AND staattnum = restore_attnum;
	621	+ END IF;
	622	+ END LOOP;
	623	+EXCEPTION
	624	+ WHEN unique_violation THEN
	625	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
	626	+END;
	627	+$$
	628	+LANGUAGE plpgsql;
	629	+
	630	+CREATE FUNCTION dbms_stats.restore_database_stats(
	631	+ as_of_timestamp timestamp with time zone
	632	+) RETURNS SETOF regclass AS
	633	+$$
	634	+SELECT dbms_stats.restore(m.id, m.relid)
	635	+ FROM (SELECT max(id) AS id, relid
	636	+ FROM (SELECT r.id, r.relid
	637	+ FROM pg_class c, dbms_stats.relation_stats_backup r,
	638	+ dbms_stats.backup_history b
	639	+ WHERE c.oid = r.relid
	640	+ AND r.id = b.id
	641	+ AND b.time <= $1
	642	+ FOR SHARE) t1
	643	+ GROUP BY t1.relid
	644	+ ORDER BY t1.relid) m;
	645	+$$
	646	+LANGUAGE sql STRICT;
	647	+
	648	+CREATE FUNCTION dbms_stats.restore_schema_stats(
	649	+ schemaname text,
	650	+ as_of_timestamp timestamp with time zone
	651	+) RETURNS SETOF regclass AS
	652	+$$
	653	+BEGIN
	654	+ IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
	655	+ RAISE EXCEPTION 'schema "%" not found', $1;
	656	+ END IF;
	657	+ IF dbms_stats.is_system_schema($1) THEN
	658	+ RAISE EXCEPTION 'restoring statistics is inhibited for system schemas: "%"', $1;
	659	+ END IF;
	660	+
	661	+ RETURN QUERY
	662	+ SELECT dbms_stats.restore(m.id, m.relid)
	663	+ FROM (SELECT max(id) AS id, relid
	664	+ FROM (SELECT r.id, r.relid
	665	+ FROM pg_class c, pg_namespace n,
	666	+ dbms_stats.relation_stats_backup r,
	667	+ dbms_stats.backup_history b
	668	+ WHERE c.oid = r.relid
	669	+ AND c.relnamespace = n.oid
	670	+ AND n.nspname = $1
	671	+ AND r.id = b.id
	672	+ AND b.time <= $2
	673	+ FOR SHARE) t1
	674	+ GROUP BY t1.relid
	675	+ ORDER BY t1.relid) m;
	676	+END;
	677	+$$
	678	+LANGUAGE plpgsql STRICT;
	679	+
	680	+CREATE FUNCTION dbms_stats.restore_table_stats(
	681	+ relid regclass,
	682	+ as_of_timestamp timestamp with time zone
	683	+) RETURNS SETOF regclass AS
	684	+$$
	685	+SELECT dbms_stats.restore(max(id), $1, NULL)
	686	+ FROM dbms_stats.backup_history WHERE time <= $2
	687	+$$
	688	+LANGUAGE sql STRICT;
	689	+
	690	+CREATE FUNCTION dbms_stats.restore_table_stats(
	691	+ schemaname text,
	692	+ tablename text,
	693	+ as_of_timestamp timestamp with time zone
	694	+) RETURNS SETOF regclass AS
	695	+$$
	696	+SELECT dbms_stats.restore_table_stats(dbms_stats.relname($1, $2)::regclass, $3)
	697	+$$
	698	+LANGUAGE sql STRICT;
	699	+
	700	+CREATE FUNCTION dbms_stats.restore_column_stats(
	701	+ relid regclass,
	702	+ attname text,
	703	+ as_of_timestamp timestamp with time zone
	704	+) RETURNS SETOF regclass AS
	705	+$$
	706	+SELECT dbms_stats.restore(max(id), $1, $2)
	707	+ FROM dbms_stats.backup_history WHERE time <= $3
	708	+$$
	709	+LANGUAGE sql STRICT;
	710	+
	711	+CREATE FUNCTION dbms_stats.restore_column_stats(
	712	+ schemaname text,
	713	+ tablename text,
	714	+ attname text,
	715	+ as_of_timestamp timestamp with time zone
	716	+) RETURNS SETOF regclass AS
	717	+$$
	718	+SELECT dbms_stats.restore(max(id), dbms_stats.relname($1, $2)::regclass, $3)
	719	+ FROM dbms_stats.backup_history WHERE time <= $4
	720	+$$
	721	+LANGUAGE sql STRICT;
	722	+
	723	+CREATE FUNCTION dbms_stats.restore_stats(
	724	+ backup_id int8
	725	+) RETURNS SETOF regclass AS
	726	+$$
	727	+DECLARE
	728	+ restore_relid regclass;
	729	+ restore_attnum int2;
	730	+ restore_attname text;
	731	+ restore_type regtype;
	732	+ cur_type regtype;
	733	+BEGIN
	734	+ IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id = $1) THEN
	735	+ RAISE EXCEPTION 'backup id % not found', $1;
	736	+ END IF;
	737	+
	738	+ /* Lock the backup */
	739	+ PERFORM * from dbms_stats.relation_stats_backup b
	740	+ WHERE id = $1 FOR SHARE;
	741	+
	742	+ /* Locking only relation_stats_locked is sufficient */
	743	+ LOCK dbms_stats.relation_stats_locked IN EXCLUSIVE MODE;
	744	+
	745	+ FOR restore_relid IN
	746	+ SELECT b.relid
	747	+ FROM pg_class c
	748	+ JOIN dbms_stats.relation_stats_backup b ON (c.oid = b.relid)
	749	+ WHERE b.id = $1
	750	+ ORDER BY c.oid::regclass::text
	751	+ LOOP
	752	+ UPDATE dbms_stats.relation_stats_locked r
	753	+ SET relid = b.relid,
	754	+ relname = b.relname,
	755	+ relpages = b.relpages,
	756	+ reltuples = b.reltuples,
	757	+ relallvisible = b.relallvisible,
	758	+ curpages = b.curpages,
	759	+ last_analyze = b.last_analyze,
	760	+ last_autoanalyze = b.last_autoanalyze
	761	+ FROM dbms_stats.relation_stats_backup b
	762	+ WHERE r.relid = restore_relid
	763	+ AND b.id = $1
	764	+ AND b.relid = restore_relid;
	765	+ IF NOT FOUND THEN
	766	+ INSERT INTO dbms_stats.relation_stats_locked
	767	+ SELECT b.relid,
	768	+ b.relname,
	769	+ b.relpages,
	770	+ b.reltuples,
	771	+ b.relallvisible,
	772	+ b.curpages,
	773	+ b.last_analyze,
	774	+ b.last_autoanalyze
	775	+ FROM dbms_stats.relation_stats_backup b
	776	+ WHERE b.id = $1
	777	+ AND b.relid = restore_relid;
	778	+ END IF;
	779	+ RETURN NEXT restore_relid;
	780	+ END LOOP;
	781	+
	782	+ FOR restore_relid, restore_attnum, restore_type, cur_type IN
	783	+ SELECT c.oid, a.attnum, b.statypid, a.atttypid
	784	+ FROM pg_class c
	785	+ JOIN dbms_stats.column_stats_backup b ON (c.oid = b.starelid)
	786	+ JOIN pg_attribute a ON (b.starelid = attrelid
	787	+ AND b.staattnum = a.attnum)
	788	+ WHERE b.id = $1
	789	+ LOOP
	790	+ IF restore_type <> cur_type THEN
	791	+ SELECT attname INTO restore_attname
	792	+ FROM pg_catalog.pg_attribute
	793	+ WHERE attrelid = restore_relid
	794	+ AND attnum = restore_attnum;
	795	+ RAISE WARNING 'data type of column "%.%" is inconsistent between database(%) and backup (%). Skip.',
	796	+ restore_relid, restore_attname, cur_type, restore_type;
	797	+ ELSE
	798	+ DELETE FROM dbms_stats.column_stats_locked
	799	+ WHERE starelid = restore_relid
	800	+ AND staattnum = restore_attnum;
	801	+ INSERT INTO dbms_stats.column_stats_locked
	802	+ SELECT starelid, staattnum, stainherit,
	803	+ stanullfrac, stawidth, stadistinct,
	804	+ stakind1, stakind2, stakind3, stakind4, stakind5,
	805	+ staop1, staop2, staop3, staop4, staop5,
	806	+ stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
	807	+ stavalues1, stavalues2, stavalues3, stavalues4, stavalues5
	808	+ FROM dbms_stats.column_stats_backup
	809	+ WHERE id = $1
	810	+ AND starelid = restore_relid
	811	+ AND staattnum = restore_attnum;
	812	+ END IF;
	813	+ END LOOP;
	814	+
	815	+END;
	816	+$$
	817	+LANGUAGE plpgsql STRICT;
	818	+
	819	+--
	820	+-- LOCK_STATS: Statistics lock functions
	821	+--
	822	+
	823	+CREATE FUNCTION dbms_stats.lock(
	824	+ relid regclass,
	825	+ attname text
	826	+) RETURNS regclass AS
	827	+$$
	828	+DECLARE
	829	+ lock_relkind "char";
	830	+ set_attnum int2;
	831	+ r record;
	832	+BEGIN
	833	+ IF $1 IS NULL THEN
	834	+ RAISE EXCEPTION 'relation required';
	835	+ END IF;
	836	+ IF $2 IS NULL THEN
	837	+ RETURN dbms_stats.lock($1);
	838	+ END IF;
	839	+ SELECT relkind INTO lock_relkind FROM pg_catalog.pg_class WHERE oid = $1;
	840	+ IF NOT FOUND THEN
	841	+ RAISE EXCEPTION 'relation "%" not found', $1;
	842	+ END IF;
	843	+ IF NOT dbms_stats.is_target_relkind(lock_relkind) THEN
	844	+ RAISE EXCEPTION '"%" must be a table or an index', $1;
	845	+ END IF;
	846	+ IF EXISTS(SELECT * FROM pg_catalog.pg_index WHERE lock_relkind = 'i' AND indexrelid = $1 AND indexprs IS NULL) THEN
	847	+ RAISE EXCEPTION '"%" must be an expression index', $1;
	848	+ END IF;
	849	+ IF dbms_stats.is_system_catalog($1) THEN
	850	+ RAISE EXCEPTION 'locking statistics is inhibited for system catalogs: "%"', $1;
	851	+ END IF;
	852	+ SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
	853	+ WHERE a.attrelid = $1 AND a.attname = $2;
	854	+ IF set_attnum IS NULL THEN
	855	+ RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
	856	+ END IF;
	857	+
	858	+ /*
	859	+ * If we don't have per-table statistics, create new one which has NULL for
	860	+ * every statistic value for column_stats_effective.
	861	+ */
	862	+ IF NOT EXISTS(SELECT * FROM dbms_stats.relation_stats_locked ru
	863	+ WHERE ru.relid = $1 FOR SHARE) THEN
	864	+ INSERT INTO dbms_stats.relation_stats_locked
	865	+ SELECT $1, dbms_stats.relname(nspname, relname),
	866	+ NULL, NULL, NULL, NULL, NULL
	867	+ FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
	868	+ WHERE c.relnamespace = n.oid
	869	+ AND c.oid = $1;
	870	+ END IF;
	871	+
	872	+ /*
	873	+ * Process for per-column statistics
	874	+ */
	875	+ FOR r IN
	876	+ SELECT stainherit, stanullfrac, stawidth, stadistinct,
	877	+ stakind1, stakind2, stakind3, stakind4, stakind5,
	878	+ staop1, staop2, staop3, staop4, staop5,
	879	+ stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
	880	+ stavalues1, stavalues2, stavalues3, stavalues4, stavalues5
	881	+ FROM dbms_stats.column_stats_effective
	882	+ WHERE starelid = $1
	883	+ AND staattnum = set_attnum
	884	+ LOOP
	885	+ UPDATE dbms_stats.column_stats_locked c
	886	+ SET stanullfrac = r.stanullfrac,
	887	+ stawidth = r.stawidth,
	888	+ stadistinct = r.stadistinct,
	889	+ stakind1 = r.stakind1,
	890	+ stakind2 = r.stakind2,
	891	+ stakind3 = r.stakind3,
	892	+ stakind4 = r.stakind4,
	893	+ stakind5 = r.stakind5,
	894	+ staop1 = r.staop1,
	895	+ staop2 = r.staop2,
	896	+ staop3 = r.staop3,
	897	+ staop4 = r.staop4,
	898	+ staop5 = r.staop5,
	899	+ stanumbers1 = r.stanumbers1,
	900	+ stanumbers2 = r.stanumbers2,
	901	+ stanumbers3 = r.stanumbers3,
	902	+ stanumbers4 = r.stanumbers4,
	903	+ stanumbers5 = r.stanumbers5,
	904	+ stavalues1 = r.stavalues1,
	905	+ stavalues2 = r.stavalues2,
	906	+ stavalues3 = r.stavalues3,
	907	+ stavalues4 = r.stavalues4,
	908	+ stavalues5 = r.stavalues5
	909	+ WHERE c.starelid = $1
	910	+ AND c.staattnum = set_attnum
	911	+ AND c.stainherit = r.stainherit;
	912	+
	913	+ IF NOT FOUND THEN
	914	+ INSERT INTO dbms_stats.column_stats_locked
	915	+ VALUES ($1,
	916	+ set_attnum,
	917	+ r.stainherit,
	918	+ r.stanullfrac,
	919	+ r.stawidth,
	920	+ r.stadistinct,
	921	+ r.stakind1,
	922	+ r.stakind2,
	923	+ r.stakind3,
	924	+ r.stakind4,
	925	+ r.stakind5,
	926	+ r.staop1,
	927	+ r.staop2,
	928	+ r.staop3,
	929	+ r.staop4,
	930	+ r.staop5,
	931	+ r.stanumbers1,
	932	+ r.stanumbers2,
	933	+ r.stanumbers3,
	934	+ r.stanumbers4,
	935	+ r.stanumbers5,
	936	+ r.stavalues1,
	937	+ r.stavalues2,
	938	+ r.stavalues3,
	939	+ r.stavalues4,
	940	+ r.stavalues5);
	941	+ END IF;
	942	+ END LOOP;
	943	+
	944	+ /* If we don't have statistics at all, raise error. */
	945	+ IF NOT FOUND THEN
	946	+ RAISE EXCEPTION 'no statistics available for column "%" of relation "%"', $2, $1::regclass;
	947	+ END IF;
	948	+
	949	+ RETURN $1;
	950	+EXCEPTION
	951	+ WHEN unique_violation THEN
	952	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
	953	+END;
	954	+$$
	955	+LANGUAGE plpgsql;
	956	+
	957	+CREATE FUNCTION dbms_stats.lock(relid regclass)
	958	+ RETURNS regclass AS
	959	+$$
	960	+DECLARE
	961	+ lock_relkind "char";
	962	+ i record;
	963	+BEGIN
	964	+ IF $1 IS NULL THEN
	965	+ RAISE EXCEPTION 'relation required';
	966	+ END IF;
	967	+ SELECT relkind INTO lock_relkind FROM pg_catalog.pg_class WHERE oid = $1;
	968	+ IF NOT FOUND THEN
	969	+ RAISE EXCEPTION 'relation "%" not found', $1;
	970	+ END IF;
	971	+ IF NOT dbms_stats.is_target_relkind(lock_relkind) THEN
	972	+ RAISE EXCEPTION 'locking statistics is not allowed for relations with relkind "%": "%"', lock_relkind, $1
	973	+ USING HINT = 'Only tables(r, m, f) and indexes(i) are lockable.';
	974	+ END IF;
	975	+ IF dbms_stats.is_system_catalog($1) THEN
	976	+ RAISE EXCEPTION 'locking statistics is not allowed for system catalogs: "%"', $1;
	977	+ END IF;
	978	+
	979	+ UPDATE dbms_stats.relation_stats_locked r
	980	+ SET relname = dbms_stats.relname(nspname, c.relname),
	981	+ relpages = v.relpages,
	982	+ reltuples = v.reltuples,
	983	+ relallvisible = v.relallvisible,
	984	+ curpages = v.curpages,
	985	+ last_analyze = v.last_analyze,
	986	+ last_autoanalyze = v.last_autoanalyze
	987	+ FROM pg_catalog.pg_class c,
	988	+ pg_catalog.pg_namespace n,
	989	+ dbms_stats.relation_stats_effective v
	990	+ WHERE r.relid = $1
	991	+ AND c.oid = $1
	992	+ AND c.relnamespace = n.oid
	993	+ AND v.relid = $1;
	994	+ IF NOT FOUND THEN
	995	+ INSERT INTO dbms_stats.relation_stats_locked
	996	+ SELECT $1, dbms_stats.relname(nspname, c.relname),
	997	+ v.relpages, v.reltuples, v.relallvisible, v.curpages,
	998	+ v.last_analyze, v.last_autoanalyze
	999	+ FROM pg_catalog.pg_class c,
	1000	+ pg_catalog.pg_namespace n,
	1001	+ dbms_stats.relation_stats_effective v
	1002	+ WHERE c.oid = $1
	1003	+ AND c.relnamespace = n.oid
	1004	+ AND v.relid = $1;
	1005	+ END IF;
	1006	+
	1007	+ IF EXISTS(SELECT *
	1008	+ FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_index ind
	1009	+ ON c.oid = ind.indexrelid
	1010	+ WHERE c.oid = $1
	1011	+ AND c.relkind = 'i'
	1012	+ AND ind.indexprs IS NULL) THEN
	1013	+ RETURN $1;
	1014	+ END IF;
	1015	+
	1016	+ FOR i IN
	1017	+ SELECT staattnum, stainherit, stanullfrac,
	1018	+ stawidth, stadistinct,
	1019	+ stakind1, stakind2, stakind3, stakind4, stakind5,
	1020	+ staop1, staop2, staop3, staop4, staop5,
	1021	+ stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
	1022	+ stavalues1, stavalues2, stavalues3, stavalues4, stavalues5
	1023	+ FROM dbms_stats.column_stats_effective
	1024	+ WHERE starelid = $1
	1025	+ LOOP
	1026	+ UPDATE dbms_stats.column_stats_locked c
	1027	+ SET stanullfrac = i.stanullfrac,
	1028	+ stawidth = i.stawidth,
	1029	+ stadistinct = i.stadistinct,
	1030	+ stakind1 = i.stakind1,
	1031	+ stakind2 = i.stakind2,
	1032	+ stakind3 = i.stakind3,
	1033	+ stakind4 = i.stakind4,
	1034	+ stakind5 = i.stakind5,
	1035	+ staop1 = i.staop1,
	1036	+ staop2 = i.staop2,
	1037	+ staop3 = i.staop3,
	1038	+ staop4 = i.staop4,
	1039	+ staop5 = i.staop5,
	1040	+ stanumbers1 = i.stanumbers1,
	1041	+ stanumbers2 = i.stanumbers2,
	1042	+ stanumbers3 = i.stanumbers3,
	1043	+ stanumbers4 = i.stanumbers4,
	1044	+ stanumbers5 = i.stanumbers5,
	1045	+ stavalues1 = i.stavalues1,
	1046	+ stavalues2 = i.stavalues2,
	1047	+ stavalues3 = i.stavalues3,
	1048	+ stavalues4 = i.stavalues4,
	1049	+ stavalues5 = i.stavalues5
	1050	+ WHERE c.starelid = $1
	1051	+ AND c.staattnum = i.staattnum
	1052	+ AND c.stainherit = i.stainherit;
	1053	+
	1054	+ IF NOT FOUND THEN
	1055	+ INSERT INTO dbms_stats.column_stats_locked
	1056	+ VALUES ($1,
	1057	+ i.staattnum,
	1058	+ i.stainherit,
	1059	+ i.stanullfrac,
	1060	+ i.stawidth,
	1061	+ i.stadistinct,
	1062	+ i.stakind1,
	1063	+ i.stakind2,
	1064	+ i.stakind3,
	1065	+ i.stakind4,
	1066	+ i.stakind5,
	1067	+ i.staop1,
	1068	+ i.staop2,
	1069	+ i.staop3,
	1070	+ i.staop4,
	1071	+ i.staop5,
	1072	+ i.stanumbers1,
	1073	+ i.stanumbers2,
	1074	+ i.stanumbers3,
	1075	+ i.stanumbers4,
	1076	+ i.stanumbers5,
	1077	+ i.stavalues1,
	1078	+ i.stavalues2,
	1079	+ i.stavalues3,
	1080	+ i.stavalues4,
	1081	+ i.stavalues5);
	1082	+ END IF;
	1083	+ END LOOP;
	1084	+
	1085	+ RETURN $1;
	1086	+EXCEPTION
	1087	+ WHEN unique_violation THEN
	1088	+ RAISE EXCEPTION 'This operation is canceled by simultaneous lock operation on the same relation.';
	1089	+END;
	1090	+$$
	1091	+LANGUAGE plpgsql;
	1092	+
	1093	+CREATE FUNCTION dbms_stats.lock_database_stats()
	1094	+ RETURNS SETOF regclass AS
	1095	+$$
	1096	+SELECT dbms_stats.lock(c.oid)
	1097	+ FROM (SELECT oid
	1098	+ FROM pg_catalog.pg_class
	1099	+ WHERE NOT dbms_stats.is_system_catalog(oid)
	1100	+ AND dbms_stats.is_target_relkind(relkind)
	1101	+ ORDER BY pg_class.oid
	1102	+ ) c;
	1103	+$$
	1104	+LANGUAGE sql;
	1105	+
	1106	+CREATE FUNCTION dbms_stats.lock_schema_stats(
	1107	+ schemaname text
	1108	+) RETURNS SETOF regclass AS
	1109	+$$
	1110	+BEGIN
	1111	+ IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
	1112	+ RAISE EXCEPTION 'schema "%" not found', $1;
	1113	+ END IF;
	1114	+ IF dbms_stats.is_system_schema($1) THEN
	1115	+ RAISE EXCEPTION 'locking statistics is not allowed for system schemas: "%"', $1;
	1116	+ END IF;
	1117	+
	1118	+ RETURN QUERY
	1119	+ SELECT dbms_stats.lock(cn.oid)
	1120	+ FROM (SELECT c.oid
	1121	+ FROM pg_class c, pg_namespace n
	1122	+ WHERE c.relnamespace = n.oid
	1123	+ AND dbms_stats.is_target_relkind(c.relkind)
	1124	+ AND n.nspname = $1
	1125	+ ORDER BY c.oid
	1126	+ ) cn;
	1127	+END;
	1128	+$$
	1129	+LANGUAGE plpgsql STRICT;
	1130	+
	1131	+CREATE FUNCTION dbms_stats.lock_table_stats(relid regclass)
	1132	+ RETURNS regclass AS
	1133	+$$
	1134	+SELECT dbms_stats.lock($1)
	1135	+$$
	1136	+LANGUAGE sql STRICT;
	1137	+
	1138	+CREATE FUNCTION dbms_stats.lock_table_stats(
	1139	+ schemaname text,
	1140	+ tablename text
	1141	+) RETURNS regclass AS
	1142	+$$
	1143	+SELECT dbms_stats.lock(dbms_stats.relname($1, $2)::regclass)
	1144	+$$
	1145	+LANGUAGE sql STRICT;
	1146	+
	1147	+CREATE FUNCTION dbms_stats.lock_column_stats(
	1148	+ relid regclass,
	1149	+ attname text
	1150	+) RETURNS regclass AS
	1151	+$$
	1152	+SELECT dbms_stats.lock($1, $2)
	1153	+$$
	1154	+LANGUAGE sql STRICT;
	1155	+
	1156	+CREATE FUNCTION dbms_stats.lock_column_stats(
	1157	+ schemaname text,
	1158	+ tablename text,
	1159	+ attname text
	1160	+) RETURNS regclass AS
	1161	+$$
	1162	+SELECT dbms_stats.lock(dbms_stats.relname($1, $2)::regclass, $3)
	1163	+$$
	1164	+LANGUAGE sql STRICT;
	1165	+
	1166	+--
	1167	+-- UNLOCK_STATS: Statistics unlock functions
	1168	+--
	1169	+
	1170	+CREATE FUNCTION dbms_stats.unlock(
	1171	+ relid regclass DEFAULT NULL,
	1172	+ attname text DEFAULT NULL
	1173	+) RETURNS SETOF regclass AS
	1174	+$$
	1175	+DECLARE
	1176	+ set_attnum int2;
	1177	+ unlock_id int8;
	1178	+BEGIN
	1179	+ IF $1 IS NULL AND $2 IS NOT NULL THEN
	1180	+ RAISE EXCEPTION 'relation required';
	1181	+ END IF;
	1182	+
	1183	+ /*
	1184	+ * Lock the target relation to prevent conflicting with stats lock/restore
	1185	+ */
	1186	+ PERFORM * FROM dbms_stats.relation_stats_locked ru
	1187	+ WHERE (ru.relid = $1 OR $1 IS NULL) FOR UPDATE;
	1188	+
	1189	+ SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
	1190	+ WHERE a.attrelid = $1 AND a.attname = $2;
	1191	+ IF $2 IS NOT NULL AND set_attnum IS NULL THEN
	1192	+ RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
	1193	+ END IF;
	1194	+
	1195	+ DELETE FROM dbms_stats.column_stats_locked
	1196	+ WHERE (starelid = $1 OR $1 IS NULL)
	1197	+ AND (staattnum = set_attnum OR $2 IS NULL);
	1198	+
	1199	+ IF $1 IS NOT NULL AND $2 IS NOT NULL THEN
	1200	+ RETURN QUERY
	1201	+ SELECT $1;
	1202	+ END IF;
	1203	+ FOR unlock_id IN
	1204	+ SELECT ru.relid
	1205	+ FROM dbms_stats.relation_stats_locked ru
	1206	+ WHERE (ru.relid = $1 OR $1 IS NULL) AND ($2 IS NULL)
	1207	+ ORDER BY ru.relid
	1208	+ LOOP
	1209	+ DELETE FROM dbms_stats.relation_stats_locked ru
	1210	+ WHERE ru.relid = unlock_id;
	1211	+ RETURN NEXT unlock_id;
	1212	+ END LOOP;
	1213	+END;
	1214	+$$
	1215	+LANGUAGE plpgsql;
	1216	+
	1217	+CREATE FUNCTION dbms_stats.unlock_database_stats()
	1218	+ RETURNS SETOF regclass AS
	1219	+$$
	1220	+DECLARE
	1221	+ unlock_id int8;
	1222	+BEGIN
	1223	+ LOCK dbms_stats.relation_stats_locked IN EXCLUSIVE MODE;
	1224	+
	1225	+ FOR unlock_id IN
	1226	+ SELECT relid
	1227	+ FROM dbms_stats.relation_stats_locked
	1228	+ ORDER BY relid
	1229	+ LOOP
	1230	+ DELETE FROM dbms_stats.relation_stats_locked
	1231	+ WHERE relid = unlock_id;
	1232	+ RETURN NEXT unlock_id;
	1233	+ END LOOP;
	1234	+END;
	1235	+$$
	1236	+LANGUAGE plpgsql STRICT;
	1237	+
	1238	+CREATE FUNCTION dbms_stats.unlock_schema_stats(
	1239	+ schemaname text
	1240	+) RETURNS SETOF regclass AS
	1241	+$$
	1242	+DECLARE
	1243	+ unlock_id int8;
	1244	+BEGIN
	1245	+ IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
	1246	+ RAISE EXCEPTION 'schema "%" not found', $1;
	1247	+ END IF;
	1248	+ IF dbms_stats.is_system_schema($1) THEN
	1249	+ RAISE EXCEPTION 'unlocking statistics is not allowed for system schemas: "%"', $1;
	1250	+ END IF;
	1251	+
	1252	+ FOR unlock_id IN
	1253	+ SELECT r.relid
	1254	+ FROM dbms_stats.relation_stats_locked r, pg_class c, pg_namespace n
	1255	+ WHERE relid = c.oid
	1256	+ AND c.relnamespace = n.oid
	1257	+ AND n.nspname = $1
	1258	+ ORDER BY relid
	1259	+ FOR UPDATE
	1260	+ LOOP
	1261	+ DELETE FROM dbms_stats.relation_stats_locked
	1262	+ WHERE relid = unlock_id;
	1263	+ RETURN NEXT unlock_id;
	1264	+ END LOOP;
	1265	+END;
	1266	+$$
	1267	+LANGUAGE plpgsql STRICT;
	1268	+
	1269	+CREATE FUNCTION dbms_stats.unlock_table_stats(relid regclass)
	1270	+ RETURNS SETOF regclass AS
	1271	+$$
	1272	+DELETE FROM dbms_stats.relation_stats_locked
	1273	+ WHERE relid = $1
	1274	+ RETURNING relid::regclass
	1275	+$$
	1276	+LANGUAGE sql STRICT;
	1277	+
	1278	+CREATE FUNCTION dbms_stats.unlock_table_stats(
	1279	+ schemaname text,
	1280	+ tablename text
	1281	+) RETURNS SETOF regclass AS
	1282	+$$
	1283	+DELETE FROM dbms_stats.relation_stats_locked
	1284	+ WHERE relid = dbms_stats.relname($1, $2)::regclass
	1285	+ RETURNING relid::regclass
	1286	+$$
	1287	+LANGUAGE sql STRICT;
	1288	+
	1289	+CREATE FUNCTION dbms_stats.unlock_column_stats(
	1290	+ relid regclass,
	1291	+ attname text
	1292	+) RETURNS SETOF regclass AS
	1293	+$$
	1294	+DECLARE
	1295	+ set_attnum int2;
	1296	+BEGIN
	1297	+ SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
	1298	+ WHERE a.attrelid = $1 AND a.attname = $2;
	1299	+ IF $2 IS NOT NULL AND set_attnum IS NULL THEN
	1300	+ RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
	1301	+ END IF;
	1302	+
	1303	+ /* Lock the locked table stats */
	1304	+ PERFORM * from dbms_stats.relation_stats_locked r
	1305	+ WHERE r.relid = $1 FOR SHARE;
	1306	+
	1307	+ DELETE FROM dbms_stats.column_stats_locked
	1308	+ WHERE starelid = $1
	1309	+ AND staattnum = set_attnum;
	1310	+
	1311	+ RETURN QUERY
	1312	+ SELECT $1;
	1313	+END;
	1314	+$$
	1315	+LANGUAGE plpgsql STRICT;
	1316	+
	1317	+CREATE FUNCTION dbms_stats.unlock_column_stats(
	1318	+ schemaname text,
	1319	+ tablename text,
	1320	+ attname text
	1321	+) RETURNS SETOF regclass AS
	1322	+$$
	1323	+DECLARE
	1324	+ set_attnum int2;
	1325	+BEGIN
	1326	+ SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
	1327	+ WHERE a.attrelid = dbms_stats.relname($1, $2)::regclass
	1328	+ AND a.attname = $3;
	1329	+ IF $3 IS NOT NULL AND set_attnum IS NULL THEN
	1330	+ RAISE EXCEPTION 'column "%" not found in relation "%.%"', $3, $1, $2;
	1331	+ END IF;
	1332	+
	1333	+ /* Lock the locked table stats */
	1334	+ PERFORM * from dbms_stats.relation_stats_locked r
	1335	+ WHERE relid = dbms_stats.relname($1, $2)::regclass FOR SHARE;
	1336	+
	1337	+ DELETE FROM dbms_stats.column_stats_locked
	1338	+ WHERE starelid = dbms_stats.relname($1, $2)::regclass
	1339	+ AND staattnum = set_attnum;
	1340	+
	1341	+ RETURN QUERY
	1342	+ SELECT dbms_stats.relname($1, $2)::regclass;
	1343	+END;
	1344	+$$
	1345	+LANGUAGE plpgsql STRICT;
	1346	+
	1347	+--
	1348	+-- IMPORT_STATS: Statistics import functions
	1349	+--
	1350	+
	1351	+CREATE FUNCTION dbms_stats.import(
	1352	+ nspname text DEFAULT NULL,
	1353	+ relid regclass DEFAULT NULL,
	1354	+ attname text DEFAULT NULL,
	1355	+ src text DEFAULT NULL
	1356	+) RETURNS void AS
	1357	+'MODULE_PATHNAME', 'dbms_stats_import'
	1358	+LANGUAGE C;
	1359	+
	1360	+CREATE FUNCTION dbms_stats.import_database_stats(src text)
	1361	+ RETURNS void AS
	1362	+$$
	1363	+SELECT dbms_stats.import(NULL, NULL, NULL, $1)
	1364	+$$
	1365	+LANGUAGE sql;
	1366	+
	1367	+CREATE FUNCTION dbms_stats.import_schema_stats(
	1368	+ schemaname text,
	1369	+ src text
	1370	+) RETURNS void AS
	1371	+$$
	1372	+SELECT dbms_stats.import($1, NULL, NULL, $2)
	1373	+$$
	1374	+LANGUAGE sql;
	1375	+
	1376	+CREATE FUNCTION dbms_stats.import_table_stats(
	1377	+ relid regclass,
	1378	+ src text
	1379	+) RETURNS void AS
	1380	+$$
	1381	+SELECT dbms_stats.import(NULL, $1, NULL, $2)
	1382	+$$
	1383	+LANGUAGE sql;
	1384	+
	1385	+CREATE FUNCTION dbms_stats.import_table_stats(
	1386	+ schemaname text,
	1387	+ tablename text,
	1388	+ src text
	1389	+) RETURNS void AS
	1390	+$$
	1391	+SELECT dbms_stats.import(NULL, dbms_stats.relname($1, $2)::regclass, NULL, $3)
	1392	+$$
	1393	+LANGUAGE sql;
	1394	+
	1395	+CREATE FUNCTION dbms_stats.import_column_stats(
	1396	+ relid regclass,
	1397	+ attname text,
	1398	+ src text
	1399	+) RETURNS void AS
	1400	+$$
	1401	+SELECT dbms_stats.import(NULL, $1, $2, $3)
	1402	+$$
	1403	+LANGUAGE sql;
	1404	+
	1405	+CREATE FUNCTION dbms_stats.import_column_stats(
	1406	+ schemaname text,
	1407	+ tablename text,
	1408	+ attname text,
	1409	+ src text
	1410	+) RETURNS void AS
	1411	+$$
	1412	+SELECT dbms_stats.import(NULL, dbms_stats.relname($1, $2)::regclass, $3, $4)
	1413	+$$
	1414	+LANGUAGE sql;
	1415	+
	1416	+--
	1417	+-- PURGE_STATS: Statistics purge function
	1418	+--
	1419	+CREATE FUNCTION dbms_stats.purge_stats(
	1420	+ backup_id int8,
	1421	+ force bool DEFAULT false
	1422	+) RETURNS SETOF dbms_stats.backup_history AS
	1423	+$$
	1424	+DECLARE
	1425	+ delete_id int8;
	1426	+ todelete dbms_stats.backup_history;
	1427	+BEGIN
	1428	+ IF $1 IS NULL THEN
	1429	+ RAISE EXCEPTION 'backup id required';
	1430	+ END IF;
	1431	+ IF $2 IS NULL THEN
	1432	+ RAISE EXCEPTION 'NULL is not allowed as the second parameter';
	1433	+ END IF;
	1434	+
	1435	+ IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
	1436	+ WHERE id = $1 FOR UPDATE) THEN
	1437	+ RAISE EXCEPTION 'backup id % not found', $1;
	1438	+ END IF;
	1439	+ IF NOT $2 AND NOT EXISTS(SELECT *
	1440	+ FROM dbms_stats.backup_history
	1441	+ WHERE unit = 'd'
	1442	+ AND id > $1) THEN
	1443	+ RAISE WARNING 'no database-wide backup will remain after purge'
	1444	+ USING HINT = 'Give true for second parameter to purge forcibly.';
	1445	+ RETURN;
	1446	+ END IF;
	1447	+
	1448	+ FOR todelete IN
	1449	+ SELECT * FROM dbms_stats.backup_history
	1450	+ WHERE id <= $1
	1451	+ ORDER BY id FOR UPDATE
	1452	+ LOOP
	1453	+ DELETE FROM dbms_stats.backup_history
	1454	+ WHERE id = todelete.id;
	1455	+ RETURN NEXT todelete;
	1456	+ END LOOP;
	1457	+END;
	1458	+$$
	1459	+LANGUAGE plpgsql;
	1460	+
	1461	+--
	1462	+-- CLEAN_STATS: Clean orphan dummy statistics
	1463	+--
	1464	+CREATE FUNCTION dbms_stats.clean_up_stats() RETURNS SETOF text AS
	1465	+$$
	1466	+DECLARE
	1467	+ clean_relid Oid;
	1468	+ clean_attnum int2;
	1469	+ clean_inherit bool;
	1470	+ clean_rel_col text;
	1471	+BEGIN
	1472	+ -- We don't have to check that table-level dummy statistics of the table
	1473	+ -- exists here, because the foreign key constraints defined on column-level
	1474	+ -- dummy static table ensures that.
	1475	+ FOR clean_rel_col, clean_relid, clean_attnum, clean_inherit IN
	1476	+ SELECT r.relname \|\| ', ' \|\| v.staattnum::text,
	1477	+ v.starelid, v.staattnum, v.stainherit
	1478	+ FROM dbms_stats.column_stats_locked v
	1479	+ JOIN dbms_stats.relation_stats_locked r ON (v.starelid = r.relid)
	1480	+ WHERE NOT EXISTS (
	1481	+ SELECT NULL
	1482	+ FROM pg_attribute a
	1483	+ WHERE a.attrelid = v.starelid
	1484	+ AND a.attnum = v.staattnum
	1485	+ AND a.attisdropped = false
	1486	+ FOR UPDATE
	1487	+ )
	1488	+ LOOP
	1489	+ DELETE FROM dbms_stats.column_stats_locked
	1490	+ WHERE starelid = clean_relid
	1491	+ AND staattnum = clean_attnum
	1492	+ AND stainherit = clean_inherit;
	1493	+ RETURN NEXT clean_rel_col;
	1494	+ END LOOP;
	1495	+
	1496	+ RETURN QUERY
	1497	+ DELETE FROM dbms_stats.relation_stats_locked r
	1498	+ WHERE NOT EXISTS (
	1499	+ SELECT NULL
	1500	+ FROM pg_class c
	1501	+ WHERE c.oid = r.relid)
	1502	+ RETURNING relname \|\| ',';
	1503	+ RETURN;
	1504	+END
	1505	+$$
	1506	+LANGUAGE plpgsql;
	1507	+--

pg_dbms_stats Fork

Commit

Tags

Frequently used words (click to add to your profile)

Commit MetaInfo

Log Message

Ändern Zusammenfassung

Diff

pg_dbms_stats
Fork