1 /* pg_dbms_stats/pg_dbms_stats--1.3.2.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,
38 last_analyze timestamp with time zone,
39 last_autoanalyze timestamp with time zone,
43 CREATE TABLE dbms_stats._column_stats_locked (
44 starelid oid NOT NULL,
45 staattnum int2 NOT NULL,
46 stainherit bool NOT NULL,
62 stavalues1 dbms_stats.anyarray,
63 stavalues2 dbms_stats.anyarray,
64 stavalues3 dbms_stats.anyarray,
65 stavalues4 dbms_stats.anyarray,
66 PRIMARY KEY (starelid, staattnum, stainherit),
67 FOREIGN KEY (starelid) REFERENCES dbms_stats._relation_stats_locked (relid) ON DELETE CASCADE
71 -- Statistics backup tables
74 CREATE TABLE dbms_stats.backup_history (
75 id serial8 PRIMARY KEY,
76 time timestamp with time zone NOT NULL,
77 unit char(1) NOT NULL,
81 CREATE TABLE dbms_stats.relation_stats_backup (
84 relname text NOT NULL,
85 relpages int4 NOT NULL,
86 reltuples float4 NOT NULL,
87 curpages int4 NOT NULL,
88 last_analyze timestamp with time zone,
89 last_autoanalyze timestamp with time zone,
90 PRIMARY KEY (id, relid),
91 FOREIGN KEY (id) REFERENCES dbms_stats.backup_history (id) ON DELETE CASCADE
94 CREATE TABLE dbms_stats.column_stats_backup (
96 statypid oid NOT NULL,
97 starelid oid NOT NULL,
98 staattnum int2 NOT NULL,
99 stainherit bool NOT NULL,
100 stanullfrac float4 NOT NULL,
101 stawidth int4 NOT NULL,
102 stadistinct float4 NOT NULL,
103 stakind1 int2 NOT NULL,
104 stakind2 int2 NOT NULL,
105 stakind3 int2 NOT NULL,
106 stakind4 int2 NOT NULL,
111 stanumbers1 float4[],
112 stanumbers2 float4[],
113 stanumbers3 float4[],
114 stanumbers4 float4[],
115 stavalues1 dbms_stats.anyarray,
116 stavalues2 dbms_stats.anyarray,
117 stavalues3 dbms_stats.anyarray,
118 stavalues4 dbms_stats.anyarray,
119 PRIMARY KEY (id, starelid, staattnum, stainherit),
120 FOREIGN KEY (id) REFERENCES dbms_stats.backup_history (id) ON DELETE CASCADE,
121 FOREIGN KEY (id, starelid) REFERENCES dbms_stats.relation_stats_backup (id, relid) ON DELETE CASCADE
128 CREATE FUNCTION dbms_stats.relname(nspname text, relname text)
130 $$SELECT quote_ident($1) || '.' || quote_ident($2)$$
131 LANGUAGE sql STABLE STRICT;
133 CREATE FUNCTION dbms_stats.is_system_schema(schemaname text)
135 'MODULE_PATHNAME', 'dbms_stats_is_system_schema'
136 LANGUAGE C IMMUTABLE STRICT;
138 CREATE FUNCTION dbms_stats.is_system_catalog(relid regclass)
140 'MODULE_PATHNAME', 'dbms_stats_is_system_catalog'
143 CREATE FUNCTION dbms_stats.is_target_relkind(relkind "char")
145 $$SELECT $1 IN ('r', 'i')$$
148 CREATE FUNCTION dbms_stats.merge(
149 lhs dbms_stats._column_stats_locked,
150 rhs pg_catalog.pg_statistic
151 ) RETURNS dbms_stats._column_stats_locked AS
152 'MODULE_PATHNAME', 'dbms_stats_merge'
156 -- Statistics views for internal use
157 -- These views are used to merge authentic stats and dummy stats by hook
158 -- function, so we don't grant SELECT privilege to PUBLIC.
161 CREATE VIEW dbms_stats.relation_stats_effective AS
164 dbms_stats.relname(nspname, c.relname) AS relname,
165 COALESCE(v.relpages, c.relpages) AS relpages,
166 COALESCE(v.reltuples, c.reltuples) AS reltuples,
168 (pg_relation_size(c.oid) / current_setting('block_size')::int4)::int4)
170 COALESCE(v.last_analyze,
171 pg_catalog.pg_stat_get_last_analyze_time(c.oid))
173 COALESCE(v.last_autoanalyze,
174 pg_catalog.pg_stat_get_last_autoanalyze_time(c.oid))
176 FROM pg_catalog.pg_class c
177 JOIN pg_catalog.pg_namespace n
178 ON c.relnamespace = n.oid
179 LEFT JOIN dbms_stats._relation_stats_locked v
181 WHERE dbms_stats.is_target_relkind(c.relkind)
182 AND NOT dbms_stats.is_system_schema(nspname);
184 CREATE VIEW dbms_stats.column_stats_effective AS
186 SELECT (dbms_stats.merge(v, s)).*
187 FROM pg_catalog.pg_statistic s
188 FULL JOIN dbms_stats._column_stats_locked v
189 USING (starelid, staattnum, stainherit)
190 WHERE NOT dbms_stats.is_system_catalog(starelid)
194 WHERE a.attrelid = starelid
195 AND a.attnum = staattnum
196 AND a.attisdropped = false
199 WHERE starelid IS NOT NULL;
202 -- Statistics views for user use (including non-superusers)
203 -- These views allow users to see dummy statistics about tables which the
204 -- user has SELECT privilege.
207 CREATE VIEW dbms_stats.relation_stats_locked
209 FROM dbms_stats._relation_stats_locked;
211 GRANT SELECT ON dbms_stats.relation_stats_locked TO PUBLIC;
213 CREATE VIEW dbms_stats.column_stats_locked
215 FROM dbms_stats._column_stats_locked
216 WHERE has_column_privilege(starelid, staattnum, 'SELECT');
218 GRANT SELECT ON dbms_stats.column_stats_locked TO PUBLIC;
221 -- Note: This view is copied from pg_stats in
222 -- src/backend/catalog/system_views.sql in core source tree of version
223 -- 9.1, and customized for pg_dbms_stats. Changes from orignal one are:
224 -- - rename from pg_stats to dbms_stats.stats by a view name.
225 -- - changed the table name from pg_statistic to dbms_stats.column_stats_effective.
227 CREATE VIEW dbms_stats.stats AS
229 nspname AS schemaname,
230 relname AS tablename,
232 stainherit AS inherited,
233 stanullfrac AS null_frac,
234 stawidth AS avg_width,
235 stadistinct AS n_distinct,
237 WHEN stakind1 IN (1, 4) THEN stavalues1
238 WHEN stakind2 IN (1, 4) THEN stavalues2
239 WHEN stakind3 IN (1, 4) THEN stavalues3
240 WHEN stakind4 IN (1, 4) THEN stavalues4
241 END AS most_common_vals,
243 WHEN stakind1 IN (1, 4) THEN stanumbers1
244 WHEN stakind2 IN (1, 4) THEN stanumbers2
245 WHEN stakind3 IN (1, 4) THEN stanumbers3
246 WHEN stakind4 IN (1, 4) THEN stanumbers4
247 END AS most_common_freqs,
249 WHEN stakind1 = 2 THEN stavalues1
250 WHEN stakind2 = 2 THEN stavalues2
251 WHEN stakind3 = 2 THEN stavalues3
252 WHEN stakind4 = 2 THEN stavalues4
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]
260 FROM dbms_stats.column_stats_effective s JOIN pg_class c ON (c.oid = s.starelid)
261 JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
262 LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
263 WHERE NOT attisdropped AND has_column_privilege(c.oid, a.attnum, 'select');
265 GRANT SELECT ON dbms_stats.stats TO PUBLIC;
271 CREATE FUNCTION dbms_stats.invalidate_relation_cache()
273 'MODULE_PATHNAME', 'dbms_stats_invalidate_relation_cache'
276 -- Invalidate cached plans when dbms_stats._relation_stats_locked is modified.
277 CREATE TRIGGER invalidate_relation_cache
278 BEFORE INSERT OR DELETE OR UPDATE
279 ON dbms_stats._relation_stats_locked
280 FOR EACH ROW EXECUTE PROCEDURE dbms_stats.invalidate_relation_cache();
282 CREATE FUNCTION dbms_stats.invalidate_column_cache()
284 'MODULE_PATHNAME', 'dbms_stats_invalidate_column_cache'
287 -- Invalidate cached plans when dbms_stats._column_stats_locked is modified.
288 CREATE TRIGGER invalidate_column_cache
289 BEFORE INSERT OR DELETE OR UPDATE
290 ON dbms_stats._column_stats_locked
291 FOR EACH ROW EXECUTE PROCEDURE dbms_stats.invalidate_column_cache();
294 -- BACKUP_STATS: Statistics backup functions
297 CREATE FUNCTION dbms_stats.backup(
303 /* Lock the backup id */
304 SELECT * from dbms_stats.backup_history
305 WHERE id = $1 FOR UPDATE;
307 INSERT INTO dbms_stats.relation_stats_backup
308 SELECT $1, v.relid, v.relname, v.relpages, v.reltuples,
309 v.curpages, v.last_analyze, v.last_autoanalyze
310 FROM pg_catalog.pg_class c,
311 dbms_stats.relation_stats_effective v
312 WHERE c.oid = v.relid
313 AND dbms_stats.is_target_relkind(relkind)
314 AND NOT dbms_stats.is_system_catalog(v.relid)
315 AND (v.relid = $2 OR $2 IS NULL);
317 INSERT INTO dbms_stats.column_stats_backup
318 SELECT $1, atttypid, s.*
319 FROM pg_catalog.pg_class c,
320 dbms_stats.column_stats_effective s,
321 pg_catalog.pg_attribute a
322 WHERE c.oid = starelid
323 AND starelid = attrelid
324 AND staattnum = attnum
325 AND dbms_stats.is_target_relkind(relkind)
326 AND NOT dbms_stats.is_system_catalog(c.oid)
327 AND ($2 IS NULL OR starelid = $2)
328 AND ($3 IS NULL OR staattnum = $3);
334 CREATE FUNCTION dbms_stats.backup(
335 relid regclass DEFAULT NULL,
336 attname text DEFAULT NULL,
337 comment text DEFAULT NULL
342 backup_relkind "char";
346 IF $1 IS NULL AND $2 IS NOT NULL THEN
347 RAISE EXCEPTION 'relation required';
349 IF $1 IS NOT NULL THEN
350 SELECT relkind INTO backup_relkind
351 FROM pg_catalog.pg_class WHERE oid = $1 FOR SHARE;
353 RAISE EXCEPTION 'relation "%" not found', $1;
355 IF NOT dbms_stats.is_target_relkind(backup_relkind) THEN
356 RAISE EXCEPTION 'relation of relkind "%" cannot have statistics to backup: "%"',
358 USING HINT = 'Only tables(r) and indexes(i) are allowed.';
360 IF dbms_stats.is_system_catalog($1) THEN
361 RAISE EXCEPTION 'backing up statistics is inhibited for system catalogs: "%"', $1;
363 IF $2 IS NOT NULL THEN
364 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
365 WHERE a.attrelid = $1 AND a.attname = $2 FOR SHARE;
366 IF set_attnum IS NULL THEN
367 RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
369 IF NOT EXISTS(SELECT * FROM dbms_stats.column_stats_effective WHERE starelid = $1 AND staattnum = set_attnum) THEN
370 RAISE EXCEPTION 'no statistics available for column "%" of relation "%"', $2, $1;
380 INSERT INTO dbms_stats.backup_history(time, unit, comment)
381 VALUES (current_timestamp, unit_type, $3)
382 RETURNING dbms_stats.backup(id, $1, set_attnum) INTO backup_id;
388 CREATE FUNCTION dbms_stats.backup_database_stats(
392 SELECT dbms_stats.backup(NULL, NULL, $1)
396 CREATE FUNCTION dbms_stats.backup_schema_stats(
404 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1 FOR SHARE)
406 RAISE EXCEPTION 'schema "%" not found', $1;
408 IF dbms_stats.is_system_schema($1) THEN
409 RAISE EXCEPTION 'backing up statistics is inhibited for system schemas: "%"', $1;
412 INSERT INTO dbms_stats.backup_history(time, unit, comment)
413 VALUES (current_timestamp, 's', comment)
414 RETURNING id INTO backup_id;
416 PERFORM dbms_stats.backup(backup_id, cn.oid, NULL)
418 FROM pg_catalog.pg_class c,
419 pg_catalog.pg_namespace n
420 WHERE n.nspname = schemaname
421 AND c.relnamespace = n.oid
422 AND dbms_stats.is_target_relkind(c.relkind)
431 CREATE FUNCTION dbms_stats.backup_table_stats(
436 SELECT dbms_stats.backup($1, NULL, $2)
440 CREATE FUNCTION dbms_stats.backup_table_stats(
446 SELECT dbms_stats.backup(dbms_stats.relname($1, $2)::regclass, NULL, $3)
450 CREATE FUNCTION dbms_stats.backup_column_stats(
456 SELECT dbms_stats.backup($1, $2, $3)
460 CREATE FUNCTION dbms_stats.backup_column_stats(
467 SELECT dbms_stats.backup(dbms_stats.relname($1, $2)::regclass, $3, $4)
472 -- RESTORE_STATS: Statistics restore functions
474 CREATE FUNCTION dbms_stats.restore(
476 relid regclass DEFAULT NULL,
477 attname text DEFAULT NULL
478 ) RETURNS SETOF regclass AS
482 restore_relid regclass;
485 restore_attname text;
486 restore_type regtype;
490 RAISE EXCEPTION 'backup id required';
492 IF $2 IS NULL AND $3 IS NOT NULL THEN
493 RAISE EXCEPTION 'relation required';
495 IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
496 WHERE id <= $1 FOR SHARE) THEN
497 RAISE EXCEPTION 'backup id % not found', $1;
499 IF $2 IS NOT NULL THEN
500 IF NOT EXISTS(SELECT * FROM pg_catalog.pg_class
501 WHERE oid = $2 FOR SHARE) THEN
502 RAISE EXCEPTION 'relation "%" not found', $2;
504 -- Grabbing all backups for the relation which is not used in restore.
505 IF NOT EXISTS(SELECT * FROM dbms_stats.relation_stats_backup b
506 WHERE b.id <= $1 AND b.relid = $2 FOR SHARE) THEN
507 RAISE EXCEPTION 'statistics of relation "%" not found in any backups before backup id = %', $2, $1;
509 IF $3 IS NOT NULL THEN
510 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
511 WHERE a.attrelid = $2 AND a.attname = $3;
512 IF set_attnum IS NULL THEN
513 RAISE EXCEPTION 'column "%" not found in relation %', $3, $2;
515 IF NOT EXISTS(SELECT * FROM dbms_stats.column_stats_backup WHERE id <= $1 AND starelid = $2 AND staattnum = set_attnum) THEN
516 RAISE EXCEPTION 'statistics of column "%" of relation "%" are not found in any backups before',$3, $2, $1;
519 PERFORM * FROM dbms_stats._relation_stats_locked r
520 WHERE r.relid = $2 FOR UPDATE;
522 /* Lock the whole relation stats if relation is not specified.*/
523 LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
526 FOR restore_id, restore_relid IN
527 SELECT max(id), coid FROM
528 (SELECT b.id as id, c.oid as coid
529 FROM pg_class c, dbms_stats.relation_stats_backup b
530 WHERE (c.oid = $2 OR $2 IS NULL)
532 AND dbms_stats.is_target_relkind(c.relkind)
533 AND NOT dbms_stats.is_system_catalog(c.oid)
537 ORDER BY coid::regclass::text
539 UPDATE dbms_stats._relation_stats_locked r
542 relpages = b.relpages,
543 reltuples = b.reltuples,
544 curpages = b.curpages,
545 last_analyze = b.last_analyze,
546 last_autoanalyze = b.last_autoanalyze
547 FROM dbms_stats.relation_stats_backup b
548 WHERE r.relid = restore_relid
549 AND b.id = restore_id
550 AND b.relid = restore_relid;
552 INSERT INTO dbms_stats._relation_stats_locked
560 FROM dbms_stats.relation_stats_backup b
561 WHERE b.id = restore_id
562 AND b.relid = restore_relid;
564 RETURN NEXT restore_relid;
567 FOR restore_id, restore_relid, restore_attnum, restore_type, cur_type IN
568 SELECT t.id, t.oid, t.attnum, b.statypid, a.atttypid
570 dbms_stats.column_stats_backup b,
571 (SELECT max(b.id) AS id, c.oid, a.attnum
572 FROM pg_class c, pg_attribute a, dbms_stats.column_stats_backup b
573 WHERE (c.oid = $2 OR $2 IS NULL)
574 AND c.oid = a.attrelid
575 AND c.oid = b.starelid
576 AND (a.attnum = set_attnum OR set_attnum IS NULL)
577 AND a.attnum = b.staattnum
578 AND NOT a.attisdropped
579 AND dbms_stats.is_target_relkind(c.relkind)
581 GROUP BY c.oid, a.attnum) t
582 WHERE a.attrelid = t.oid
583 AND a.attnum = t.attnum
585 AND b.starelid = t.oid
586 AND b.staattnum = t.attnum
588 IF restore_type <> cur_type THEN
589 SELECT a.attname INTO restore_attname
590 FROM pg_catalog.pg_attribute a
591 WHERE a.attrelid = restore_relid
592 AND a.attnum = restore_attnum;
593 RAISE WARNING 'data type of column "%.%" is inconsistent between database(%) and backup (%). Skip.',
594 restore_relid, restore_attname, cur_type, restore_type;
596 DELETE FROM dbms_stats._column_stats_locked
597 WHERE starelid = restore_relid
598 AND staattnum = restore_attnum;
599 INSERT INTO dbms_stats._column_stats_locked
600 SELECT starelid, staattnum, stainherit,
601 stanullfrac, stawidth, stadistinct,
602 stakind1, stakind2, stakind3, stakind4,
603 staop1, staop2, staop3, staop4,
604 stanumbers1, stanumbers2, stanumbers3, stanumbers4,
605 stavalues1, stavalues2, stavalues3, stavalues4
606 FROM dbms_stats.column_stats_backup
607 WHERE id = restore_id
608 AND starelid = restore_relid
609 AND staattnum = restore_attnum;
613 WHEN unique_violation THEN
614 RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
619 CREATE FUNCTION dbms_stats.restore_database_stats(
620 as_of_timestamp timestamp with time zone
621 ) RETURNS SETOF regclass AS
623 SELECT dbms_stats.restore(m.id, m.relid)
624 FROM (SELECT max(id) AS id, relid
625 FROM (SELECT r.id, r.relid
626 FROM pg_class c, dbms_stats.relation_stats_backup r,
627 dbms_stats.backup_history b
628 WHERE c.oid = r.relid
633 ORDER BY t1.relid) m;
637 CREATE FUNCTION dbms_stats.restore_schema_stats(
639 as_of_timestamp timestamp with time zone
640 ) RETURNS SETOF regclass AS
643 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
644 RAISE EXCEPTION 'schema "%" not found', $1;
646 IF dbms_stats.is_system_schema($1) THEN
647 RAISE EXCEPTION 'restoring statistics is inhibited for system schemas: "%"', $1;
651 SELECT dbms_stats.restore(m.id, m.relid)
652 FROM (SELECT max(id) AS id, relid
653 FROM (SELECT r.id, r.relid
654 FROM pg_class c, pg_namespace n,
655 dbms_stats.relation_stats_backup r,
656 dbms_stats.backup_history b
657 WHERE c.oid = r.relid
658 AND c.relnamespace = n.oid
664 ORDER BY t1.relid) m;
667 LANGUAGE plpgsql STRICT;
669 CREATE FUNCTION dbms_stats.restore_table_stats(
671 as_of_timestamp timestamp with time zone
672 ) RETURNS SETOF regclass AS
674 SELECT dbms_stats.restore(max(id), $1, NULL)
675 FROM dbms_stats.backup_history WHERE time <= $2
679 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_table_stats(dbms_stats.relname($1, $2)::regclass, $3)
689 CREATE FUNCTION dbms_stats.restore_column_stats(
692 as_of_timestamp timestamp with time zone
693 ) RETURNS SETOF regclass AS
695 SELECT dbms_stats.restore(max(id), $1, $2)
696 FROM dbms_stats.backup_history WHERE time <= $3
700 CREATE FUNCTION dbms_stats.restore_column_stats(
704 as_of_timestamp timestamp with time zone
705 ) RETURNS SETOF regclass AS
707 SELECT dbms_stats.restore(max(id), dbms_stats.relname($1, $2)::regclass, $3)
708 FROM dbms_stats.backup_history WHERE time <= $4
712 CREATE FUNCTION dbms_stats.restore_stats(
714 ) RETURNS SETOF regclass AS
717 restore_relid regclass;
719 restore_attname text;
720 restore_type regtype;
723 IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id = $1) THEN
724 RAISE EXCEPTION 'backup id % not found', $1;
727 /* Lock the backup */
728 PERFORM * from dbms_stats.relation_stats_backup b
729 WHERE id = $1 FOR SHARE;
731 /* Locking only _relation_stats_locked is sufficient */
732 LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
737 JOIN dbms_stats.relation_stats_backup b ON (c.oid = b.relid)
739 ORDER BY c.oid::regclass::text
741 UPDATE dbms_stats._relation_stats_locked r
744 relpages = b.relpages,
745 reltuples = b.reltuples,
746 curpages = b.curpages,
747 last_analyze = b.last_analyze,
748 last_autoanalyze = b.last_autoanalyze
749 FROM dbms_stats.relation_stats_backup b
750 WHERE r.relid = restore_relid
752 AND b.relid = restore_relid;
754 INSERT INTO dbms_stats._relation_stats_locked
762 FROM dbms_stats.relation_stats_backup b
764 AND b.relid = restore_relid;
766 RETURN NEXT restore_relid;
769 FOR restore_relid, restore_attnum, restore_type, cur_type IN
770 SELECT c.oid, a.attnum, b.statypid, a.atttypid
772 JOIN dbms_stats.column_stats_backup b ON (c.oid = b.starelid)
773 JOIN pg_attribute a ON (b.starelid = attrelid
774 AND b.staattnum = a.attnum)
777 IF restore_type <> cur_type THEN
778 SELECT attname INTO restore_attname
779 FROM pg_catalog.pg_attribute
780 WHERE attrelid = restore_relid
781 AND attnum = restore_attnum;
782 RAISE WARNING 'data type of column "%.%" is inconsistent between database(%) and backup (%). Skip.',
783 restore_relid, restore_attname, cur_type, restore_type;
785 DELETE FROM dbms_stats._column_stats_locked
786 WHERE starelid = restore_relid
787 AND staattnum = restore_attnum;
788 INSERT INTO dbms_stats._column_stats_locked
789 SELECT starelid, staattnum, stainherit,
790 stanullfrac, stawidth, stadistinct,
791 stakind1, stakind2, stakind3, stakind4,
792 staop1, staop2, staop3, staop4,
793 stanumbers1, stanumbers2, stanumbers3, stanumbers4,
794 stavalues1, stavalues2, stavalues3, stavalues4
795 FROM dbms_stats.column_stats_backup
797 AND starelid = restore_relid
798 AND staattnum = restore_attnum;
804 LANGUAGE plpgsql STRICT;
807 -- LOCK_STATS: Statistics lock functions
810 CREATE FUNCTION dbms_stats.lock(
813 ) RETURNS regclass AS
821 RAISE EXCEPTION 'relation required';
824 RETURN dbms_stats.lock($1);
826 SELECT relkind INTO lock_relkind FROM pg_catalog.pg_class WHERE oid = $1;
828 RAISE EXCEPTION 'relation "%" not found', $1;
830 IF NOT dbms_stats.is_target_relkind(lock_relkind) THEN
831 RAISE EXCEPTION '"%" must be a table or an index', $1;
833 IF EXISTS(SELECT * FROM pg_catalog.pg_index WHERE lock_relkind = 'i' AND indexrelid = $1 AND indexprs IS NULL) THEN
834 RAISE EXCEPTION '"%" must be an expression index', $1;
836 IF dbms_stats.is_system_catalog($1) THEN
837 RAISE EXCEPTION 'locking statistics is inhibited for system catalogs: "%"', $1;
839 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
840 WHERE a.attrelid = $1 AND a.attname = $2;
841 IF set_attnum IS NULL THEN
842 RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
846 * If we don't have per-table statistics, create new one which has NULL for
847 * every statistic value for column_stats_effective.
849 IF NOT EXISTS(SELECT * FROM dbms_stats._relation_stats_locked ru
850 WHERE ru.relid = $1 FOR SHARE) THEN
851 INSERT INTO dbms_stats._relation_stats_locked
852 SELECT $1, dbms_stats.relname(nspname, relname),
853 NULL, NULL, NULL, NULL, NULL
854 FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
855 WHERE c.relnamespace = n.oid
860 * Process for per-column statistics
863 SELECT stainherit, stanullfrac, stawidth, stadistinct,
864 stakind1, stakind2, stakind3, stakind4,
865 staop1, staop2, staop3, staop4,
866 stanumbers1, stanumbers2, stanumbers3, stanumbers4,
867 stavalues1, stavalues2, stavalues3, stavalues4
868 FROM dbms_stats.column_stats_effective
870 AND staattnum = set_attnum
872 UPDATE dbms_stats._column_stats_locked c
873 SET stanullfrac = r.stanullfrac,
874 stawidth = r.stawidth,
875 stadistinct = r.stadistinct,
876 stakind1 = r.stakind1,
877 stakind2 = r.stakind2,
878 stakind3 = r.stakind3,
879 stakind4 = r.stakind4,
884 stanumbers1 = r.stanumbers1,
885 stanumbers2 = r.stanumbers2,
886 stanumbers3 = r.stanumbers3,
887 stanumbers4 = r.stanumbers4,
888 stavalues1 = r.stavalues1,
889 stavalues2 = r.stavalues2,
890 stavalues3 = r.stavalues3,
891 stavalues4 = r.stavalues4
892 WHERE c.starelid = $1
893 AND c.staattnum = set_attnum
894 AND c.stainherit = r.stainherit;
897 INSERT INTO dbms_stats._column_stats_locked
923 /* If we don't have statistics at all, raise error. */
925 RAISE EXCEPTION 'no statistics available for column "%" of relation "%"', $2, $1::regclass;
930 WHEN unique_violation THEN
931 RAISE EXCEPTION 'This operation is canceled by simultaneous lock or restore operation on the same relation.';
936 CREATE FUNCTION dbms_stats.lock(relid regclass)
944 RAISE EXCEPTION 'relation required';
946 SELECT relkind INTO lock_relkind FROM pg_catalog.pg_class WHERE oid = $1;
948 RAISE EXCEPTION 'relation "%" not found', $1;
950 IF NOT dbms_stats.is_target_relkind(lock_relkind) THEN
951 RAISE EXCEPTION 'locking statistics is not allowed for relations with relkind "%": "%"', lock_relkind, $1
952 USING HINT = 'Only tables(r) and indexes(i) are lockable.';
954 IF dbms_stats.is_system_catalog($1) THEN
955 RAISE EXCEPTION 'locking statistics is not allowed for system catalogs: "%"', $1;
958 UPDATE dbms_stats._relation_stats_locked r
959 SET relname = dbms_stats.relname(nspname, c.relname),
960 relpages = v.relpages,
961 reltuples = v.reltuples,
962 curpages = v.curpages,
963 last_analyze = v.last_analyze,
964 last_autoanalyze = v.last_autoanalyze
965 FROM pg_catalog.pg_class c,
966 pg_catalog.pg_namespace n,
967 dbms_stats.relation_stats_effective v
970 AND c.relnamespace = n.oid
973 INSERT INTO dbms_stats._relation_stats_locked
974 SELECT $1, dbms_stats.relname(nspname, c.relname),
975 v.relpages, v.reltuples, v.curpages,
976 v.last_analyze, v.last_autoanalyze
977 FROM pg_catalog.pg_class c,
978 pg_catalog.pg_namespace n,
979 dbms_stats.relation_stats_effective v
981 AND c.relnamespace = n.oid
986 FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_index ind
987 ON c.oid = ind.indexrelid
990 AND ind.indexprs IS NULL) THEN
995 SELECT staattnum, stainherit, stanullfrac,
996 stawidth, stadistinct,
997 stakind1, stakind2, stakind3, stakind4,
998 staop1, staop2, staop3, staop4,
999 stanumbers1, stanumbers2, stanumbers3, stanumbers4,
1000 stavalues1, stavalues2, stavalues3, stavalues4
1001 FROM dbms_stats.column_stats_effective
1004 UPDATE dbms_stats._column_stats_locked c
1005 SET stanullfrac = i.stanullfrac,
1006 stawidth = i.stawidth,
1007 stadistinct = i.stadistinct,
1008 stakind1 = i.stakind1,
1009 stakind2 = i.stakind2,
1010 stakind3 = i.stakind3,
1011 stakind4 = i.stakind4,
1016 stanumbers1 = i.stanumbers1,
1017 stanumbers2 = i.stanumbers2,
1018 stanumbers3 = i.stanumbers3,
1019 stanumbers4 = i.stanumbers4,
1020 stavalues1 = i.stavalues1,
1021 stavalues2 = i.stavalues2,
1022 stavalues3 = i.stavalues3,
1023 stavalues4 = i.stavalues4
1024 WHERE c.starelid = $1
1025 AND c.staattnum = i.staattnum
1026 AND c.stainherit = i.stainherit;
1029 INSERT INTO dbms_stats._column_stats_locked
1057 WHEN unique_violation THEN
1058 RAISE EXCEPTION 'This operation is canceled by simultaneous lock operation on the same relation.';
1063 CREATE FUNCTION dbms_stats.lock_database_stats()
1064 RETURNS SETOF regclass AS
1066 SELECT dbms_stats.lock(c.oid)
1068 FROM pg_catalog.pg_class
1069 WHERE NOT dbms_stats.is_system_catalog(oid)
1070 AND dbms_stats.is_target_relkind(relkind)
1071 ORDER BY pg_class.oid
1076 CREATE FUNCTION dbms_stats.lock_schema_stats(
1078 ) RETURNS SETOF regclass AS
1081 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
1082 RAISE EXCEPTION 'schema "%" not found', $1;
1084 IF dbms_stats.is_system_schema($1) THEN
1085 RAISE EXCEPTION 'locking statistics is not allowed for system schemas: "%"', $1;
1089 SELECT dbms_stats.lock(cn.oid)
1091 FROM pg_class c, pg_namespace n
1092 WHERE c.relnamespace = n.oid
1093 AND dbms_stats.is_target_relkind(c.relkind)
1099 LANGUAGE plpgsql STRICT;
1101 CREATE FUNCTION dbms_stats.lock_table_stats(relid regclass)
1104 SELECT dbms_stats.lock($1)
1106 LANGUAGE sql STRICT;
1108 CREATE FUNCTION dbms_stats.lock_table_stats(
1111 ) RETURNS regclass AS
1113 SELECT dbms_stats.lock(dbms_stats.relname($1, $2)::regclass)
1115 LANGUAGE sql STRICT;
1117 CREATE FUNCTION dbms_stats.lock_column_stats(
1120 ) RETURNS regclass AS
1122 SELECT dbms_stats.lock($1, $2)
1124 LANGUAGE sql STRICT;
1126 CREATE FUNCTION dbms_stats.lock_column_stats(
1130 ) RETURNS regclass AS
1132 SELECT dbms_stats.lock(dbms_stats.relname($1, $2)::regclass, $3)
1134 LANGUAGE sql STRICT;
1137 -- UNLOCK_STATS: Statistics unlock functions
1140 CREATE FUNCTION dbms_stats.unlock(
1141 relid regclass DEFAULT NULL,
1142 attname text DEFAULT NULL
1143 ) RETURNS SETOF regclass AS
1149 IF $1 IS NULL AND $2 IS NOT NULL THEN
1150 RAISE EXCEPTION 'relation required';
1154 * Lock the target relation to prevent conflicting with stats lock/restore
1156 PERFORM * FROM dbms_stats._relation_stats_locked ru
1157 WHERE (ru.relid = $1 OR $1 IS NULL) FOR UPDATE;
1159 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
1160 WHERE a.attrelid = $1 AND a.attname = $2;
1161 IF $2 IS NOT NULL AND set_attnum IS NULL THEN
1162 RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
1165 DELETE FROM dbms_stats._column_stats_locked
1166 WHERE (starelid = $1 OR $1 IS NULL)
1167 AND (staattnum = set_attnum OR $2 IS NULL);
1169 IF $1 IS NOT NULL AND $2 IS NOT NULL THEN
1175 FROM dbms_stats._relation_stats_locked ru
1176 WHERE (ru.relid = $1 OR $1 IS NULL) AND ($2 IS NULL)
1179 DELETE FROM dbms_stats._relation_stats_locked ru
1180 WHERE ru.relid = unlock_id;
1181 RETURN NEXT unlock_id;
1187 CREATE FUNCTION dbms_stats.unlock_database_stats()
1188 RETURNS SETOF regclass AS
1193 LOCK dbms_stats._relation_stats_locked IN EXCLUSIVE MODE;
1197 FROM dbms_stats._relation_stats_locked
1200 DELETE FROM dbms_stats._relation_stats_locked
1201 WHERE relid = unlock_id;
1202 RETURN NEXT unlock_id;
1206 LANGUAGE plpgsql STRICT;
1208 CREATE FUNCTION dbms_stats.unlock_schema_stats(
1210 ) RETURNS SETOF regclass AS
1215 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
1216 RAISE EXCEPTION 'schema "%" not found', $1;
1218 IF dbms_stats.is_system_schema($1) THEN
1219 RAISE EXCEPTION 'unlocking statistics is not allowed for system schemas: "%"', $1;
1224 FROM dbms_stats._relation_stats_locked r, pg_class c, pg_namespace n
1226 AND c.relnamespace = n.oid
1231 DELETE FROM dbms_stats._relation_stats_locked
1232 WHERE relid = unlock_id;
1233 RETURN NEXT unlock_id;
1237 LANGUAGE plpgsql STRICT;
1239 CREATE FUNCTION dbms_stats.unlock_table_stats(relid regclass)
1240 RETURNS SETOF regclass AS
1242 DELETE FROM dbms_stats._relation_stats_locked
1244 RETURNING relid::regclass
1246 LANGUAGE sql STRICT;
1248 CREATE FUNCTION dbms_stats.unlock_table_stats(
1251 ) RETURNS SETOF regclass AS
1253 DELETE FROM dbms_stats._relation_stats_locked
1254 WHERE relid = dbms_stats.relname($1, $2)::regclass
1255 RETURNING relid::regclass
1257 LANGUAGE sql STRICT;
1259 CREATE FUNCTION dbms_stats.unlock_column_stats(
1262 ) RETURNS SETOF regclass AS
1267 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
1268 WHERE a.attrelid = $1 AND a.attname = $2;
1269 IF $2 IS NOT NULL AND set_attnum IS NULL THEN
1270 RAISE EXCEPTION 'column "%" not found in relation "%"', $2, $1;
1273 /* Lock the locked table stats */
1274 PERFORM * from dbms_stats.relation_stats_locked r
1275 WHERE r.relid = $1 FOR SHARE;
1277 DELETE FROM dbms_stats._column_stats_locked
1279 AND staattnum = set_attnum;
1285 LANGUAGE plpgsql STRICT;
1287 CREATE FUNCTION dbms_stats.unlock_column_stats(
1291 ) RETURNS SETOF regclass AS
1296 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
1297 WHERE a.attrelid = dbms_stats.relname($1, $2)::regclass
1299 IF $3 IS NOT NULL AND set_attnum IS NULL THEN
1300 RAISE EXCEPTION 'column "%" not found in relation "%.%"', $3, $1, $2;
1303 /* Lock the locked table stats */
1304 PERFORM * from dbms_stats.relation_stats_locked r
1305 WHERE relid = dbms_stats.relname($1, $2)::regclass FOR SHARE;
1307 DELETE FROM dbms_stats._column_stats_locked
1308 WHERE starelid = dbms_stats.relname($1, $2)::regclass
1309 AND staattnum = set_attnum;
1312 SELECT dbms_stats.relname($1, $2)::regclass;
1315 LANGUAGE plpgsql STRICT;
1318 -- IMPORT_STATS: Statistics import functions
1321 CREATE FUNCTION dbms_stats.import(
1322 nspname text DEFAULT NULL,
1323 relid regclass DEFAULT NULL,
1324 attname text DEFAULT NULL,
1325 src text DEFAULT NULL
1327 'MODULE_PATHNAME', 'dbms_stats_import'
1330 CREATE FUNCTION dbms_stats.import_database_stats(src text)
1333 SELECT dbms_stats.import(NULL, NULL, NULL, $1)
1337 CREATE FUNCTION dbms_stats.import_schema_stats(
1342 SELECT dbms_stats.import($1, NULL, NULL, $2)
1346 CREATE FUNCTION dbms_stats.import_table_stats(
1351 SELECT dbms_stats.import(NULL, $1, NULL, $2)
1355 CREATE FUNCTION dbms_stats.import_table_stats(
1361 SELECT dbms_stats.import(NULL, dbms_stats.relname($1, $2)::regclass, NULL, $3)
1365 CREATE FUNCTION dbms_stats.import_column_stats(
1371 SELECT dbms_stats.import(NULL, $1, $2, $3)
1375 CREATE FUNCTION dbms_stats.import_column_stats(
1382 SELECT dbms_stats.import(NULL, dbms_stats.relname($1, $2)::regclass, $3, $4)
1387 -- PURGE_STATS: Statistics purge function
1389 CREATE FUNCTION dbms_stats.purge_stats(
1391 force bool DEFAULT false
1392 ) RETURNS SETOF dbms_stats.backup_history AS
1396 todelete dbms_stats.backup_history;
1399 RAISE EXCEPTION 'backup id required';
1402 RAISE EXCEPTION 'NULL is not allowed as the second parameter';
1405 IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history
1406 WHERE id = $1 FOR UPDATE) THEN
1407 RAISE EXCEPTION 'backup id % not found', $1;
1409 IF NOT $2 AND NOT EXISTS(SELECT *
1410 FROM dbms_stats.backup_history
1413 RAISE WARNING 'no database-wide backup will remain after purge'
1414 USING HINT = 'Give true for second parameter to purge forcibly.';
1419 SELECT * FROM dbms_stats.backup_history
1421 ORDER BY id FOR UPDATE
1423 DELETE FROM dbms_stats.backup_history
1424 WHERE id = todelete.id;
1425 RETURN NEXT todelete;
1432 -- CLEAN_STATS: Clean orphan dummy statistics
1434 CREATE FUNCTION dbms_stats.clean_up_stats() RETURNS SETOF text AS
1442 -- We don't have to check that table-level dummy statistics of the table
1443 -- exists here, because the foreign key constraints defined on column-level
1444 -- dummy static table ensures that.
1445 FOR clean_rel_col, clean_relid, clean_attnum, clean_inherit IN
1446 SELECT r.relname || ', ' || v.staattnum::text,
1447 v.starelid, v.staattnum, v.stainherit
1448 FROM dbms_stats._column_stats_locked v
1449 JOIN dbms_stats._relation_stats_locked r ON (v.starelid = r.relid)
1453 WHERE a.attrelid = v.starelid
1454 AND a.attnum = v.staattnum
1455 AND a.attisdropped = false
1459 DELETE FROM dbms_stats._column_stats_locked
1460 WHERE starelid = clean_relid
1461 AND staattnum = clean_attnum
1462 AND stainherit = clean_inherit;
1463 RETURN NEXT clean_rel_col;
1467 DELETE FROM dbms_stats._relation_stats_locked r
1471 WHERE c.oid = r.relid)
1472 RETURNING relname || ',';
1478 GRANT USAGE ON schema dbms_stats TO PUBLIC;