1 /* pg_dbms_stats/pg_dbms_stats--1.3.11.sql */
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
6 -- define alias of anyarray type because parser does not allow to use
7 -- anyarray in type definitions.
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,
29 -- User defined stats tables
32 CREATE TABLE dbms_stats.relation_stats_locked (
34 relname text NOT NULL,
39 last_analyze timestamp with time zone,
40 last_autoanalyze timestamp with time zone,
44 CREATE TABLE dbms_stats.column_stats_locked (
45 starelid oid NOT NULL,
46 staattnum int2 NOT NULL,
47 stainherit bool NOT NULL,
71 stavalues1 dbms_stats.anyarray,
72 stavalues2 dbms_stats.anyarray,
73 stavalues3 dbms_stats.anyarray,
74 stavalues4 dbms_stats.anyarray,
75 stavalues5 dbms_stats.anyarray,
76 PRIMARY KEY (starelid, staattnum, stainherit),
77 FOREIGN KEY (starelid) REFERENCES dbms_stats.relation_stats_locked (relid) ON DELETE CASCADE
81 -- Statistics backup tables
84 CREATE TABLE dbms_stats.backup_history (
85 id serial8 PRIMARY KEY,
86 time timestamp with time zone NOT NULL,
87 unit char(1) NOT NULL,
91 CREATE TABLE dbms_stats.relation_stats_backup (
94 relname text NOT NULL,
95 relpages int4 NOT NULL,
96 reltuples float4 NOT NULL,
97 relallvisible int4 NOT NULL,
98 curpages int4 NOT NULL,
99 last_analyze timestamp with time zone,
100 last_autoanalyze timestamp with time zone,
101 PRIMARY KEY (id, relid),
102 FOREIGN KEY (id) REFERENCES dbms_stats.backup_history (id) ON DELETE CASCADE
105 CREATE TABLE dbms_stats.column_stats_backup (
107 statypid oid NOT NULL,
108 starelid oid NOT NULL,
109 staattnum int2 NOT NULL,
110 stainherit bool NOT NULL,
111 stanullfrac float4 NOT NULL,
112 stawidth int4 NOT NULL,
113 stadistinct float4 NOT NULL,
114 stakind1 int2 NOT NULL,
115 stakind2 int2 NOT NULL,
116 stakind3 int2 NOT NULL,
117 stakind4 int2 NOT NULL,
118 stakind5 int2 NOT NULL,
124 stacoll1 oid NOT NULL,
125 stacoll2 oid NOT NULL,
126 stacoll3 oid NOT NULL,
127 stacoll4 oid NOT NULL,
128 stacoll5 oid NOT NULL,
129 stanumbers1 float4[],
130 stanumbers2 float4[],
131 stanumbers3 float4[],
132 stanumbers4 float4[],
133 stanumbers5 float4[],
134 stavalues1 dbms_stats.anyarray,
135 stavalues2 dbms_stats.anyarray,
136 stavalues3 dbms_stats.anyarray,
137 stavalues4 dbms_stats.anyarray,
138 stavalues5 dbms_stats.anyarray,
139 PRIMARY KEY (id, starelid, staattnum, stainherit),
140 FOREIGN KEY (id) REFERENCES dbms_stats.backup_history (id) ON DELETE CASCADE,
141 FOREIGN KEY (id, starelid) REFERENCES dbms_stats.relation_stats_backup (id, relid) ON DELETE CASCADE
148 CREATE FUNCTION dbms_stats.relname(nspname text, relname text)
150 $$SELECT pg_catalog.quote_ident($1) || '.' || pg_catalog.quote_ident($2)$$
151 LANGUAGE sql STABLE STRICT;
153 CREATE FUNCTION dbms_stats.is_system_schema(schemaname text)
155 'MODULE_PATHNAME', 'dbms_stats_is_system_schema'
156 LANGUAGE C IMMUTABLE STRICT;
158 CREATE FUNCTION dbms_stats.is_system_catalog(relid regclass)
160 'MODULE_PATHNAME', 'dbms_stats_is_system_catalog'
163 CREATE FUNCTION dbms_stats.is_target_relkind(relkind "char")
165 $$SELECT $1 IN ('r', 'i', 'f', 'm')$$
168 CREATE FUNCTION dbms_stats.merge(
169 lhs dbms_stats.column_stats_locked,
170 rhs pg_catalog.pg_statistic
171 ) RETURNS dbms_stats.column_stats_locked AS
172 'MODULE_PATHNAME', 'dbms_stats_merge'
175 CREATE VIEW dbms_stats.relation_stats_effective AS
178 dbms_stats.relname(nspname, c.relname) AS relname,
179 COALESCE(v.relpages, c.relpages) AS relpages,
180 COALESCE(v.reltuples, c.reltuples) AS reltuples,
181 COALESCE(v.relallvisible, c.relallvisible) AS relallvisible,
183 (pg_catalog.pg_relation_size(c.oid) /
184 pg_catalog.current_setting('block_size')::int4)::int4)
186 COALESCE(v.last_analyze,
187 pg_catalog.pg_stat_get_last_analyze_time(c.oid))
189 COALESCE(v.last_autoanalyze,
190 pg_catalog.pg_stat_get_last_autoanalyze_time(c.oid))
192 FROM pg_catalog.pg_class c
193 JOIN pg_catalog.pg_namespace n
194 ON c.relnamespace = n.oid
195 LEFT JOIN dbms_stats.relation_stats_locked v
197 WHERE dbms_stats.is_target_relkind(c.relkind)
198 AND NOT dbms_stats.is_system_schema(nspname);
200 CREATE VIEW dbms_stats.column_stats_effective AS
202 SELECT (dbms_stats.merge(v, s)).*
203 FROM pg_catalog.pg_statistic s
204 FULL JOIN dbms_stats.column_stats_locked v
205 USING (starelid, staattnum, stainherit)
206 WHERE NOT dbms_stats.is_system_catalog(starelid)
210 WHERE a.attrelid = starelid
211 AND a.attnum = staattnum
212 AND a.attisdropped = false
215 WHERE starelid IS NOT NULL;
218 -- Note: This view is copied from pg_stats in
219 -- src/backend/catalog/system_views.sql in core source tree of version
220 -- 9.5, and customized for pg_dbms_stats. Changes from orignal one are:
221 -- - rename from pg_stats to dbms_stats.stats by a view name.
222 -- - changed the table name from pg_statistic to dbms_stats.column_stats_effective.
224 CREATE VIEW dbms_stats.stats with (security_barrier) AS
226 nspname AS schemaname,
227 relname AS tablename,
229 stainherit AS inherited,
230 stanullfrac AS null_frac,
231 stawidth AS avg_width,
232 stadistinct AS n_distinct,
234 WHEN stakind1 = 1 THEN stavalues1
235 WHEN stakind2 = 1 THEN stavalues2
236 WHEN stakind3 = 1 THEN stavalues3
237 WHEN stakind4 = 1 THEN stavalues4
238 WHEN stakind5 = 1 THEN stavalues5
239 END AS most_common_vals,
241 WHEN stakind1 = 1 THEN stanumbers1
242 WHEN stakind2 = 1 THEN stanumbers2
243 WHEN stakind3 = 1 THEN stanumbers3
244 WHEN stakind4 = 1 THEN stanumbers4
245 WHEN stakind5 = 1 THEN stanumbers5
246 END AS most_common_freqs,
248 WHEN stakind1 = 2 THEN stavalues1
249 WHEN stakind2 = 2 THEN stavalues2
250 WHEN stakind3 = 2 THEN stavalues3
251 WHEN stakind4 = 2 THEN stavalues4
252 WHEN stakind5 = 2 THEN stavalues5
253 END AS histogram_bounds,
255 WHEN stakind1 = 3 THEN stanumbers1[1]
256 WHEN stakind2 = 3 THEN stanumbers2[1]
257 WHEN stakind3 = 3 THEN stanumbers3[1]
258 WHEN stakind4 = 3 THEN stanumbers4[1]
259 WHEN stakind5 = 3 THEN stanumbers5[1]
262 WHEN stakind1 = 4 THEN stavalues1
263 WHEN stakind2 = 4 THEN stavalues2
264 WHEN stakind3 = 4 THEN stavalues3
265 WHEN stakind4 = 4 THEN stavalues4
266 WHEN stakind5 = 4 THEN stavalues5
267 END AS most_common_elems,
269 WHEN stakind1 = 4 THEN stanumbers1
270 WHEN stakind2 = 4 THEN stanumbers2
271 WHEN stakind3 = 4 THEN stanumbers3
272 WHEN stakind4 = 4 THEN stanumbers4
273 WHEN stakind5 = 4 THEN stanumbers5
274 END AS most_common_elem_freqs,
276 WHEN stakind1 = 5 THEN stanumbers1
277 WHEN stakind2 = 5 THEN stanumbers2
278 WHEN stakind3 = 5 THEN stanumbers3
279 WHEN stakind4 = 5 THEN stanumbers4
280 WHEN stakind5 = 5 THEN stanumbers5
281 END AS elem_count_histogram
282 FROM dbms_stats.column_stats_effective s JOIN pg_class c ON (c.oid = s.starelid)
283 JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
284 LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
285 WHERE NOT attisdropped
286 AND pg_catalog.has_column_privilege(c.oid, a.attnum, 'select')
287 AND (c.relrowsecurity = false OR NOT pg_catalog.row_security_active(c.oid));
293 CREATE FUNCTION dbms_stats.invalidate_relation_cache()
295 'MODULE_PATHNAME', 'dbms_stats_invalidate_relation_cache'
298 -- Invalidate cached plans when dbms_stats.relation_stats_locked is modified.
299 CREATE TRIGGER invalidate_relation_cache
300 BEFORE INSERT OR DELETE OR UPDATE
301 ON dbms_stats.relation_stats_locked
302 FOR EACH ROW EXECUTE PROCEDURE dbms_stats.invalidate_relation_cache();
304 CREATE FUNCTION dbms_stats.invalidate_column_cache()
306 'MODULE_PATHNAME', 'dbms_stats_invalidate_column_cache'
309 -- Invalidate cached plans when dbms_stats.column_stats_locked is modified.
310 CREATE TRIGGER invalidate_column_cache
311 BEFORE INSERT OR DELETE OR UPDATE
312 ON dbms_stats.column_stats_locked
313 FOR EACH ROW EXECUTE PROCEDURE dbms_stats.invalidate_column_cache();
316 -- BACKUP_STATS: Statistics backup functions
319 CREATE FUNCTION dbms_stats.backup(
325 /* Lock the backup id */
326 SELECT * from dbms_stats.backup_history
327 WHERE id = $1 FOR UPDATE;
329 INSERT INTO dbms_stats.relation_stats_backup
330 SELECT $1, v.relid, v.relname, v.relpages, v.reltuples, v.relallvisible,
331 v.curpages, v.last_analyze, v.last_autoanalyze
332 FROM pg_catalog.pg_class c,
333 dbms_stats.relation_stats_effective v
334 WHERE c.oid = v.relid
335 AND dbms_stats.is_target_relkind(relkind)
336 AND NOT dbms_stats.is_system_catalog(v.relid)
337 AND (v.relid = $2 OR $2 IS NULL);
339 INSERT INTO dbms_stats.column_stats_backup
340 SELECT $1, atttypid, s.*
341 FROM pg_catalog.pg_class c,
342 dbms_stats.column_stats_effective s,
343 pg_catalog.pg_attribute a
344 WHERE c.oid = starelid
345 AND starelid = attrelid
346 AND staattnum = attnum
347 AND dbms_stats.is_target_relkind(relkind)
348 AND NOT dbms_stats.is_system_catalog(c.oid)
349 AND ($2 IS NULL OR starelid = $2)
350 AND ($3 IS NULL OR staattnum = $3);
356 CREATE FUNCTION dbms_stats.backup(
357 relid regclass DEFAULT NULL,
358 attname text DEFAULT NULL,
359 comment text DEFAULT NULL
364 backup_relkind "char";
368 IF $1 IS NULL AND $2 IS NOT NULL THEN
369 RAISE EXCEPTION 'relation required';
371 IF $1 IS NOT NULL THEN
372 SELECT relkind INTO backup_relkind
373 FROM pg_catalog.pg_class WHERE oid = $1 FOR SHARE;
375 RAISE EXCEPTION 'relation "%" not found', $1;
377 IF NOT dbms_stats.is_target_relkind(backup_relkind) THEN
378 RAISE EXCEPTION 'relation of relkind "%" cannot have statistics to backup: "%"',
380 USING HINT = 'Only tables(r), materialized views(m), foreign tables(f) and indexes(i) are allowed.';
382 IF dbms_stats.is_system_catalog($1) THEN
383 RAISE EXCEPTION 'backing up statistics is inhibited for system catalogs: "%"', $1;
385 IF $2 IS NOT NULL THEN
386 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
387 WHERE a.attrelid = $1 AND a.attname = $2 FOR SHARE;
388 IF set_attnum IS NULL THEN
389 RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
391 IF NOT EXISTS(SELECT * FROM dbms_stats.column_stats_effective WHERE starelid = $1 AND staattnum = set_attnum) THEN
392 RAISE EXCEPTION 'no statistics available for column "%" of relation "%"', $2, $1;
402 INSERT INTO dbms_stats.backup_history(time, unit, comment)
403 VALUES (current_timestamp, unit_type, $3)
404 RETURNING dbms_stats.backup(id, $1, set_attnum) INTO backup_id;
410 CREATE FUNCTION dbms_stats.backup_database_stats(
414 SELECT dbms_stats.backup(NULL, NULL, $1)
418 CREATE FUNCTION dbms_stats.backup_schema_stats(
426 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1 FOR SHARE)
428 RAISE EXCEPTION 'schema "%" not found', $1;
430 IF dbms_stats.is_system_schema($1) THEN
431 RAISE EXCEPTION 'backing up statistics is inhibited for system schemas: "%"', $1;
434 INSERT INTO dbms_stats.backup_history(time, unit, comment)
435 VALUES (current_timestamp, 's', comment)
436 RETURNING id INTO backup_id;
438 PERFORM dbms_stats.backup(backup_id, cn.oid, NULL)
440 FROM pg_catalog.pg_class c,
441 pg_catalog.pg_namespace n
442 WHERE n.nspname = schemaname
443 AND c.relnamespace = n.oid
444 AND dbms_stats.is_target_relkind(c.relkind)
453 CREATE FUNCTION dbms_stats.backup_table_stats(
458 SELECT dbms_stats.backup($1, NULL, $2)
462 CREATE FUNCTION dbms_stats.backup_table_stats(
468 SELECT dbms_stats.backup(dbms_stats.relname($1, $2)::regclass, NULL, $3)
472 CREATE FUNCTION dbms_stats.backup_column_stats(
478 SELECT dbms_stats.backup($1, $2, $3)
482 CREATE FUNCTION dbms_stats.backup_column_stats(
489 SELECT dbms_stats.backup(dbms_stats.relname($1, $2)::regclass, $3, $4)
494 -- RESTORE_STATS: Statistics restore functions
496 CREATE FUNCTION dbms_stats.restore(
498 relid regclass DEFAULT NULL,
499 attname text DEFAULT NULL
500 ) RETURNS SETOF regclass AS
504 restore_relid regclass;
507 restore_attname text;
508 restore_type regtype;
512 RAISE EXCEPTION 'backup id required';
514 IF $2 IS NULL AND $3 IS NOT NULL THEN
515 RAISE EXCEPTION 'relation required';
517 IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
518 WHERE id <= $1 FOR SHARE) THEN
519 RAISE EXCEPTION 'backup id % not found', $1;
521 IF $2 IS NOT NULL THEN
522 IF NOT EXISTS(SELECT * FROM pg_catalog.pg_class
523 WHERE oid = $2 FOR SHARE) THEN
524 RAISE EXCEPTION 'relation "%" not found', $2;
526 -- Grabbing all backups for the relation which is not used in restore.
527 IF NOT EXISTS(SELECT * FROM dbms_stats.relation_stats_backup b
528 WHERE b.id <= $1 AND b.relid = $2 FOR SHARE) THEN
529 RAISE EXCEPTION 'statistics of relation "%" not found in any backups before backup id = %', $2, $1;
531 IF $3 IS NOT NULL THEN
532 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
533 WHERE a.attrelid = $2 AND a.attname = $3;
534 IF set_attnum IS NULL THEN
535 RAISE EXCEPTION 'column "%" not found in relation %', $3, $2;
537 IF NOT EXISTS(SELECT * FROM dbms_stats.column_stats_backup WHERE id <= $1 AND starelid = $2 AND staattnum = set_attnum) THEN
538 RAISE EXCEPTION 'statistics of column "%" of relation "%" are not found in any backups before backup id = %',$3, $2, $1;
541 PERFORM * FROM dbms_stats.relation_stats_locked r
542 WHERE r.relid = $2 FOR UPDATE;
544 /* Lock the whole relation stats if relation is not specified.*/
545 LOCK dbms_stats.relation_stats_locked IN EXCLUSIVE MODE;
548 FOR restore_id, restore_relid IN
549 SELECT pg_catalog.max(id), coid FROM
550 (SELECT b.id as id, c.oid as coid
551 FROM pg_class c, dbms_stats.relation_stats_backup b
552 WHERE (c.oid = $2 OR $2 IS NULL)
554 AND dbms_stats.is_target_relkind(c.relkind)
555 AND NOT dbms_stats.is_system_catalog(c.oid)
559 ORDER BY coid::regclass::text
561 UPDATE dbms_stats.relation_stats_locked r
564 relpages = b.relpages,
565 reltuples = b.reltuples,
566 relallvisible = b.relallvisible,
567 curpages = b.curpages,
568 last_analyze = b.last_analyze,
569 last_autoanalyze = b.last_autoanalyze
570 FROM dbms_stats.relation_stats_backup b
571 WHERE r.relid = restore_relid
572 AND b.id = restore_id
573 AND b.relid = restore_relid;
575 INSERT INTO dbms_stats.relation_stats_locked
584 FROM dbms_stats.relation_stats_backup b
585 WHERE b.id = restore_id
586 AND b.relid = restore_relid;
588 RETURN NEXT restore_relid;
591 FOR restore_id, restore_relid, restore_attnum, restore_type, cur_type IN
592 SELECT t.id, t.oid, t.attnum, b.statypid, a.atttypid
594 dbms_stats.column_stats_backup b,
595 (SELECT pg_catalog.max(b.id) AS id, c.oid, a.attnum
596 FROM pg_class c, pg_attribute a,
597 dbms_stats.column_stats_backup b
598 WHERE (c.oid = $2 OR $2 IS NULL)
599 AND c.oid = a.attrelid
600 AND c.oid = b.starelid
601 AND (a.attnum = set_attnum OR set_attnum IS NULL)
602 AND a.attnum = b.staattnum
603 AND NOT a.attisdropped
604 AND dbms_stats.is_target_relkind(c.relkind)
606 GROUP BY c.oid, a.attnum) t
607 WHERE a.attrelid = t.oid
608 AND a.attnum = t.attnum
610 AND b.starelid = t.oid
611 AND b.staattnum = t.attnum
613 IF restore_type <> cur_type THEN
614 SELECT a.attname INTO restore_attname
615 FROM pg_catalog.pg_attribute a
616 WHERE a.attrelid = restore_relid
617 AND a.attnum = restore_attnum;
618 RAISE WARNING 'data type of column "%.%" is inconsistent between database(%) and backup (%). Skip.',
619 restore_relid, restore_attname, cur_type, restore_type;
621 DELETE FROM dbms_stats.column_stats_locked
622 WHERE starelid = restore_relid
623 AND staattnum = restore_attnum;
624 INSERT INTO dbms_stats.column_stats_locked
625 SELECT starelid, staattnum, stainherit,
626 stanullfrac, stawidth, stadistinct,
627 stakind1, stakind2, stakind3, stakind4, stakind5,
628 staop1, staop2, staop3, staop4, staop5,
629 stacoll1, stacoll2, stacoll3, stacoll4, stacoll5,
630 stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
631 stavalues1, stavalues2, stavalues3, stavalues4, stavalues5
632 FROM dbms_stats.column_stats_backup
633 WHERE id = restore_id
634 AND starelid = restore_relid
635 AND staattnum = restore_attnum;
639 WHEN unique_violation THEN
640 RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
645 CREATE FUNCTION dbms_stats.restore_database_stats(
646 as_of_timestamp timestamp with time zone
647 ) RETURNS SETOF regclass AS
649 SELECT dbms_stats.restore(m.id, m.relid)
650 FROM (SELECT pg_catalog.max(id) AS id, relid
651 FROM (SELECT r.id, r.relid
652 FROM pg_class c, dbms_stats.relation_stats_backup r,
653 dbms_stats.backup_history b
654 WHERE c.oid = r.relid
659 ORDER BY t1.relid) m;
663 CREATE FUNCTION dbms_stats.restore_schema_stats(
665 as_of_timestamp timestamp with time zone
666 ) RETURNS SETOF regclass AS
669 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
670 RAISE EXCEPTION 'schema "%" not found', $1;
672 IF dbms_stats.is_system_schema($1) THEN
673 RAISE EXCEPTION 'restoring statistics is inhibited for system schemas: "%"', $1;
677 SELECT dbms_stats.restore(m.id, m.relid)
678 FROM (SELECT pg_catalog.max(id) AS id, relid
679 FROM (SELECT r.id, r.relid
680 FROM pg_class c, pg_namespace n,
681 dbms_stats.relation_stats_backup r,
682 dbms_stats.backup_history b
683 WHERE c.oid = r.relid
684 AND c.relnamespace = n.oid
690 ORDER BY t1.relid) m;
693 LANGUAGE plpgsql STRICT;
695 CREATE FUNCTION dbms_stats.restore_table_stats(
697 as_of_timestamp timestamp with time zone
698 ) RETURNS SETOF regclass AS
700 SELECT dbms_stats.restore(pg_catalog.max(id), $1, NULL)
701 FROM dbms_stats.backup_history WHERE time <= $2
705 CREATE FUNCTION dbms_stats.restore_table_stats(
708 as_of_timestamp timestamp with time zone
709 ) RETURNS SETOF regclass AS
711 SELECT dbms_stats.restore_table_stats(dbms_stats.relname($1, $2)::regclass, $3)
715 CREATE FUNCTION dbms_stats.restore_column_stats(
718 as_of_timestamp timestamp with time zone
719 ) RETURNS SETOF regclass AS
721 SELECT dbms_stats.restore(pg_catalog.max(id), $1, $2)
722 FROM dbms_stats.backup_history WHERE time <= $3
726 CREATE FUNCTION dbms_stats.restore_column_stats(
730 as_of_timestamp timestamp with time zone
731 ) RETURNS SETOF regclass AS
733 SELECT dbms_stats.restore(pg_catalog.max(id),
734 dbms_stats.relname($1, $2)::regclass, $3)
735 FROM dbms_stats.backup_history WHERE time <= $4
739 CREATE FUNCTION dbms_stats.restore_stats(
741 ) RETURNS SETOF regclass AS
744 restore_relid regclass;
746 restore_attname text;
747 restore_type regtype;
750 IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id = $1) THEN
751 RAISE EXCEPTION 'backup id % not found', $1;
754 /* Lock the backup */
755 PERFORM * from dbms_stats.relation_stats_backup b
756 WHERE id = $1 FOR SHARE;
758 /* Locking only relation_stats_locked is sufficient */
759 LOCK dbms_stats.relation_stats_locked IN EXCLUSIVE MODE;
764 JOIN dbms_stats.relation_stats_backup b ON (c.oid = b.relid)
766 ORDER BY c.oid::regclass::text
768 UPDATE dbms_stats.relation_stats_locked r
771 relpages = b.relpages,
772 reltuples = b.reltuples,
773 relallvisible = b.relallvisible,
774 curpages = b.curpages,
775 last_analyze = b.last_analyze,
776 last_autoanalyze = b.last_autoanalyze
777 FROM dbms_stats.relation_stats_backup b
778 WHERE r.relid = restore_relid
780 AND b.relid = restore_relid;
782 INSERT INTO dbms_stats.relation_stats_locked
791 FROM dbms_stats.relation_stats_backup b
793 AND b.relid = restore_relid;
795 RETURN NEXT restore_relid;
798 FOR restore_relid, restore_attnum, restore_type, cur_type IN
799 SELECT c.oid, a.attnum, b.statypid, a.atttypid
801 JOIN dbms_stats.column_stats_backup b ON (c.oid = b.starelid)
802 JOIN pg_attribute a ON (b.starelid = attrelid
803 AND b.staattnum = a.attnum)
806 IF restore_type <> cur_type THEN
807 SELECT attname INTO restore_attname
808 FROM pg_catalog.pg_attribute
809 WHERE attrelid = restore_relid
810 AND attnum = restore_attnum;
811 RAISE WARNING 'data type of column "%.%" is inconsistent between database(%) and backup (%). Skip.',
812 restore_relid, restore_attname, cur_type, restore_type;
814 DELETE FROM dbms_stats.column_stats_locked
815 WHERE starelid = restore_relid
816 AND staattnum = restore_attnum;
817 INSERT INTO dbms_stats.column_stats_locked
818 SELECT starelid, staattnum, stainherit,
819 stanullfrac, stawidth, stadistinct,
820 stakind1, stakind2, stakind3, stakind4, stakind5,
821 staop1, staop2, staop3, staop4, staop5,
822 stacoll1, stacoll2, stacoll3, stacoll4, stacoll5,
823 stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
824 stavalues1, stavalues2, stavalues3, stavalues4, stavalues5
825 FROM dbms_stats.column_stats_backup
827 AND starelid = restore_relid
828 AND staattnum = restore_attnum;
834 LANGUAGE plpgsql STRICT;
837 -- LOCK_STATS: Statistics lock functions
840 CREATE FUNCTION dbms_stats.lock(
843 ) RETURNS regclass AS
851 RAISE EXCEPTION 'relation required';
854 RETURN dbms_stats.lock($1);
856 SELECT relkind INTO lock_relkind FROM pg_catalog.pg_class WHERE oid = $1;
858 RAISE EXCEPTION 'relation "%" not found', $1;
860 IF NOT dbms_stats.is_target_relkind(lock_relkind) THEN
861 RAISE EXCEPTION '"%" must be a table or an index', $1;
863 IF EXISTS(SELECT * FROM pg_catalog.pg_index WHERE lock_relkind = 'i' AND indexrelid = $1 AND indexprs IS NULL) THEN
864 RAISE EXCEPTION '"%" must be an expression index', $1;
866 IF dbms_stats.is_system_catalog($1) THEN
867 RAISE EXCEPTION 'locking statistics is inhibited for system catalogs: "%"', $1;
869 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
870 WHERE a.attrelid = $1 AND a.attname = $2;
871 IF set_attnum IS NULL THEN
872 RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
876 * If we don't have per-table statistics, create new one which has NULL for
877 * every statistic value for column_stats_effective.
879 IF NOT EXISTS(SELECT * FROM dbms_stats.relation_stats_locked ru
880 WHERE ru.relid = $1 FOR SHARE) THEN
881 INSERT INTO dbms_stats.relation_stats_locked
882 SELECT $1, dbms_stats.relname(nspname, relname),
883 NULL, NULL, NULL, NULL, NULL
884 FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
885 WHERE c.relnamespace = n.oid
890 * Process for per-column statistics
893 SELECT stainherit, stanullfrac, stawidth, stadistinct,
894 stakind1, stakind2, stakind3, stakind4, stakind5,
895 staop1, staop2, staop3, staop4, staop5,
896 stacoll1, stacoll2, stacoll3, stacoll4, stacoll5,
897 stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
898 stavalues1, stavalues2, stavalues3, stavalues4, stavalues5
899 FROM dbms_stats.column_stats_effective
901 AND staattnum = set_attnum
903 UPDATE dbms_stats.column_stats_locked c
904 SET stanullfrac = r.stanullfrac,
905 stawidth = r.stawidth,
906 stadistinct = r.stadistinct,
907 stakind1 = r.stakind1,
908 stakind2 = r.stakind2,
909 stakind3 = r.stakind3,
910 stakind4 = r.stakind4,
911 stakind5 = r.stakind5,
917 stacoll1 = r.stacoll1,
918 stacoll2 = r.stacoll2,
919 stacoll3 = r.stacoll3,
920 stacoll4 = r.stacoll4,
921 stacoll5 = r.stacoll5,
922 stanumbers1 = r.stanumbers1,
923 stanumbers2 = r.stanumbers2,
924 stanumbers3 = r.stanumbers3,
925 stanumbers4 = r.stanumbers4,
926 stanumbers5 = r.stanumbers5,
927 stavalues1 = r.stavalues1,
928 stavalues2 = r.stavalues2,
929 stavalues3 = r.stavalues3,
930 stavalues4 = r.stavalues4,
931 stavalues5 = r.stavalues5
932 WHERE c.starelid = $1
933 AND c.staattnum = set_attnum
934 AND c.stainherit = r.stainherit;
937 INSERT INTO dbms_stats.column_stats_locked
972 /* If we don't have statistics at all, raise error. */
974 RAISE EXCEPTION 'no statistics available for column "%" of relation "%"', $2, $1::regclass;
979 WHEN unique_violation THEN
980 RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
985 CREATE FUNCTION dbms_stats.lock(relid regclass)
993 RAISE EXCEPTION 'relation required';
995 SELECT relkind INTO lock_relkind FROM pg_catalog.pg_class WHERE oid = $1;
997 RAISE EXCEPTION 'relation "%" not found', $1;
999 IF NOT dbms_stats.is_target_relkind(lock_relkind) THEN
1000 RAISE EXCEPTION 'locking statistics is not allowed for relations with relkind "%": "%"', lock_relkind, $1
1001 USING HINT = 'Only tables(r, m, f) and indexes(i) are lockable.';
1003 IF dbms_stats.is_system_catalog($1) THEN
1004 RAISE EXCEPTION 'locking statistics is not allowed for system catalogs: "%"', $1;
1007 UPDATE dbms_stats.relation_stats_locked r
1008 SET relname = dbms_stats.relname(nspname, c.relname),
1009 relpages = v.relpages,
1010 reltuples = v.reltuples,
1011 relallvisible = v.relallvisible,
1012 curpages = v.curpages,
1013 last_analyze = v.last_analyze,
1014 last_autoanalyze = v.last_autoanalyze
1015 FROM pg_catalog.pg_class c,
1016 pg_catalog.pg_namespace n,
1017 dbms_stats.relation_stats_effective v
1020 AND c.relnamespace = n.oid
1023 INSERT INTO dbms_stats.relation_stats_locked
1024 SELECT $1, dbms_stats.relname(nspname, c.relname),
1025 v.relpages, v.reltuples, v.relallvisible, v.curpages,
1026 v.last_analyze, v.last_autoanalyze
1027 FROM pg_catalog.pg_class c,
1028 pg_catalog.pg_namespace n,
1029 dbms_stats.relation_stats_effective v
1031 AND c.relnamespace = n.oid
1036 FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_index ind
1037 ON c.oid = ind.indexrelid
1040 AND ind.indexprs IS NULL) THEN
1045 SELECT staattnum, stainherit, stanullfrac,
1046 stawidth, stadistinct,
1047 stakind1, stakind2, stakind3, stakind4, stakind5,
1048 staop1, staop2, staop3, staop4, staop5,
1049 stacoll1, stacoll2, stacoll3, stacoll4, stacoll5,
1050 stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
1051 stavalues1, stavalues2, stavalues3, stavalues4, stavalues5
1052 FROM dbms_stats.column_stats_effective
1055 UPDATE dbms_stats.column_stats_locked c
1056 SET stanullfrac = i.stanullfrac,
1057 stawidth = i.stawidth,
1058 stadistinct = i.stadistinct,
1059 stakind1 = i.stakind1,
1060 stakind2 = i.stakind2,
1061 stakind3 = i.stakind3,
1062 stakind4 = i.stakind4,
1063 stakind5 = i.stakind5,
1069 stacoll1 = i.stacoll1,
1070 stacoll2 = i.stacoll2,
1071 stacoll3 = i.stacoll3,
1072 stacoll4 = i.stacoll4,
1073 stacoll5 = i.stacoll5,
1074 stanumbers1 = i.stanumbers1,
1075 stanumbers2 = i.stanumbers2,
1076 stanumbers3 = i.stanumbers3,
1077 stanumbers4 = i.stanumbers4,
1078 stanumbers5 = i.stanumbers5,
1079 stavalues1 = i.stavalues1,
1080 stavalues2 = i.stavalues2,
1081 stavalues3 = i.stavalues3,
1082 stavalues4 = i.stavalues4,
1083 stavalues5 = i.stavalues5
1084 WHERE c.starelid = $1
1085 AND c.staattnum = i.staattnum
1086 AND c.stainherit = i.stainherit;
1089 INSERT INTO dbms_stats.column_stats_locked
1126 WHEN unique_violation THEN
1127 RAISE EXCEPTION 'This operation is canceled by simultaneous lock operation on the same relation.';
1132 CREATE FUNCTION dbms_stats.lock_database_stats()
1133 RETURNS SETOF regclass AS
1135 SELECT dbms_stats.lock(c.oid)
1137 FROM pg_catalog.pg_class
1138 WHERE NOT dbms_stats.is_system_catalog(oid)
1139 AND dbms_stats.is_target_relkind(relkind)
1140 ORDER BY pg_class.oid
1145 CREATE FUNCTION dbms_stats.lock_schema_stats(
1147 ) RETURNS SETOF regclass AS
1150 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
1151 RAISE EXCEPTION 'schema "%" not found', $1;
1153 IF dbms_stats.is_system_schema($1) THEN
1154 RAISE EXCEPTION 'locking statistics is not allowed for system schemas: "%"', $1;
1158 SELECT dbms_stats.lock(cn.oid)
1160 FROM pg_class c, pg_namespace n
1161 WHERE c.relnamespace = n.oid
1162 AND dbms_stats.is_target_relkind(c.relkind)
1168 LANGUAGE plpgsql STRICT;
1170 CREATE FUNCTION dbms_stats.lock_table_stats(relid regclass)
1173 SELECT dbms_stats.lock($1)
1175 LANGUAGE sql STRICT;
1177 CREATE FUNCTION dbms_stats.lock_table_stats(
1180 ) RETURNS regclass AS
1182 SELECT dbms_stats.lock(dbms_stats.relname($1, $2)::regclass)
1184 LANGUAGE sql STRICT;
1186 CREATE FUNCTION dbms_stats.lock_column_stats(
1189 ) RETURNS regclass AS
1191 SELECT dbms_stats.lock($1, $2)
1193 LANGUAGE sql STRICT;
1195 CREATE FUNCTION dbms_stats.lock_column_stats(
1199 ) RETURNS regclass AS
1201 SELECT dbms_stats.lock(dbms_stats.relname($1, $2)::regclass, $3)
1203 LANGUAGE sql STRICT;
1206 -- UNLOCK_STATS: Statistics unlock functions
1209 CREATE FUNCTION dbms_stats.unlock(
1210 relid regclass DEFAULT NULL,
1211 attname text DEFAULT NULL
1212 ) RETURNS SETOF regclass AS
1218 IF $1 IS NULL AND $2 IS NOT NULL THEN
1219 RAISE EXCEPTION 'relation required';
1223 * Lock the target relation to prevent conflicting with stats lock/restore
1225 PERFORM * FROM dbms_stats.relation_stats_locked ru
1226 WHERE (ru.relid = $1 OR $1 IS NULL) FOR UPDATE;
1228 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
1229 WHERE a.attrelid = $1 AND a.attname = $2;
1230 IF $2 IS NOT NULL AND set_attnum IS NULL THEN
1231 RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
1234 DELETE FROM dbms_stats.column_stats_locked
1235 WHERE (starelid = $1 OR $1 IS NULL)
1236 AND (staattnum = set_attnum OR $2 IS NULL);
1238 IF $1 IS NOT NULL AND $2 IS NOT NULL THEN
1244 FROM dbms_stats.relation_stats_locked ru
1245 WHERE (ru.relid = $1 OR $1 IS NULL) AND ($2 IS NULL)
1248 DELETE FROM dbms_stats.relation_stats_locked ru
1249 WHERE ru.relid = unlock_id;
1250 RETURN NEXT unlock_id;
1256 CREATE FUNCTION dbms_stats.unlock_database_stats()
1257 RETURNS SETOF regclass AS
1262 LOCK dbms_stats.relation_stats_locked IN EXCLUSIVE MODE;
1266 FROM dbms_stats.relation_stats_locked
1269 DELETE FROM dbms_stats.relation_stats_locked
1270 WHERE relid = unlock_id;
1271 RETURN NEXT unlock_id;
1275 LANGUAGE plpgsql STRICT;
1277 CREATE FUNCTION dbms_stats.unlock_schema_stats(
1279 ) RETURNS SETOF regclass AS
1284 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
1285 RAISE EXCEPTION 'schema "%" not found', $1;
1287 IF dbms_stats.is_system_schema($1) THEN
1288 RAISE EXCEPTION 'unlocking statistics is not allowed for system schemas: "%"', $1;
1293 FROM dbms_stats.relation_stats_locked r, pg_class c, pg_namespace n
1295 AND c.relnamespace = n.oid
1300 DELETE FROM dbms_stats.relation_stats_locked
1301 WHERE relid = unlock_id;
1302 RETURN NEXT unlock_id;
1306 LANGUAGE plpgsql STRICT;
1308 CREATE FUNCTION dbms_stats.unlock_table_stats(relid regclass)
1309 RETURNS SETOF regclass AS
1311 DELETE FROM dbms_stats.relation_stats_locked
1313 RETURNING relid::regclass
1315 LANGUAGE sql STRICT;
1317 CREATE FUNCTION dbms_stats.unlock_table_stats(
1320 ) RETURNS SETOF regclass AS
1322 DELETE FROM dbms_stats.relation_stats_locked
1323 WHERE relid = dbms_stats.relname($1, $2)::regclass
1324 RETURNING relid::regclass
1326 LANGUAGE sql STRICT;
1328 CREATE FUNCTION dbms_stats.unlock_column_stats(
1331 ) RETURNS SETOF regclass AS
1336 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
1337 WHERE a.attrelid = $1 AND a.attname = $2;
1338 IF $2 IS NOT NULL AND set_attnum IS NULL THEN
1339 RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
1342 /* Lock the locked table stats */
1343 PERFORM * from dbms_stats.relation_stats_locked r
1344 WHERE r.relid = $1 FOR SHARE;
1346 DELETE FROM dbms_stats.column_stats_locked
1348 AND staattnum = set_attnum;
1354 LANGUAGE plpgsql STRICT;
1356 CREATE FUNCTION dbms_stats.unlock_column_stats(
1360 ) RETURNS SETOF regclass AS
1365 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
1366 WHERE a.attrelid = dbms_stats.relname($1, $2)::regclass
1368 IF $3 IS NOT NULL AND set_attnum IS NULL THEN
1369 RAISE EXCEPTION 'column "%" not found in relation "%.%"', $3, $1, $2;
1372 /* Lock the locked table stats */
1373 PERFORM * from dbms_stats.relation_stats_locked r
1374 WHERE relid = dbms_stats.relname($1, $2)::regclass FOR SHARE;
1376 DELETE FROM dbms_stats.column_stats_locked
1377 WHERE starelid = dbms_stats.relname($1, $2)::regclass
1378 AND staattnum = set_attnum;
1381 SELECT dbms_stats.relname($1, $2)::regclass;
1384 LANGUAGE plpgsql STRICT;
1387 -- IMPORT_STATS: Statistics import functions
1390 CREATE FUNCTION dbms_stats.import(
1391 nspname text DEFAULT NULL,
1392 relid regclass DEFAULT NULL,
1393 attname text DEFAULT NULL,
1394 src text DEFAULT NULL
1396 'MODULE_PATHNAME', 'dbms_stats_import'
1399 CREATE FUNCTION dbms_stats.import_database_stats(src text)
1402 SELECT dbms_stats.import(NULL, NULL, NULL, $1)
1406 CREATE FUNCTION dbms_stats.import_schema_stats(
1411 SELECT dbms_stats.import($1, NULL, NULL, $2)
1415 CREATE FUNCTION dbms_stats.import_table_stats(
1420 SELECT dbms_stats.import(NULL, $1, NULL, $2)
1424 CREATE FUNCTION dbms_stats.import_table_stats(
1430 SELECT dbms_stats.import(NULL, dbms_stats.relname($1, $2)::regclass, NULL, $3)
1434 CREATE FUNCTION dbms_stats.import_column_stats(
1440 SELECT dbms_stats.import(NULL, $1, $2, $3)
1444 CREATE FUNCTION dbms_stats.import_column_stats(
1451 SELECT dbms_stats.import(NULL, dbms_stats.relname($1, $2)::regclass, $3, $4)
1456 -- PURGE_STATS: Statistics purge function
1458 CREATE FUNCTION dbms_stats.purge_stats(
1460 force bool DEFAULT false
1461 ) RETURNS SETOF dbms_stats.backup_history AS
1465 todelete dbms_stats.backup_history;
1468 RAISE EXCEPTION 'backup id required';
1471 RAISE EXCEPTION 'NULL is not allowed as the second parameter';
1474 IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
1475 WHERE id = $1 FOR UPDATE) THEN
1476 RAISE EXCEPTION 'backup id % not found', $1;
1478 IF NOT $2 AND NOT EXISTS(SELECT *
1479 FROM dbms_stats.backup_history
1482 RAISE WARNING 'no database-wide backup will remain after purge'
1483 USING HINT = 'Give true for second parameter to purge forcibly.';
1488 SELECT * FROM dbms_stats.backup_history
1490 ORDER BY id FOR UPDATE
1492 DELETE FROM dbms_stats.backup_history
1493 WHERE id = todelete.id;
1494 RETURN NEXT todelete;
1501 -- CLEAN_STATS: Clean orphan dummy statistics
1503 CREATE FUNCTION dbms_stats.clean_up_stats() RETURNS SETOF text AS
1511 -- We don't have to check that table-level dummy statistics of the table
1512 -- exists here, because the foreign key constraints defined on column-level
1513 -- dummy static table ensures that.
1514 FOR clean_rel_col, clean_relid, clean_attnum, clean_inherit IN
1515 SELECT r.relname || ', ' || v.staattnum::text,
1516 v.starelid, v.staattnum, v.stainherit
1517 FROM dbms_stats.column_stats_locked v
1518 JOIN dbms_stats.relation_stats_locked r ON (v.starelid = r.relid)
1522 WHERE a.attrelid = v.starelid
1523 AND a.attnum = v.staattnum
1524 AND a.attisdropped = false
1528 DELETE FROM dbms_stats.column_stats_locked
1529 WHERE starelid = clean_relid
1530 AND staattnum = clean_attnum
1531 AND stainherit = clean_inherit;
1532 RETURN NEXT clean_rel_col;
1536 DELETE FROM dbms_stats.relation_stats_locked r
1540 WHERE c.oid = r.relid)
1541 RETURNING relname || ',';
1548 * Stuff for manipulating statistics
1551 /* Primitive functions for tweaking statistics */
1552 CREATE FUNCTION dbms_stats.anyarray_basetype(dbms_stats.anyarray)
1554 AS 'MODULE_PATHNAME', 'dbms_stats_anyarray_basetype'
1557 CREATE FUNCTION dbms_stats.type_is_analyzable(oid) returns bool
1558 AS 'MODULE_PATHNAME', 'dbms_stats_type_is_analyzable'
1559 LANGUAGE C STRICT STABLE;
1562 * Create and drop a cast necessary to set column values of dbms_stats.anyarray
1565 CREATE FUNCTION dbms_stats.prepare_statstweak(regtype)
1573 srctypname := $1 || '[]';
1574 funcname := 'dbms_stats._' || pg_catalog.replace($1::text, ' ', '_') ||
1576 funcdef := funcname || '(' || srctypname || ')';
1577 castdef := '(' || srctypname || ' AS dbms_stats.anyarray)';
1579 IF (NOT dbms_stats.type_is_analyzable($1::regtype)) THEN
1580 RAISE 'the type can not have statistics';
1583 EXECUTE 'CREATE FUNCTION ' || funcdef ||
1584 ' RETURNS dbms_stats.anyarray ' ||
1585 ' AS ''pg_dbms_stats'', ''dbms_stats_anyary_anyary'''||
1586 ' LANGUAGE C STRICT IMMUTABLE';
1587 EXECUTE 'CREATE CAST '|| castdef ||
1588 ' WITH FUNCTION ' || funcdef ||
1590 RETURN '(func ' || funcdef || ', cast ' || castdef || ')';
1592 WHEN duplicate_function THEN
1593 RAISE 'run dbms_stats.drop_statstweak() for the type before this';
1595 $$ LANGUAGE plpgsql;
1597 CREATE FUNCTION dbms_stats.drop_statstweak(regtype)
1605 srctypname := $1 || '[]';
1606 funcname := 'dbms_stats._' || pg_catalog.replace($1::text, ' ', '_') ||
1608 funcdef := funcname || '(' || srctypname || ')';
1609 castdef := '(' || srctypname || ' AS dbms_stats.anyarray)';
1611 EXECUTE 'DROP CAST ' || castdef;
1612 EXECUTE 'DROP FUNCTION ' || funcdef;
1613 RETURN '(func ' || funcdef || ', cast ' || castdef || ')';
1615 WHEN undefined_function OR undefined_object THEN
1616 RAISE 'function % or cast % does not exist', funcdef, castdef;
1618 $$ LANGUAGE plpgsql;