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 quote_ident($1) || '.' || 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_relation_size(c.oid) / current_setting('block_size')::int4)::int4)
185 COALESCE(v.last_analyze,
186 pg_catalog.pg_stat_get_last_analyze_time(c.oid))
188 COALESCE(v.last_autoanalyze,
189 pg_catalog.pg_stat_get_last_autoanalyze_time(c.oid))
191 FROM pg_catalog.pg_class c
192 JOIN pg_catalog.pg_namespace n
193 ON c.relnamespace = n.oid
194 LEFT JOIN dbms_stats.relation_stats_locked v
196 WHERE dbms_stats.is_target_relkind(c.relkind)
197 AND NOT dbms_stats.is_system_schema(nspname);
199 CREATE VIEW dbms_stats.column_stats_effective AS
201 SELECT (dbms_stats.merge(v, s)).*
202 FROM pg_catalog.pg_statistic s
203 FULL JOIN dbms_stats.column_stats_locked v
204 USING (starelid, staattnum, stainherit)
205 WHERE NOT dbms_stats.is_system_catalog(starelid)
209 WHERE a.attrelid = starelid
210 AND a.attnum = staattnum
211 AND a.attisdropped = false
214 WHERE starelid IS NOT NULL;
217 -- Note: This view is copied from pg_stats in
218 -- src/backend/catalog/system_views.sql in core source tree of version
219 -- 9.5, and customized for pg_dbms_stats. Changes from orignal one are:
220 -- - rename from pg_stats to dbms_stats.stats by a view name.
221 -- - changed the table name from pg_statistic to dbms_stats.column_stats_effective.
223 CREATE VIEW dbms_stats.stats with (security_barrier) AS
225 nspname AS schemaname,
226 relname AS tablename,
228 stainherit AS inherited,
229 stanullfrac AS null_frac,
230 stawidth AS avg_width,
231 stadistinct AS n_distinct,
233 WHEN stakind1 = 1 THEN stavalues1
234 WHEN stakind2 = 1 THEN stavalues2
235 WHEN stakind3 = 1 THEN stavalues3
236 WHEN stakind4 = 1 THEN stavalues4
237 WHEN stakind5 = 1 THEN stavalues5
238 END AS most_common_vals,
240 WHEN stakind1 = 1 THEN stanumbers1
241 WHEN stakind2 = 1 THEN stanumbers2
242 WHEN stakind3 = 1 THEN stanumbers3
243 WHEN stakind4 = 1 THEN stanumbers4
244 WHEN stakind5 = 1 THEN stanumbers5
245 END AS most_common_freqs,
247 WHEN stakind1 = 2 THEN stavalues1
248 WHEN stakind2 = 2 THEN stavalues2
249 WHEN stakind3 = 2 THEN stavalues3
250 WHEN stakind4 = 2 THEN stavalues4
251 WHEN stakind5 = 2 THEN stavalues5
252 END AS histogram_bounds,
254 WHEN stakind1 = 3 THEN stanumbers1[1]
255 WHEN stakind2 = 3 THEN stanumbers2[1]
256 WHEN stakind3 = 3 THEN stanumbers3[1]
257 WHEN stakind4 = 3 THEN stanumbers4[1]
258 WHEN stakind5 = 3 THEN stanumbers5[1]
261 WHEN stakind1 = 4 THEN stavalues1
262 WHEN stakind2 = 4 THEN stavalues2
263 WHEN stakind3 = 4 THEN stavalues3
264 WHEN stakind4 = 4 THEN stavalues4
265 WHEN stakind5 = 4 THEN stavalues5
266 END AS most_common_elems,
268 WHEN stakind1 = 4 THEN stanumbers1
269 WHEN stakind2 = 4 THEN stanumbers2
270 WHEN stakind3 = 4 THEN stanumbers3
271 WHEN stakind4 = 4 THEN stanumbers4
272 WHEN stakind5 = 4 THEN stanumbers5
273 END AS most_common_elem_freqs,
275 WHEN stakind1 = 5 THEN stanumbers1
276 WHEN stakind2 = 5 THEN stanumbers2
277 WHEN stakind3 = 5 THEN stanumbers3
278 WHEN stakind4 = 5 THEN stanumbers4
279 WHEN stakind5 = 5 THEN stanumbers5
280 END AS elem_count_histogram
281 FROM dbms_stats.column_stats_effective s JOIN pg_class c ON (c.oid = s.starelid)
282 JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
283 LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
284 WHERE NOT attisdropped
285 AND has_column_privilege(c.oid, a.attnum, 'select')
286 AND (c.relrowsecurity = false OR NOT row_security_active(c.oid));
292 CREATE FUNCTION dbms_stats.invalidate_relation_cache()
294 'MODULE_PATHNAME', 'dbms_stats_invalidate_relation_cache'
297 -- Invalidate cached plans when dbms_stats.relation_stats_locked is modified.
298 CREATE TRIGGER invalidate_relation_cache
299 BEFORE INSERT OR DELETE OR UPDATE
300 ON dbms_stats.relation_stats_locked
301 FOR EACH ROW EXECUTE PROCEDURE dbms_stats.invalidate_relation_cache();
303 CREATE FUNCTION dbms_stats.invalidate_column_cache()
305 'MODULE_PATHNAME', 'dbms_stats_invalidate_column_cache'
308 -- Invalidate cached plans when dbms_stats.column_stats_locked is modified.
309 CREATE TRIGGER invalidate_column_cache
310 BEFORE INSERT OR DELETE OR UPDATE
311 ON dbms_stats.column_stats_locked
312 FOR EACH ROW EXECUTE PROCEDURE dbms_stats.invalidate_column_cache();
315 -- BACKUP_STATS: Statistics backup functions
318 CREATE FUNCTION dbms_stats.backup(
324 /* Lock the backup id */
325 SELECT * from dbms_stats.backup_history
326 WHERE id = $1 FOR UPDATE;
328 INSERT INTO dbms_stats.relation_stats_backup
329 SELECT $1, v.relid, v.relname, v.relpages, v.reltuples, v.relallvisible,
330 v.curpages, v.last_analyze, v.last_autoanalyze
331 FROM pg_catalog.pg_class c,
332 dbms_stats.relation_stats_effective v
333 WHERE c.oid = v.relid
334 AND dbms_stats.is_target_relkind(relkind)
335 AND NOT dbms_stats.is_system_catalog(v.relid)
336 AND (v.relid = $2 OR $2 IS NULL);
338 INSERT INTO dbms_stats.column_stats_backup
339 SELECT $1, atttypid, s.*
340 FROM pg_catalog.pg_class c,
341 dbms_stats.column_stats_effective s,
342 pg_catalog.pg_attribute a
343 WHERE c.oid = starelid
344 AND starelid = attrelid
345 AND staattnum = attnum
346 AND dbms_stats.is_target_relkind(relkind)
347 AND NOT dbms_stats.is_system_catalog(c.oid)
348 AND ($2 IS NULL OR starelid = $2)
349 AND ($3 IS NULL OR staattnum = $3);
355 CREATE FUNCTION dbms_stats.backup(
356 relid regclass DEFAULT NULL,
357 attname text DEFAULT NULL,
358 comment text DEFAULT NULL
363 backup_relkind "char";
367 IF $1 IS NULL AND $2 IS NOT NULL THEN
368 RAISE EXCEPTION 'relation required';
370 IF $1 IS NOT NULL THEN
371 SELECT relkind INTO backup_relkind
372 FROM pg_catalog.pg_class WHERE oid = $1 FOR SHARE;
374 RAISE EXCEPTION 'relation "%" not found', $1;
376 IF NOT dbms_stats.is_target_relkind(backup_relkind) THEN
377 RAISE EXCEPTION 'relation of relkind "%" cannot have statistics to backup: "%"',
379 USING HINT = 'Only tables(r), materialized views(m), foreign tables(f) and indexes(i) are allowed.';
381 IF dbms_stats.is_system_catalog($1) THEN
382 RAISE EXCEPTION 'backing up statistics is inhibited for system catalogs: "%"', $1;
384 IF $2 IS NOT NULL THEN
385 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
386 WHERE a.attrelid = $1 AND a.attname = $2 FOR SHARE;
387 IF set_attnum IS NULL THEN
388 RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
390 IF NOT EXISTS(SELECT * FROM dbms_stats.column_stats_effective WHERE starelid = $1 AND staattnum = set_attnum) THEN
391 RAISE EXCEPTION 'no statistics available for column "%" of relation "%"', $2, $1;
401 INSERT INTO dbms_stats.backup_history(time, unit, comment)
402 VALUES (current_timestamp, unit_type, $3)
403 RETURNING dbms_stats.backup(id, $1, set_attnum) INTO backup_id;
409 CREATE FUNCTION dbms_stats.backup_database_stats(
413 SELECT dbms_stats.backup(NULL, NULL, $1)
417 CREATE FUNCTION dbms_stats.backup_schema_stats(
425 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1 FOR SHARE)
427 RAISE EXCEPTION 'schema "%" not found', $1;
429 IF dbms_stats.is_system_schema($1) THEN
430 RAISE EXCEPTION 'backing up statistics is inhibited for system schemas: "%"', $1;
433 INSERT INTO dbms_stats.backup_history(time, unit, comment)
434 VALUES (current_timestamp, 's', comment)
435 RETURNING id INTO backup_id;
437 PERFORM dbms_stats.backup(backup_id, cn.oid, NULL)
439 FROM pg_catalog.pg_class c,
440 pg_catalog.pg_namespace n
441 WHERE n.nspname = schemaname
442 AND c.relnamespace = n.oid
443 AND dbms_stats.is_target_relkind(c.relkind)
452 CREATE FUNCTION dbms_stats.backup_table_stats(
457 SELECT dbms_stats.backup($1, NULL, $2)
461 CREATE FUNCTION dbms_stats.backup_table_stats(
467 SELECT dbms_stats.backup(dbms_stats.relname($1, $2)::regclass, NULL, $3)
471 CREATE FUNCTION dbms_stats.backup_column_stats(
477 SELECT dbms_stats.backup($1, $2, $3)
481 CREATE FUNCTION dbms_stats.backup_column_stats(
488 SELECT dbms_stats.backup(dbms_stats.relname($1, $2)::regclass, $3, $4)
493 -- RESTORE_STATS: Statistics restore functions
495 CREATE FUNCTION dbms_stats.restore(
497 relid regclass DEFAULT NULL,
498 attname text DEFAULT NULL
499 ) RETURNS SETOF regclass AS
503 restore_relid regclass;
506 restore_attname text;
507 restore_type regtype;
511 RAISE EXCEPTION 'backup id required';
513 IF $2 IS NULL AND $3 IS NOT NULL THEN
514 RAISE EXCEPTION 'relation required';
516 IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
517 WHERE id <= $1 FOR SHARE) THEN
518 RAISE EXCEPTION 'backup id % not found', $1;
520 IF $2 IS NOT NULL THEN
521 IF NOT EXISTS(SELECT * FROM pg_catalog.pg_class
522 WHERE oid = $2 FOR SHARE) THEN
523 RAISE EXCEPTION 'relation "%" not found', $2;
525 -- Grabbing all backups for the relation which is not used in restore.
526 IF NOT EXISTS(SELECT * FROM dbms_stats.relation_stats_backup b
527 WHERE b.id <= $1 AND b.relid = $2 FOR SHARE) THEN
528 RAISE EXCEPTION 'statistics of relation "%" not found in any backups before backup id = %', $2, $1;
530 IF $3 IS NOT NULL THEN
531 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
532 WHERE a.attrelid = $2 AND a.attname = $3;
533 IF set_attnum IS NULL THEN
534 RAISE EXCEPTION 'column "%" not found in relation %', $3, $2;
536 IF NOT EXISTS(SELECT * FROM dbms_stats.column_stats_backup WHERE id <= $1 AND starelid = $2 AND staattnum = set_attnum) THEN
537 RAISE EXCEPTION 'statistics of column "%" of relation "%" are not found in any backups before backup id = %',$3, $2, $1;
540 PERFORM * FROM dbms_stats.relation_stats_locked r
541 WHERE r.relid = $2 FOR UPDATE;
543 /* Lock the whole relation stats if relation is not specified.*/
544 LOCK dbms_stats.relation_stats_locked IN EXCLUSIVE MODE;
547 FOR restore_id, restore_relid IN
548 SELECT max(id), coid FROM
549 (SELECT b.id as id, c.oid as coid
550 FROM pg_class c, dbms_stats.relation_stats_backup b
551 WHERE (c.oid = $2 OR $2 IS NULL)
553 AND dbms_stats.is_target_relkind(c.relkind)
554 AND NOT dbms_stats.is_system_catalog(c.oid)
558 ORDER BY coid::regclass::text
560 UPDATE dbms_stats.relation_stats_locked r
563 relpages = b.relpages,
564 reltuples = b.reltuples,
565 relallvisible = b.relallvisible,
566 curpages = b.curpages,
567 last_analyze = b.last_analyze,
568 last_autoanalyze = b.last_autoanalyze
569 FROM dbms_stats.relation_stats_backup b
570 WHERE r.relid = restore_relid
571 AND b.id = restore_id
572 AND b.relid = restore_relid;
574 INSERT INTO dbms_stats.relation_stats_locked
583 FROM dbms_stats.relation_stats_backup b
584 WHERE b.id = restore_id
585 AND b.relid = restore_relid;
587 RETURN NEXT restore_relid;
590 FOR restore_id, restore_relid, restore_attnum, restore_type, cur_type IN
591 SELECT t.id, t.oid, t.attnum, b.statypid, a.atttypid
593 dbms_stats.column_stats_backup b,
594 (SELECT max(b.id) AS id, c.oid, a.attnum
595 FROM pg_class c, pg_attribute a, dbms_stats.column_stats_backup b
596 WHERE (c.oid = $2 OR $2 IS NULL)
597 AND c.oid = a.attrelid
598 AND c.oid = b.starelid
599 AND (a.attnum = set_attnum OR set_attnum IS NULL)
600 AND a.attnum = b.staattnum
601 AND NOT a.attisdropped
602 AND dbms_stats.is_target_relkind(c.relkind)
604 GROUP BY c.oid, a.attnum) t
605 WHERE a.attrelid = t.oid
606 AND a.attnum = t.attnum
608 AND b.starelid = t.oid
609 AND b.staattnum = t.attnum
611 IF restore_type <> cur_type THEN
612 SELECT a.attname INTO restore_attname
613 FROM pg_catalog.pg_attribute a
614 WHERE a.attrelid = restore_relid
615 AND a.attnum = restore_attnum;
616 RAISE WARNING 'data type of column "%.%" is inconsistent between database(%) and backup (%). Skip.',
617 restore_relid, restore_attname, cur_type, restore_type;
619 DELETE FROM dbms_stats.column_stats_locked
620 WHERE starelid = restore_relid
621 AND staattnum = restore_attnum;
622 INSERT INTO dbms_stats.column_stats_locked
623 SELECT starelid, staattnum, stainherit,
624 stanullfrac, stawidth, stadistinct,
625 stakind1, stakind2, stakind3, stakind4, stakind5,
626 staop1, staop2, staop3, staop4, staop5,
627 stacoll1, stacoll2, stacoll3, stacoll4, stacoll5,
628 stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
629 stavalues1, stavalues2, stavalues3, stavalues4, stavalues5
630 FROM dbms_stats.column_stats_backup
631 WHERE id = restore_id
632 AND starelid = restore_relid
633 AND staattnum = restore_attnum;
637 WHEN unique_violation THEN
638 RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
643 CREATE FUNCTION dbms_stats.restore_database_stats(
644 as_of_timestamp timestamp with time zone
645 ) RETURNS SETOF regclass AS
647 SELECT dbms_stats.restore(m.id, m.relid)
648 FROM (SELECT max(id) AS id, relid
649 FROM (SELECT r.id, r.relid
650 FROM pg_class c, dbms_stats.relation_stats_backup r,
651 dbms_stats.backup_history b
652 WHERE c.oid = r.relid
657 ORDER BY t1.relid) m;
661 CREATE FUNCTION dbms_stats.restore_schema_stats(
663 as_of_timestamp timestamp with time zone
664 ) RETURNS SETOF regclass AS
667 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
668 RAISE EXCEPTION 'schema "%" not found', $1;
670 IF dbms_stats.is_system_schema($1) THEN
671 RAISE EXCEPTION 'restoring statistics is inhibited for system schemas: "%"', $1;
675 SELECT dbms_stats.restore(m.id, m.relid)
676 FROM (SELECT max(id) AS id, relid
677 FROM (SELECT r.id, r.relid
678 FROM pg_class c, pg_namespace n,
679 dbms_stats.relation_stats_backup r,
680 dbms_stats.backup_history b
681 WHERE c.oid = r.relid
682 AND c.relnamespace = n.oid
688 ORDER BY t1.relid) m;
691 LANGUAGE plpgsql STRICT;
693 CREATE FUNCTION dbms_stats.restore_table_stats(
695 as_of_timestamp timestamp with time zone
696 ) RETURNS SETOF regclass AS
698 SELECT dbms_stats.restore(max(id), $1, NULL)
699 FROM dbms_stats.backup_history WHERE time <= $2
703 CREATE FUNCTION dbms_stats.restore_table_stats(
706 as_of_timestamp timestamp with time zone
707 ) RETURNS SETOF regclass AS
709 SELECT dbms_stats.restore_table_stats(dbms_stats.relname($1, $2)::regclass, $3)
713 CREATE FUNCTION dbms_stats.restore_column_stats(
716 as_of_timestamp timestamp with time zone
717 ) RETURNS SETOF regclass AS
719 SELECT dbms_stats.restore(max(id), $1, $2)
720 FROM dbms_stats.backup_history WHERE time <= $3
724 CREATE FUNCTION dbms_stats.restore_column_stats(
728 as_of_timestamp timestamp with time zone
729 ) RETURNS SETOF regclass AS
731 SELECT dbms_stats.restore(max(id), dbms_stats.relname($1, $2)::regclass, $3)
732 FROM dbms_stats.backup_history WHERE time <= $4
736 CREATE FUNCTION dbms_stats.restore_stats(
738 ) RETURNS SETOF regclass AS
741 restore_relid regclass;
743 restore_attname text;
744 restore_type regtype;
747 IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id = $1) THEN
748 RAISE EXCEPTION 'backup id % not found', $1;
751 /* Lock the backup */
752 PERFORM * from dbms_stats.relation_stats_backup b
753 WHERE id = $1 FOR SHARE;
755 /* Locking only relation_stats_locked is sufficient */
756 LOCK dbms_stats.relation_stats_locked IN EXCLUSIVE MODE;
761 JOIN dbms_stats.relation_stats_backup b ON (c.oid = b.relid)
763 ORDER BY c.oid::regclass::text
765 UPDATE dbms_stats.relation_stats_locked r
768 relpages = b.relpages,
769 reltuples = b.reltuples,
770 relallvisible = b.relallvisible,
771 curpages = b.curpages,
772 last_analyze = b.last_analyze,
773 last_autoanalyze = b.last_autoanalyze
774 FROM dbms_stats.relation_stats_backup b
775 WHERE r.relid = restore_relid
777 AND b.relid = restore_relid;
779 INSERT INTO dbms_stats.relation_stats_locked
788 FROM dbms_stats.relation_stats_backup b
790 AND b.relid = restore_relid;
792 RETURN NEXT restore_relid;
795 FOR restore_relid, restore_attnum, restore_type, cur_type IN
796 SELECT c.oid, a.attnum, b.statypid, a.atttypid
798 JOIN dbms_stats.column_stats_backup b ON (c.oid = b.starelid)
799 JOIN pg_attribute a ON (b.starelid = attrelid
800 AND b.staattnum = a.attnum)
803 IF restore_type <> cur_type THEN
804 SELECT attname INTO restore_attname
805 FROM pg_catalog.pg_attribute
806 WHERE attrelid = restore_relid
807 AND attnum = restore_attnum;
808 RAISE WARNING 'data type of column "%.%" is inconsistent between database(%) and backup (%). Skip.',
809 restore_relid, restore_attname, cur_type, restore_type;
811 DELETE FROM dbms_stats.column_stats_locked
812 WHERE starelid = restore_relid
813 AND staattnum = restore_attnum;
814 INSERT INTO dbms_stats.column_stats_locked
815 SELECT starelid, staattnum, stainherit,
816 stanullfrac, stawidth, stadistinct,
817 stakind1, stakind2, stakind3, stakind4, stakind5,
818 staop1, staop2, staop3, staop4, staop5,
819 stacoll1, stacoll2, stacoll3, stacoll4, stacoll5,
820 stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
821 stavalues1, stavalues2, stavalues3, stavalues4, stavalues5
822 FROM dbms_stats.column_stats_backup
824 AND starelid = restore_relid
825 AND staattnum = restore_attnum;
831 LANGUAGE plpgsql STRICT;
834 -- LOCK_STATS: Statistics lock functions
837 CREATE FUNCTION dbms_stats.lock(
840 ) RETURNS regclass AS
848 RAISE EXCEPTION 'relation required';
851 RETURN dbms_stats.lock($1);
853 SELECT relkind INTO lock_relkind FROM pg_catalog.pg_class WHERE oid = $1;
855 RAISE EXCEPTION 'relation "%" not found', $1;
857 IF NOT dbms_stats.is_target_relkind(lock_relkind) THEN
858 RAISE EXCEPTION '"%" must be a table or an index', $1;
860 IF EXISTS(SELECT * FROM pg_catalog.pg_index WHERE lock_relkind = 'i' AND indexrelid = $1 AND indexprs IS NULL) THEN
861 RAISE EXCEPTION '"%" must be an expression index', $1;
863 IF dbms_stats.is_system_catalog($1) THEN
864 RAISE EXCEPTION 'locking statistics is inhibited for system catalogs: "%"', $1;
866 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
867 WHERE a.attrelid = $1 AND a.attname = $2;
868 IF set_attnum IS NULL THEN
869 RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
873 * If we don't have per-table statistics, create new one which has NULL for
874 * every statistic value for column_stats_effective.
876 IF NOT EXISTS(SELECT * FROM dbms_stats.relation_stats_locked ru
877 WHERE ru.relid = $1 FOR SHARE) THEN
878 INSERT INTO dbms_stats.relation_stats_locked
879 SELECT $1, dbms_stats.relname(nspname, relname),
880 NULL, NULL, NULL, NULL, NULL
881 FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
882 WHERE c.relnamespace = n.oid
887 * Process for per-column statistics
890 SELECT stainherit, stanullfrac, stawidth, stadistinct,
891 stakind1, stakind2, stakind3, stakind4, stakind5,
892 staop1, staop2, staop3, staop4, staop5,
893 stacoll1, stacoll2, stacoll3, stacoll4, stacoll5,
894 stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
895 stavalues1, stavalues2, stavalues3, stavalues4, stavalues5
896 FROM dbms_stats.column_stats_effective
898 AND staattnum = set_attnum
900 UPDATE dbms_stats.column_stats_locked c
901 SET stanullfrac = r.stanullfrac,
902 stawidth = r.stawidth,
903 stadistinct = r.stadistinct,
904 stakind1 = r.stakind1,
905 stakind2 = r.stakind2,
906 stakind3 = r.stakind3,
907 stakind4 = r.stakind4,
908 stakind5 = r.stakind5,
914 stacoll1 = r.stacoll1,
915 stacoll2 = r.stacoll2,
916 stacoll3 = r.stacoll3,
917 stacoll4 = r.stacoll4,
918 stacoll5 = r.stacoll5,
919 stanumbers1 = r.stanumbers1,
920 stanumbers2 = r.stanumbers2,
921 stanumbers3 = r.stanumbers3,
922 stanumbers4 = r.stanumbers4,
923 stanumbers5 = r.stanumbers5,
924 stavalues1 = r.stavalues1,
925 stavalues2 = r.stavalues2,
926 stavalues3 = r.stavalues3,
927 stavalues4 = r.stavalues4,
928 stavalues5 = r.stavalues5
929 WHERE c.starelid = $1
930 AND c.staattnum = set_attnum
931 AND c.stainherit = r.stainherit;
934 INSERT INTO dbms_stats.column_stats_locked
969 /* If we don't have statistics at all, raise error. */
971 RAISE EXCEPTION 'no statistics available for column "%" of relation "%"', $2, $1::regclass;
976 WHEN unique_violation THEN
977 RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
982 CREATE FUNCTION dbms_stats.lock(relid regclass)
990 RAISE EXCEPTION 'relation required';
992 SELECT relkind INTO lock_relkind FROM pg_catalog.pg_class WHERE oid = $1;
994 RAISE EXCEPTION 'relation "%" not found', $1;
996 IF NOT dbms_stats.is_target_relkind(lock_relkind) THEN
997 RAISE EXCEPTION 'locking statistics is not allowed for relations with relkind "%": "%"', lock_relkind, $1
998 USING HINT = 'Only tables(r, m, f) and indexes(i) are lockable.';
1000 IF dbms_stats.is_system_catalog($1) THEN
1001 RAISE EXCEPTION 'locking statistics is not allowed for system catalogs: "%"', $1;
1004 UPDATE dbms_stats.relation_stats_locked r
1005 SET relname = dbms_stats.relname(nspname, c.relname),
1006 relpages = v.relpages,
1007 reltuples = v.reltuples,
1008 relallvisible = v.relallvisible,
1009 curpages = v.curpages,
1010 last_analyze = v.last_analyze,
1011 last_autoanalyze = v.last_autoanalyze
1012 FROM pg_catalog.pg_class c,
1013 pg_catalog.pg_namespace n,
1014 dbms_stats.relation_stats_effective v
1017 AND c.relnamespace = n.oid
1020 INSERT INTO dbms_stats.relation_stats_locked
1021 SELECT $1, dbms_stats.relname(nspname, c.relname),
1022 v.relpages, v.reltuples, v.relallvisible, v.curpages,
1023 v.last_analyze, v.last_autoanalyze
1024 FROM pg_catalog.pg_class c,
1025 pg_catalog.pg_namespace n,
1026 dbms_stats.relation_stats_effective v
1028 AND c.relnamespace = n.oid
1033 FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_index ind
1034 ON c.oid = ind.indexrelid
1037 AND ind.indexprs IS NULL) THEN
1042 SELECT staattnum, stainherit, stanullfrac,
1043 stawidth, stadistinct,
1044 stakind1, stakind2, stakind3, stakind4, stakind5,
1045 staop1, staop2, staop3, staop4, staop5,
1046 stacoll1, stacoll2, stacoll3, stacoll4, stacoll5,
1047 stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
1048 stavalues1, stavalues2, stavalues3, stavalues4, stavalues5
1049 FROM dbms_stats.column_stats_effective
1052 UPDATE dbms_stats.column_stats_locked c
1053 SET stanullfrac = i.stanullfrac,
1054 stawidth = i.stawidth,
1055 stadistinct = i.stadistinct,
1056 stakind1 = i.stakind1,
1057 stakind2 = i.stakind2,
1058 stakind3 = i.stakind3,
1059 stakind4 = i.stakind4,
1060 stakind5 = i.stakind5,
1066 stacoll1 = i.stacoll1,
1067 stacoll2 = i.stacoll2,
1068 stacoll3 = i.stacoll3,
1069 stacoll4 = i.stacoll4,
1070 stacoll5 = i.stacoll5,
1071 stanumbers1 = i.stanumbers1,
1072 stanumbers2 = i.stanumbers2,
1073 stanumbers3 = i.stanumbers3,
1074 stanumbers4 = i.stanumbers4,
1075 stanumbers5 = i.stanumbers5,
1076 stavalues1 = i.stavalues1,
1077 stavalues2 = i.stavalues2,
1078 stavalues3 = i.stavalues3,
1079 stavalues4 = i.stavalues4,
1080 stavalues5 = i.stavalues5
1081 WHERE c.starelid = $1
1082 AND c.staattnum = i.staattnum
1083 AND c.stainherit = i.stainherit;
1086 INSERT INTO dbms_stats.column_stats_locked
1123 WHEN unique_violation THEN
1124 RAISE EXCEPTION 'This operation is canceled by simultaneous lock operation on the same relation.';
1129 CREATE FUNCTION dbms_stats.lock_database_stats()
1130 RETURNS SETOF regclass AS
1132 SELECT dbms_stats.lock(c.oid)
1134 FROM pg_catalog.pg_class
1135 WHERE NOT dbms_stats.is_system_catalog(oid)
1136 AND dbms_stats.is_target_relkind(relkind)
1137 ORDER BY pg_class.oid
1142 CREATE FUNCTION dbms_stats.lock_schema_stats(
1144 ) RETURNS SETOF regclass AS
1147 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
1148 RAISE EXCEPTION 'schema "%" not found', $1;
1150 IF dbms_stats.is_system_schema($1) THEN
1151 RAISE EXCEPTION 'locking statistics is not allowed for system schemas: "%"', $1;
1155 SELECT dbms_stats.lock(cn.oid)
1157 FROM pg_class c, pg_namespace n
1158 WHERE c.relnamespace = n.oid
1159 AND dbms_stats.is_target_relkind(c.relkind)
1165 LANGUAGE plpgsql STRICT;
1167 CREATE FUNCTION dbms_stats.lock_table_stats(relid regclass)
1170 SELECT dbms_stats.lock($1)
1172 LANGUAGE sql STRICT;
1174 CREATE FUNCTION dbms_stats.lock_table_stats(
1177 ) RETURNS regclass AS
1179 SELECT dbms_stats.lock(dbms_stats.relname($1, $2)::regclass)
1181 LANGUAGE sql STRICT;
1183 CREATE FUNCTION dbms_stats.lock_column_stats(
1186 ) RETURNS regclass AS
1188 SELECT dbms_stats.lock($1, $2)
1190 LANGUAGE sql STRICT;
1192 CREATE FUNCTION dbms_stats.lock_column_stats(
1196 ) RETURNS regclass AS
1198 SELECT dbms_stats.lock(dbms_stats.relname($1, $2)::regclass, $3)
1200 LANGUAGE sql STRICT;
1203 -- UNLOCK_STATS: Statistics unlock functions
1206 CREATE FUNCTION dbms_stats.unlock(
1207 relid regclass DEFAULT NULL,
1208 attname text DEFAULT NULL
1209 ) RETURNS SETOF regclass AS
1215 IF $1 IS NULL AND $2 IS NOT NULL THEN
1216 RAISE EXCEPTION 'relation required';
1220 * Lock the target relation to prevent conflicting with stats lock/restore
1222 PERFORM * FROM dbms_stats.relation_stats_locked ru
1223 WHERE (ru.relid = $1 OR $1 IS NULL) FOR UPDATE;
1225 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
1226 WHERE a.attrelid = $1 AND a.attname = $2;
1227 IF $2 IS NOT NULL AND set_attnum IS NULL THEN
1228 RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
1231 DELETE FROM dbms_stats.column_stats_locked
1232 WHERE (starelid = $1 OR $1 IS NULL)
1233 AND (staattnum = set_attnum OR $2 IS NULL);
1235 IF $1 IS NOT NULL AND $2 IS NOT NULL THEN
1241 FROM dbms_stats.relation_stats_locked ru
1242 WHERE (ru.relid = $1 OR $1 IS NULL) AND ($2 IS NULL)
1245 DELETE FROM dbms_stats.relation_stats_locked ru
1246 WHERE ru.relid = unlock_id;
1247 RETURN NEXT unlock_id;
1253 CREATE FUNCTION dbms_stats.unlock_database_stats()
1254 RETURNS SETOF regclass AS
1259 LOCK dbms_stats.relation_stats_locked IN EXCLUSIVE MODE;
1263 FROM dbms_stats.relation_stats_locked
1266 DELETE FROM dbms_stats.relation_stats_locked
1267 WHERE relid = unlock_id;
1268 RETURN NEXT unlock_id;
1272 LANGUAGE plpgsql STRICT;
1274 CREATE FUNCTION dbms_stats.unlock_schema_stats(
1276 ) RETURNS SETOF regclass AS
1281 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
1282 RAISE EXCEPTION 'schema "%" not found', $1;
1284 IF dbms_stats.is_system_schema($1) THEN
1285 RAISE EXCEPTION 'unlocking statistics is not allowed for system schemas: "%"', $1;
1290 FROM dbms_stats.relation_stats_locked r, pg_class c, pg_namespace n
1292 AND c.relnamespace = n.oid
1297 DELETE FROM dbms_stats.relation_stats_locked
1298 WHERE relid = unlock_id;
1299 RETURN NEXT unlock_id;
1303 LANGUAGE plpgsql STRICT;
1305 CREATE FUNCTION dbms_stats.unlock_table_stats(relid regclass)
1306 RETURNS SETOF regclass AS
1308 DELETE FROM dbms_stats.relation_stats_locked
1310 RETURNING relid::regclass
1312 LANGUAGE sql STRICT;
1314 CREATE FUNCTION dbms_stats.unlock_table_stats(
1317 ) RETURNS SETOF regclass AS
1319 DELETE FROM dbms_stats.relation_stats_locked
1320 WHERE relid = dbms_stats.relname($1, $2)::regclass
1321 RETURNING relid::regclass
1323 LANGUAGE sql STRICT;
1325 CREATE FUNCTION dbms_stats.unlock_column_stats(
1328 ) RETURNS SETOF regclass AS
1333 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
1334 WHERE a.attrelid = $1 AND a.attname = $2;
1335 IF $2 IS NOT NULL AND set_attnum IS NULL THEN
1336 RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
1339 /* Lock the locked table stats */
1340 PERFORM * from dbms_stats.relation_stats_locked r
1341 WHERE r.relid = $1 FOR SHARE;
1343 DELETE FROM dbms_stats.column_stats_locked
1345 AND staattnum = set_attnum;
1351 LANGUAGE plpgsql STRICT;
1353 CREATE FUNCTION dbms_stats.unlock_column_stats(
1357 ) RETURNS SETOF regclass AS
1362 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
1363 WHERE a.attrelid = dbms_stats.relname($1, $2)::regclass
1365 IF $3 IS NOT NULL AND set_attnum IS NULL THEN
1366 RAISE EXCEPTION 'column "%" not found in relation "%.%"', $3, $1, $2;
1369 /* Lock the locked table stats */
1370 PERFORM * from dbms_stats.relation_stats_locked r
1371 WHERE relid = dbms_stats.relname($1, $2)::regclass FOR SHARE;
1373 DELETE FROM dbms_stats.column_stats_locked
1374 WHERE starelid = dbms_stats.relname($1, $2)::regclass
1375 AND staattnum = set_attnum;
1378 SELECT dbms_stats.relname($1, $2)::regclass;
1381 LANGUAGE plpgsql STRICT;
1384 -- IMPORT_STATS: Statistics import functions
1387 CREATE FUNCTION dbms_stats.import(
1388 nspname text DEFAULT NULL,
1389 relid regclass DEFAULT NULL,
1390 attname text DEFAULT NULL,
1391 src text DEFAULT NULL
1393 'MODULE_PATHNAME', 'dbms_stats_import'
1396 CREATE FUNCTION dbms_stats.import_database_stats(src text)
1399 SELECT dbms_stats.import(NULL, NULL, NULL, $1)
1403 CREATE FUNCTION dbms_stats.import_schema_stats(
1408 SELECT dbms_stats.import($1, NULL, NULL, $2)
1412 CREATE FUNCTION dbms_stats.import_table_stats(
1417 SELECT dbms_stats.import(NULL, $1, NULL, $2)
1421 CREATE FUNCTION dbms_stats.import_table_stats(
1427 SELECT dbms_stats.import(NULL, dbms_stats.relname($1, $2)::regclass, NULL, $3)
1431 CREATE FUNCTION dbms_stats.import_column_stats(
1437 SELECT dbms_stats.import(NULL, $1, $2, $3)
1441 CREATE FUNCTION dbms_stats.import_column_stats(
1448 SELECT dbms_stats.import(NULL, dbms_stats.relname($1, $2)::regclass, $3, $4)
1453 -- PURGE_STATS: Statistics purge function
1455 CREATE FUNCTION dbms_stats.purge_stats(
1457 force bool DEFAULT false
1458 ) RETURNS SETOF dbms_stats.backup_history AS
1462 todelete dbms_stats.backup_history;
1465 RAISE EXCEPTION 'backup id required';
1468 RAISE EXCEPTION 'NULL is not allowed as the second parameter';
1471 IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
1472 WHERE id = $1 FOR UPDATE) THEN
1473 RAISE EXCEPTION 'backup id % not found', $1;
1475 IF NOT $2 AND NOT EXISTS(SELECT *
1476 FROM dbms_stats.backup_history
1479 RAISE WARNING 'no database-wide backup will remain after purge'
1480 USING HINT = 'Give true for second parameter to purge forcibly.';
1485 SELECT * FROM dbms_stats.backup_history
1487 ORDER BY id FOR UPDATE
1489 DELETE FROM dbms_stats.backup_history
1490 WHERE id = todelete.id;
1491 RETURN NEXT todelete;
1498 -- CLEAN_STATS: Clean orphan dummy statistics
1500 CREATE FUNCTION dbms_stats.clean_up_stats() RETURNS SETOF text AS
1508 -- We don't have to check that table-level dummy statistics of the table
1509 -- exists here, because the foreign key constraints defined on column-level
1510 -- dummy static table ensures that.
1511 FOR clean_rel_col, clean_relid, clean_attnum, clean_inherit IN
1512 SELECT r.relname || ', ' || v.staattnum::text,
1513 v.starelid, v.staattnum, v.stainherit
1514 FROM dbms_stats.column_stats_locked v
1515 JOIN dbms_stats.relation_stats_locked r ON (v.starelid = r.relid)
1519 WHERE a.attrelid = v.starelid
1520 AND a.attnum = v.staattnum
1521 AND a.attisdropped = false
1525 DELETE FROM dbms_stats.column_stats_locked
1526 WHERE starelid = clean_relid
1527 AND staattnum = clean_attnum
1528 AND stainherit = clean_inherit;
1529 RETURN NEXT clean_rel_col;
1533 DELETE FROM dbms_stats.relation_stats_locked r
1537 WHERE c.oid = r.relid)
1538 RETURNING relname || ',';
1545 * Stuff for manipulating statistics
1548 /* Primitive functions for tweaking statistics */
1549 CREATE FUNCTION dbms_stats.anyarray_basetype(dbms_stats.anyarray)
1551 AS 'MODULE_PATHNAME', 'dbms_stats_anyarray_basetype'
1554 CREATE FUNCTION dbms_stats.type_is_analyzable(oid) returns bool
1555 AS 'MODULE_PATHNAME', 'dbms_stats_type_is_analyzable'
1556 LANGUAGE C STRICT STABLE;
1559 * Create and drop a cast necessary to set column values of dbms_stats.anyarray
1562 CREATE OR REPLACE FUNCTION dbms_stats.prepare_statstweak(regtype)
1570 srctypname := $1 || '[]';
1571 funcname := 'dbms_stats._' || replace($1::text, ' ', '_') || '_ary_anyarray';
1572 funcdef := funcname || '(' || srctypname || ')';
1573 castdef := '(' || srctypname || ' AS dbms_stats.anyarray)';
1575 IF (NOT dbms_stats.type_is_analyzable($1::regtype)) THEN
1576 RAISE 'the type can not have statistics';
1579 EXECUTE 'CREATE FUNCTION ' || funcdef ||
1580 ' RETURNS dbms_stats.anyarray ' ||
1581 ' AS ''pg_dbms_stats'', ''dbms_stats_anyary_anyary'''||
1582 ' LANGUAGE C STRICT IMMUTABLE';
1583 EXECUTE 'CREATE CAST '|| castdef ||
1584 ' WITH FUNCTION ' || funcdef ||
1586 RETURN '(func ' || funcdef || ', cast ' || castdef || ')';
1588 WHEN duplicate_function THEN
1589 RAISE 'run dbms_stats.drop_statstweak() for the type before this';
1591 $$ LANGUAGE plpgsql;
1593 CREATE OR REPLACE FUNCTION dbms_stats.drop_statstweak(regtype)
1601 srctypname := $1 || '[]';
1602 funcname := 'dbms_stats._' || replace($1::text, ' ', '_') || '_ary_anyarray';
1603 funcdef := funcname || '(' || srctypname || ')';
1604 castdef := '(' || srctypname || ' AS dbms_stats.anyarray)';
1606 EXECUTE 'DROP CAST ' || castdef;
1607 EXECUTE 'DROP FUNCTION ' || funcdef;
1608 RETURN '(func ' || funcdef || ', cast ' || castdef || ')';
1610 WHEN undefined_function OR undefined_object THEN
1611 RAISE 'function % or cast % does not exist', funcdef, castdef;
1613 $$ LANGUAGE plpgsql;