1 /* pg_dbms_stats/pg_dbms_stats--1.3.7.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,
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
76 -- Statistics backup tables
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,
86 CREATE TABLE dbms_stats.relation_stats_backup (
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
100 CREATE TABLE dbms_stats.column_stats_backup (
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,
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
138 CREATE FUNCTION dbms_stats.relname(nspname text, relname text)
140 $$SELECT quote_ident($1) || '.' || quote_ident($2)$$
141 LANGUAGE sql STABLE STRICT;
143 CREATE FUNCTION dbms_stats.is_system_schema(schemaname text)
145 'MODULE_PATHNAME', 'dbms_stats_is_system_schema'
146 LANGUAGE C IMMUTABLE STRICT;
148 CREATE FUNCTION dbms_stats.is_system_catalog(relid regclass)
150 'MODULE_PATHNAME', 'dbms_stats_is_system_catalog'
153 CREATE FUNCTION dbms_stats.is_target_relkind(relkind "char")
155 $$SELECT $1 IN ('r', 'i', 'f', 'm')$$
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'
165 CREATE VIEW dbms_stats.relation_stats_effective AS
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,
173 (pg_relation_size(c.oid) / current_setting('block_size')::int4)::int4)
175 COALESCE(v.last_analyze,
176 pg_catalog.pg_stat_get_last_analyze_time(c.oid))
178 COALESCE(v.last_autoanalyze,
179 pg_catalog.pg_stat_get_last_autoanalyze_time(c.oid))
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
186 WHERE dbms_stats.is_target_relkind(c.relkind)
187 AND NOT dbms_stats.is_system_schema(nspname);
189 CREATE VIEW dbms_stats.column_stats_effective AS
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)
199 WHERE a.attrelid = starelid
200 AND a.attnum = staattnum
201 AND a.attisdropped = false
204 WHERE starelid IS NOT NULL;
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.3, 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.
213 CREATE VIEW dbms_stats.stats AS
215 nspname AS schemaname,
216 relname AS tablename,
218 stainherit AS inherited,
219 stanullfrac AS null_frac,
220 stawidth AS avg_width,
221 stadistinct AS n_distinct,
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,
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,
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,
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]
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,
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,
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');
280 CREATE FUNCTION dbms_stats.invalidate_relation_cache()
282 'MODULE_PATHNAME', 'dbms_stats_invalidate_relation_cache'
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();
291 CREATE FUNCTION dbms_stats.invalidate_column_cache()
293 'MODULE_PATHNAME', 'dbms_stats_invalidate_column_cache'
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();
303 -- BACKUP_STATS: Statistics backup functions
306 CREATE FUNCTION dbms_stats.backup(
312 /* Lock the backup id */
313 SELECT * from dbms_stats.backup_history
314 WHERE id = $1 FOR UPDATE;
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);
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);
343 CREATE FUNCTION dbms_stats.backup(
344 relid regclass DEFAULT NULL,
345 attname text DEFAULT NULL,
346 comment text DEFAULT NULL
351 backup_relkind "char";
355 IF $1 IS NULL AND $2 IS NOT NULL THEN
356 RAISE EXCEPTION 'relation required';
358 IF $1 IS NOT NULL THEN
359 SELECT relkind INTO backup_relkind
360 FROM pg_catalog.pg_class WHERE oid = $1 FOR SHARE;
362 RAISE EXCEPTION 'relation "%" not found', $1;
364 IF NOT dbms_stats.is_target_relkind(backup_relkind) THEN
365 RAISE EXCEPTION 'relation of relkind "%" cannot have statistics to backup: "%"',
367 USING HINT = 'Only tables(r), materialized views(m), foreign tables(f) and indexes(i) are allowed.';
369 IF dbms_stats.is_system_catalog($1) THEN
370 RAISE EXCEPTION 'backing up statistics is inhibited for system catalogs: "%"', $1;
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;
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;
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;
397 CREATE FUNCTION dbms_stats.backup_database_stats(
401 SELECT dbms_stats.backup(NULL, NULL, $1)
405 CREATE FUNCTION dbms_stats.backup_schema_stats(
413 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1 FOR SHARE)
415 RAISE EXCEPTION 'schema "%" not found', $1;
417 IF dbms_stats.is_system_schema($1) THEN
418 RAISE EXCEPTION 'backing up statistics is inhibited for system schemas: "%"', $1;
421 INSERT INTO dbms_stats.backup_history(time, unit, comment)
422 VALUES (current_timestamp, 's', comment)
423 RETURNING id INTO backup_id;
425 PERFORM dbms_stats.backup(backup_id, cn.oid, NULL)
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)
440 CREATE FUNCTION dbms_stats.backup_table_stats(
445 SELECT dbms_stats.backup($1, NULL, $2)
449 CREATE FUNCTION dbms_stats.backup_table_stats(
455 SELECT dbms_stats.backup(dbms_stats.relname($1, $2)::regclass, NULL, $3)
459 CREATE FUNCTION dbms_stats.backup_column_stats(
465 SELECT dbms_stats.backup($1, $2, $3)
469 CREATE FUNCTION dbms_stats.backup_column_stats(
476 SELECT dbms_stats.backup(dbms_stats.relname($1, $2)::regclass, $3, $4)
481 -- RESTORE_STATS: Statistics restore functions
483 CREATE FUNCTION dbms_stats.restore(
485 relid regclass DEFAULT NULL,
486 attname text DEFAULT NULL
487 ) RETURNS SETOF regclass AS
491 restore_relid regclass;
494 restore_attname text;
495 restore_type regtype;
499 RAISE EXCEPTION 'backup id required';
501 IF $2 IS NULL AND $3 IS NOT NULL THEN
502 RAISE EXCEPTION 'relation required';
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;
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;
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;
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;
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;
528 PERFORM * FROM dbms_stats.relation_stats_locked r
529 WHERE r.relid = $2 FOR UPDATE;
531 /* Lock the whole relation stats if relation is not specified.*/
532 LOCK dbms_stats.relation_stats_locked IN EXCLUSIVE MODE;
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)
541 AND dbms_stats.is_target_relkind(c.relkind)
542 AND NOT dbms_stats.is_system_catalog(c.oid)
546 ORDER BY coid::regclass::text
548 UPDATE dbms_stats.relation_stats_locked r
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;
562 INSERT INTO dbms_stats.relation_stats_locked
571 FROM dbms_stats.relation_stats_backup b
572 WHERE b.id = restore_id
573 AND b.relid = restore_relid;
575 RETURN NEXT restore_relid;
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
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)
592 GROUP BY c.oid, a.attnum) t
593 WHERE a.attrelid = t.oid
594 AND a.attnum = t.attnum
596 AND b.starelid = t.oid
597 AND b.staattnum = t.attnum
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;
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;
624 WHEN unique_violation THEN
625 RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
630 CREATE FUNCTION dbms_stats.restore_database_stats(
631 as_of_timestamp timestamp with time zone
632 ) RETURNS SETOF regclass AS
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
644 ORDER BY t1.relid) m;
648 CREATE FUNCTION dbms_stats.restore_schema_stats(
650 as_of_timestamp timestamp with time zone
651 ) RETURNS SETOF regclass AS
654 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
655 RAISE EXCEPTION 'schema "%" not found', $1;
657 IF dbms_stats.is_system_schema($1) THEN
658 RAISE EXCEPTION 'restoring statistics is inhibited for system schemas: "%"', $1;
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
675 ORDER BY t1.relid) m;
678 LANGUAGE plpgsql STRICT;
680 CREATE FUNCTION dbms_stats.restore_table_stats(
682 as_of_timestamp timestamp with time zone
683 ) RETURNS SETOF regclass AS
685 SELECT dbms_stats.restore(max(id), $1, NULL)
686 FROM dbms_stats.backup_history WHERE time <= $2
690 CREATE FUNCTION dbms_stats.restore_table_stats(
693 as_of_timestamp timestamp with time zone
694 ) RETURNS SETOF regclass AS
696 SELECT dbms_stats.restore_table_stats(dbms_stats.relname($1, $2)::regclass, $3)
700 CREATE FUNCTION dbms_stats.restore_column_stats(
703 as_of_timestamp timestamp with time zone
704 ) RETURNS SETOF regclass AS
706 SELECT dbms_stats.restore(max(id), $1, $2)
707 FROM dbms_stats.backup_history WHERE time <= $3
711 CREATE FUNCTION dbms_stats.restore_column_stats(
715 as_of_timestamp timestamp with time zone
716 ) RETURNS SETOF regclass AS
718 SELECT dbms_stats.restore(max(id), dbms_stats.relname($1, $2)::regclass, $3)
719 FROM dbms_stats.backup_history WHERE time <= $4
723 CREATE FUNCTION dbms_stats.restore_stats(
725 ) RETURNS SETOF regclass AS
728 restore_relid regclass;
730 restore_attname text;
731 restore_type regtype;
734 IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id = $1) THEN
735 RAISE EXCEPTION 'backup id % not found', $1;
738 /* Lock the backup */
739 PERFORM * from dbms_stats.relation_stats_backup b
740 WHERE id = $1 FOR SHARE;
742 /* Locking only relation_stats_locked is sufficient */
743 LOCK dbms_stats.relation_stats_locked IN EXCLUSIVE MODE;
748 JOIN dbms_stats.relation_stats_backup b ON (c.oid = b.relid)
750 ORDER BY c.oid::regclass::text
752 UPDATE dbms_stats.relation_stats_locked r
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
764 AND b.relid = restore_relid;
766 INSERT INTO dbms_stats.relation_stats_locked
775 FROM dbms_stats.relation_stats_backup b
777 AND b.relid = restore_relid;
779 RETURN NEXT restore_relid;
782 FOR restore_relid, restore_attnum, restore_type, cur_type IN
783 SELECT c.oid, a.attnum, b.statypid, a.atttypid
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)
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;
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
810 AND starelid = restore_relid
811 AND staattnum = restore_attnum;
817 LANGUAGE plpgsql STRICT;
820 -- LOCK_STATS: Statistics lock functions
823 CREATE FUNCTION dbms_stats.lock(
826 ) RETURNS regclass AS
834 RAISE EXCEPTION 'relation required';
837 RETURN dbms_stats.lock($1);
839 SELECT relkind INTO lock_relkind FROM pg_catalog.pg_class WHERE oid = $1;
841 RAISE EXCEPTION 'relation "%" not found', $1;
843 IF NOT dbms_stats.is_target_relkind(lock_relkind) THEN
844 RAISE EXCEPTION '"%" must be a table or an index', $1;
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;
849 IF dbms_stats.is_system_catalog($1) THEN
850 RAISE EXCEPTION 'locking statistics is inhibited for system catalogs: "%"', $1;
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;
859 * If we don't have per-table statistics, create new one which has NULL for
860 * every statistic value for column_stats_effective.
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
873 * Process for per-column statistics
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
883 AND staattnum = set_attnum
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,
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;
914 INSERT INTO dbms_stats.column_stats_locked
944 /* If we don't have statistics at all, raise error. */
946 RAISE EXCEPTION 'no statistics available for column "%" of relation "%"', $2, $1::regclass;
951 WHEN unique_violation THEN
952 RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
957 CREATE FUNCTION dbms_stats.lock(relid regclass)
965 RAISE EXCEPTION 'relation required';
967 SELECT relkind INTO lock_relkind FROM pg_catalog.pg_class WHERE oid = $1;
969 RAISE EXCEPTION 'relation "%" not found', $1;
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.';
975 IF dbms_stats.is_system_catalog($1) THEN
976 RAISE EXCEPTION 'locking statistics is not allowed for system catalogs: "%"', $1;
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
992 AND c.relnamespace = n.oid
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
1003 AND c.relnamespace = n.oid
1008 FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_index ind
1009 ON c.oid = ind.indexrelid
1012 AND ind.indexprs IS NULL) THEN
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
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,
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;
1055 INSERT INTO dbms_stats.column_stats_locked
1087 WHEN unique_violation THEN
1088 RAISE EXCEPTION 'This operation is canceled by simultaneous lock operation on the same relation.';
1093 CREATE FUNCTION dbms_stats.lock_database_stats()
1094 RETURNS SETOF regclass AS
1096 SELECT dbms_stats.lock(c.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
1106 CREATE FUNCTION dbms_stats.lock_schema_stats(
1108 ) RETURNS SETOF regclass AS
1111 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
1112 RAISE EXCEPTION 'schema "%" not found', $1;
1114 IF dbms_stats.is_system_schema($1) THEN
1115 RAISE EXCEPTION 'locking statistics is not allowed for system schemas: "%"', $1;
1119 SELECT dbms_stats.lock(cn.oid)
1121 FROM pg_class c, pg_namespace n
1122 WHERE c.relnamespace = n.oid
1123 AND dbms_stats.is_target_relkind(c.relkind)
1129 LANGUAGE plpgsql STRICT;
1131 CREATE FUNCTION dbms_stats.lock_table_stats(relid regclass)
1134 SELECT dbms_stats.lock($1)
1136 LANGUAGE sql STRICT;
1138 CREATE FUNCTION dbms_stats.lock_table_stats(
1141 ) RETURNS regclass AS
1143 SELECT dbms_stats.lock(dbms_stats.relname($1, $2)::regclass)
1145 LANGUAGE sql STRICT;
1147 CREATE FUNCTION dbms_stats.lock_column_stats(
1150 ) RETURNS regclass AS
1152 SELECT dbms_stats.lock($1, $2)
1154 LANGUAGE sql STRICT;
1156 CREATE FUNCTION dbms_stats.lock_column_stats(
1160 ) RETURNS regclass AS
1162 SELECT dbms_stats.lock(dbms_stats.relname($1, $2)::regclass, $3)
1164 LANGUAGE sql STRICT;
1167 -- UNLOCK_STATS: Statistics unlock functions
1170 CREATE FUNCTION dbms_stats.unlock(
1171 relid regclass DEFAULT NULL,
1172 attname text DEFAULT NULL
1173 ) RETURNS SETOF regclass AS
1179 IF $1 IS NULL AND $2 IS NOT NULL THEN
1180 RAISE EXCEPTION 'relation required';
1184 * Lock the target relation to prevent conflicting with stats lock/restore
1186 PERFORM * FROM dbms_stats.relation_stats_locked ru
1187 WHERE (ru.relid = $1 OR $1 IS NULL) FOR UPDATE;
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;
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);
1199 IF $1 IS NOT NULL AND $2 IS NOT NULL THEN
1205 FROM dbms_stats.relation_stats_locked ru
1206 WHERE (ru.relid = $1 OR $1 IS NULL) AND ($2 IS NULL)
1209 DELETE FROM dbms_stats.relation_stats_locked ru
1210 WHERE ru.relid = unlock_id;
1211 RETURN NEXT unlock_id;
1217 CREATE FUNCTION dbms_stats.unlock_database_stats()
1218 RETURNS SETOF regclass AS
1223 LOCK dbms_stats.relation_stats_locked IN EXCLUSIVE MODE;
1227 FROM dbms_stats.relation_stats_locked
1230 DELETE FROM dbms_stats.relation_stats_locked
1231 WHERE relid = unlock_id;
1232 RETURN NEXT unlock_id;
1236 LANGUAGE plpgsql STRICT;
1238 CREATE FUNCTION dbms_stats.unlock_schema_stats(
1240 ) RETURNS SETOF regclass AS
1245 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
1246 RAISE EXCEPTION 'schema "%" not found', $1;
1248 IF dbms_stats.is_system_schema($1) THEN
1249 RAISE EXCEPTION 'unlocking statistics is not allowed for system schemas: "%"', $1;
1254 FROM dbms_stats.relation_stats_locked r, pg_class c, pg_namespace n
1256 AND c.relnamespace = n.oid
1261 DELETE FROM dbms_stats.relation_stats_locked
1262 WHERE relid = unlock_id;
1263 RETURN NEXT unlock_id;
1267 LANGUAGE plpgsql STRICT;
1269 CREATE FUNCTION dbms_stats.unlock_table_stats(relid regclass)
1270 RETURNS SETOF regclass AS
1272 DELETE FROM dbms_stats.relation_stats_locked
1274 RETURNING relid::regclass
1276 LANGUAGE sql STRICT;
1278 CREATE FUNCTION dbms_stats.unlock_table_stats(
1281 ) RETURNS SETOF regclass AS
1283 DELETE FROM dbms_stats.relation_stats_locked
1284 WHERE relid = dbms_stats.relname($1, $2)::regclass
1285 RETURNING relid::regclass
1287 LANGUAGE sql STRICT;
1289 CREATE FUNCTION dbms_stats.unlock_column_stats(
1292 ) RETURNS SETOF regclass AS
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;
1303 /* Lock the locked table stats */
1304 PERFORM * from dbms_stats.relation_stats_locked r
1305 WHERE r.relid = $1 FOR SHARE;
1307 DELETE FROM dbms_stats.column_stats_locked
1309 AND staattnum = set_attnum;
1315 LANGUAGE plpgsql STRICT;
1317 CREATE FUNCTION dbms_stats.unlock_column_stats(
1321 ) RETURNS SETOF regclass AS
1326 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
1327 WHERE a.attrelid = dbms_stats.relname($1, $2)::regclass
1329 IF $3 IS NOT NULL AND set_attnum IS NULL THEN
1330 RAISE EXCEPTION 'column "%" not found in relation "%.%"', $3, $1, $2;
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;
1337 DELETE FROM dbms_stats.column_stats_locked
1338 WHERE starelid = dbms_stats.relname($1, $2)::regclass
1339 AND staattnum = set_attnum;
1342 SELECT dbms_stats.relname($1, $2)::regclass;
1345 LANGUAGE plpgsql STRICT;
1348 -- IMPORT_STATS: Statistics import functions
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
1357 'MODULE_PATHNAME', 'dbms_stats_import'
1360 CREATE FUNCTION dbms_stats.import_database_stats(src text)
1363 SELECT dbms_stats.import(NULL, NULL, NULL, $1)
1367 CREATE FUNCTION dbms_stats.import_schema_stats(
1372 SELECT dbms_stats.import($1, NULL, NULL, $2)
1376 CREATE FUNCTION dbms_stats.import_table_stats(
1381 SELECT dbms_stats.import(NULL, $1, NULL, $2)
1385 CREATE FUNCTION dbms_stats.import_table_stats(
1391 SELECT dbms_stats.import(NULL, dbms_stats.relname($1, $2)::regclass, NULL, $3)
1395 CREATE FUNCTION dbms_stats.import_column_stats(
1401 SELECT dbms_stats.import(NULL, $1, $2, $3)
1405 CREATE FUNCTION dbms_stats.import_column_stats(
1412 SELECT dbms_stats.import(NULL, dbms_stats.relname($1, $2)::regclass, $3, $4)
1417 -- PURGE_STATS: Statistics purge function
1419 CREATE FUNCTION dbms_stats.purge_stats(
1421 force bool DEFAULT false
1422 ) RETURNS SETOF dbms_stats.backup_history AS
1426 todelete dbms_stats.backup_history;
1429 RAISE EXCEPTION 'backup id required';
1432 RAISE EXCEPTION 'NULL is not allowed as the second parameter';
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;
1439 IF NOT $2 AND NOT EXISTS(SELECT *
1440 FROM dbms_stats.backup_history
1443 RAISE WARNING 'no database-wide backup will remain after purge'
1444 USING HINT = 'Give true for second parameter to purge forcibly.';
1449 SELECT * FROM dbms_stats.backup_history
1451 ORDER BY id FOR UPDATE
1453 DELETE FROM dbms_stats.backup_history
1454 WHERE id = todelete.id;
1455 RETURN NEXT todelete;
1462 -- CLEAN_STATS: Clean orphan dummy statistics
1464 CREATE FUNCTION dbms_stats.clean_up_stats() RETURNS SETOF text AS
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)
1483 WHERE a.attrelid = v.starelid
1484 AND a.attnum = v.staattnum
1485 AND a.attisdropped = false
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;
1497 DELETE FROM dbms_stats.relation_stats_locked r
1501 WHERE c.oid = r.relid)
1502 RETURNING relname || ',';