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,
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')$$
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'
166 -- Statistics views for internal use
167 -- These views are used to merge authentic stats and dummy stats by hook
168 -- function, so we don't grant SELECT privilege to PUBLIC.
171 CREATE VIEW dbms_stats.relation_stats_effective AS
174 dbms_stats.relname(nspname, c.relname) AS relname,
175 COALESCE(v.relpages, c.relpages) AS relpages,
176 COALESCE(v.reltuples, c.reltuples) AS reltuples,
177 COALESCE(v.relallvisible, c.relallvisible) AS relallvisible,
179 (pg_relation_size(c.oid) / current_setting('block_size')::int4)::int4)
181 COALESCE(v.last_analyze,
182 pg_catalog.pg_stat_get_last_analyze_time(c.oid))
184 COALESCE(v.last_autoanalyze,
185 pg_catalog.pg_stat_get_last_autoanalyze_time(c.oid))
187 FROM pg_catalog.pg_class c
188 JOIN pg_catalog.pg_namespace n
189 ON c.relnamespace = n.oid
190 LEFT JOIN dbms_stats._relation_stats_locked v
192 WHERE dbms_stats.is_target_relkind(c.relkind)
193 AND NOT dbms_stats.is_system_schema(nspname);
195 CREATE VIEW dbms_stats.column_stats_effective AS
197 SELECT (dbms_stats.merge(v, s)).*
198 FROM pg_catalog.pg_statistic s
199 FULL JOIN dbms_stats._column_stats_locked v
200 USING (starelid, staattnum, stainherit)
201 WHERE NOT dbms_stats.is_system_catalog(starelid)
205 WHERE a.attrelid = starelid
206 AND a.attnum = staattnum
207 AND a.attisdropped = false
210 WHERE starelid IS NOT NULL;
213 -- Statistics views for user use (including non-superusers)
214 -- These views allow users to see dummy statistics about tables which the
215 -- user has SELECT privilege.
218 CREATE VIEW dbms_stats.relation_stats_locked
220 FROM dbms_stats._relation_stats_locked;
222 GRANT SELECT ON dbms_stats.relation_stats_locked TO PUBLIC;
224 CREATE VIEW dbms_stats.column_stats_locked
226 FROM dbms_stats._column_stats_locked
227 WHERE has_column_privilege(starelid, staattnum, 'SELECT');
229 GRANT SELECT ON dbms_stats.column_stats_locked TO PUBLIC;
232 -- Note: This view is copied from pg_stats in
233 -- src/backend/catalog/system_views.sql in core source tree of version
234 -- 9.2, and customized for pg_dbms_stats. Changes from orignal one are:
235 -- - rename from pg_stats to dbms_stats.stats by a view name.
236 -- - changed the table name from pg_statistic to dbms_stats.column_stats_effective.
238 CREATE VIEW dbms_stats.stats AS
240 nspname AS schemaname,
241 relname AS tablename,
243 stainherit AS inherited,
244 stanullfrac AS null_frac,
245 stawidth AS avg_width,
246 stadistinct AS n_distinct,
248 WHEN stakind1 = 1 THEN stavalues1
249 WHEN stakind2 = 1 THEN stavalues2
250 WHEN stakind3 = 1 THEN stavalues3
251 WHEN stakind4 = 1 THEN stavalues4
252 WHEN stakind5 = 1 THEN stavalues5
253 END AS most_common_vals,
255 WHEN stakind1 = 1 THEN stanumbers1
256 WHEN stakind2 = 1 THEN stanumbers2
257 WHEN stakind3 = 1 THEN stanumbers3
258 WHEN stakind4 = 1 THEN stanumbers4
259 WHEN stakind5 = 1 THEN stanumbers5
260 END AS most_common_freqs,
262 WHEN stakind1 = 2 THEN stavalues1
263 WHEN stakind2 = 2 THEN stavalues2
264 WHEN stakind3 = 2 THEN stavalues3
265 WHEN stakind4 = 2 THEN stavalues4
266 WHEN stakind5 = 2 THEN stavalues5
267 END AS histogram_bounds,
269 WHEN stakind1 = 3 THEN stanumbers1[1]
270 WHEN stakind2 = 3 THEN stanumbers2[1]
271 WHEN stakind3 = 3 THEN stanumbers3[1]
272 WHEN stakind4 = 3 THEN stanumbers4[1]
273 WHEN stakind5 = 3 THEN stanumbers5[1]
276 WHEN stakind1 = 4 THEN stavalues1
277 WHEN stakind2 = 4 THEN stavalues2
278 WHEN stakind3 = 4 THEN stavalues3
279 WHEN stakind4 = 4 THEN stavalues4
280 WHEN stakind5 = 4 THEN stavalues5
281 END AS most_common_elems,
283 WHEN stakind1 = 4 THEN stanumbers1
284 WHEN stakind2 = 4 THEN stanumbers2
285 WHEN stakind3 = 4 THEN stanumbers3
286 WHEN stakind4 = 4 THEN stanumbers4
287 WHEN stakind5 = 4 THEN stanumbers5
288 END AS most_common_elem_freqs,
290 WHEN stakind1 = 5 THEN stanumbers1
291 WHEN stakind2 = 5 THEN stanumbers2
292 WHEN stakind3 = 5 THEN stanumbers3
293 WHEN stakind4 = 5 THEN stanumbers4
294 WHEN stakind5 = 5 THEN stanumbers5
295 END AS elem_count_histogram
296 FROM dbms_stats.column_stats_effective s JOIN pg_class c ON (c.oid = s.starelid)
297 JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
298 LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
299 WHERE NOT attisdropped AND has_column_privilege(c.oid, a.attnum, 'select');
301 GRANT SELECT ON dbms_stats.stats TO PUBLIC;
307 CREATE FUNCTION dbms_stats.invalidate_relation_cache()
309 'MODULE_PATHNAME', 'dbms_stats_invalidate_relation_cache'
312 -- Invalidate cached plans when dbms_stats._relation_stats_locked is modified.
313 CREATE TRIGGER invalidate_relation_cache
314 BEFORE INSERT OR DELETE OR UPDATE
315 ON dbms_stats._relation_stats_locked
316 FOR EACH ROW EXECUTE PROCEDURE dbms_stats.invalidate_relation_cache();
318 CREATE FUNCTION dbms_stats.invalidate_column_cache()
320 'MODULE_PATHNAME', 'dbms_stats_invalidate_column_cache'
323 -- Invalidate cached plans when dbms_stats._column_stats_locked is modified.
324 CREATE TRIGGER invalidate_column_cache
325 BEFORE INSERT OR DELETE OR UPDATE
326 ON dbms_stats._column_stats_locked
327 FOR EACH ROW EXECUTE PROCEDURE dbms_stats.invalidate_column_cache();
330 -- BACKUP_STATS: Statistic backup functions
333 CREATE FUNCTION dbms_stats.backup(
339 INSERT INTO dbms_stats.relation_stats_backup
340 SELECT $1, v.relid, v.relname, v.relpages, v.reltuples, v.relallvisible,
341 v.curpages, v.last_analyze, v.last_autoanalyze
342 FROM pg_catalog.pg_class c,
343 dbms_stats.relation_stats_effective v
344 WHERE c.oid = v.relid
345 AND dbms_stats.is_target_relkind(relkind)
346 AND NOT dbms_stats.is_system_catalog(v.relid)
347 AND (v.relid = $2 OR $2 IS NULL);
349 INSERT INTO dbms_stats.column_stats_backup
350 SELECT $1, atttypid, s.*
351 FROM pg_catalog.pg_class c,
352 dbms_stats.column_stats_effective s,
353 pg_catalog.pg_attribute a
354 WHERE c.oid = starelid
355 AND starelid = attrelid
356 AND staattnum = attnum
357 AND dbms_stats.is_target_relkind(relkind)
358 AND NOT dbms_stats.is_system_catalog(c.oid)
359 AND ($2 IS NULL OR starelid = $2)
360 AND ($3 IS NULL OR staattnum = $3);
366 CREATE FUNCTION dbms_stats.backup(
367 relid regclass DEFAULT NULL,
368 attname text DEFAULT NULL,
369 comment text DEFAULT NULL
374 backup_relkind "char";
378 IF $1 IS NULL AND $2 IS NOT NULL THEN
379 RAISE EXCEPTION 'relation is required';
381 IF $1 IS NOT NULL THEN
382 SELECT relkind INTO backup_relkind
383 FROM pg_catalog.pg_class WHERE oid = $1;
385 RAISE EXCEPTION 'relation "%" does not exist', $1;
387 IF NOT dbms_stats.is_target_relkind(backup_relkind) THEN
388 RAISE EXCEPTION 'can not backup statistics of "%" with relkind "%"',
390 USING HINT = 'only tables and indexes are supported';
392 IF dbms_stats.is_system_catalog($1) THEN
393 RAISE EXCEPTION 'can not backup statistics of system catalog "%"', $1;
395 IF $2 IS NOT NULL THEN
396 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
397 WHERE a.attrelid = $1 AND a.attname = $2;
398 IF set_attnum IS NULL THEN
399 RAISE EXCEPTION 'column "%" of "%" does not exist', $2, $1;
401 IF NOT EXISTS(SELECT * FROM dbms_stats.column_stats_effective WHERE starelid = $1 AND staattnum = set_attnum) THEN
402 RAISE EXCEPTION 'no statistic for column "%" of "%" exists', $2, $1;
412 INSERT INTO dbms_stats.backup_history(time, unit, comment)
413 VALUES (current_timestamp, unit_type, $3)
414 RETURNING dbms_stats.backup(id, $1, set_attnum) INTO backup_id;
420 CREATE FUNCTION dbms_stats.backup_database_stats(
424 SELECT dbms_stats.backup(NULL, NULL, $1)
428 CREATE FUNCTION dbms_stats.backup_schema_stats(
436 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
437 RAISE EXCEPTION 'schema "%" does not exist', $1;
439 IF dbms_stats.is_system_schema($1) THEN
440 RAISE EXCEPTION 'can not backup statistics of relation in system schema "%"', $1;
443 INSERT INTO dbms_stats.backup_history(time, unit, comment)
444 VALUES (current_timestamp, 's', comment)
445 RETURNING id INTO backup_id;
447 PERFORM dbms_stats.backup(backup_id, cn.oid, NULL)
449 FROM pg_catalog.pg_class c,
450 pg_catalog.pg_namespace n
451 WHERE n.nspname = schemaname
452 AND c.relnamespace = n.oid
453 AND dbms_stats.is_target_relkind(c.relkind)
462 CREATE FUNCTION dbms_stats.backup_table_stats(
467 SELECT dbms_stats.backup($1, NULL, $2)
471 CREATE FUNCTION dbms_stats.backup_table_stats(
477 SELECT dbms_stats.backup(dbms_stats.relname($1, $2)::regclass, NULL, $3)
481 CREATE FUNCTION dbms_stats.backup_column_stats(
487 SELECT dbms_stats.backup($1, $2, $3)
491 CREATE FUNCTION dbms_stats.backup_column_stats(
498 SELECT dbms_stats.backup(dbms_stats.relname($1, $2)::regclass, $3, $4)
503 -- RESTORE_STATS: Statistic restore functions
505 CREATE FUNCTION dbms_stats.restore(
507 relid regclass DEFAULT NULL,
508 attname text DEFAULT NULL
509 ) RETURNS SETOF regclass AS
513 restore_relid regclass;
516 restore_attname text;
517 restore_type regtype;
521 RAISE EXCEPTION 'backup id is required';
523 IF $2 IS NULL AND $3 IS NOT NULL THEN
524 RAISE EXCEPTION 'relation is required';
526 IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id <= $1) THEN
527 RAISE EXCEPTION 'backup id % does not exist', $1;
529 IF $2 IS NOT NULL THEN
530 IF NOT EXISTS(SELECT * FROM pg_catalog.pg_class WHERE oid = $2) THEN
531 RAISE EXCEPTION 'relation "%" does not exist', $2;
533 IF NOT EXISTS(SELECT * FROM dbms_stats.relation_stats_backup b
534 WHERE b.id <= $1 AND b.relid = $2) THEN
535 RAISE EXCEPTION 'relation "%" does not exist in previous backup', $2;
537 IF $3 IS NOT NULL THEN
538 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
539 WHERE a.attrelid = $2 AND a.attname = $3;
540 IF set_attnum IS NULL THEN
541 RAISE EXCEPTION 'column "%" of "%" does not exist', $3, $2;
543 IF NOT EXISTS(SELECT * FROM dbms_stats.column_stats_backup WHERE id <= $1 AND starelid = $2 AND staattnum = set_attnum) THEN
544 RAISE EXCEPTION 'column "%" of "%" does not exist in previous backup',$3, $2;
549 LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
550 LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
552 FOR restore_id, restore_relid IN
553 SELECT max(b.id), c.oid
554 FROM pg_class c, dbms_stats.relation_stats_backup b
555 WHERE (c.oid = $2 OR $2 IS NULL)
557 AND dbms_stats.is_target_relkind(c.relkind)
558 AND NOT dbms_stats.is_system_catalog(c.oid)
561 ORDER BY c.oid::regclass::text
563 UPDATE dbms_stats._relation_stats_locked r
566 relpages = b.relpages,
567 reltuples = b.reltuples,
568 relallvisible = b.relallvisible,
569 curpages = b.curpages,
570 last_analyze = b.last_analyze,
571 last_autoanalyze = b.last_autoanalyze
572 FROM dbms_stats.relation_stats_backup b
573 WHERE r.relid = restore_relid
574 AND b.id = restore_id
575 AND b.relid = restore_relid;
577 INSERT INTO dbms_stats._relation_stats_locked
586 FROM dbms_stats.relation_stats_backup b
587 WHERE b.id = restore_id
588 AND b.relid = restore_relid;
590 RETURN NEXT restore_relid;
593 FOR restore_id, restore_relid, restore_attnum, restore_type, cur_type IN
594 SELECT t.id, t.oid, t.attnum, b.statypid, a.atttypid
596 dbms_stats.column_stats_backup b,
597 (SELECT max(b.id) AS id, c.oid, a.attnum
598 FROM pg_class c, pg_attribute a, dbms_stats.column_stats_backup b
599 WHERE (c.oid = $2 OR $2 IS NULL)
600 AND c.oid = a.attrelid
601 AND c.oid = b.starelid
602 AND (a.attnum = set_attnum OR set_attnum IS NULL)
603 AND a.attnum = b.staattnum
604 AND NOT a.attisdropped
605 AND dbms_stats.is_target_relkind(c.relkind)
607 GROUP BY c.oid, a.attnum) t
608 WHERE a.attrelid = t.oid
609 AND a.attnum = t.attnum
611 AND b.starelid = t.oid
612 AND b.staattnum = t.attnum
614 IF restore_type <> cur_type THEN
615 SELECT a.attname INTO restore_attname
616 FROM pg_catalog.pg_attribute a
617 WHERE a.attrelid = restore_relid
618 AND a.attnum = restore_attnum;
619 RAISE WARNING 'skip "%.%" because of type mismatch: "%" in backup and "%" in database',
620 restore_relid, restore_attname, restore_type, cur_type;
622 DELETE FROM dbms_stats._column_stats_locked
623 WHERE starelid = restore_relid
624 AND staattnum = restore_attnum;
625 INSERT INTO dbms_stats._column_stats_locked
626 SELECT starelid, staattnum, stainherit,
627 stanullfrac, stawidth, stadistinct,
628 stakind1, stakind2, stakind3, stakind4, stakind5,
629 staop1, staop2, staop3, staop4, staop5,
630 stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
631 stavalues1, stavalues2, stavalues3, stavalues4, stavalues5
632 FROM dbms_stats.column_stats_backup
633 WHERE id = restore_id
634 AND starelid = restore_relid
635 AND staattnum = restore_attnum;
642 CREATE FUNCTION dbms_stats.restore_database_stats(
643 as_of_timestamp timestamp with time zone
644 ) RETURNS SETOF regclass AS
646 SELECT dbms_stats.restore(m.id, m.relid)
647 FROM (SELECT max(r.id) AS id, r.relid
648 FROM pg_class c, dbms_stats.relation_stats_backup r,
649 dbms_stats.backup_history b
650 WHERE c.oid = r.relid
659 CREATE FUNCTION dbms_stats.restore_schema_stats(
661 as_of_timestamp timestamp with time zone
662 ) RETURNS SETOF regclass AS
665 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
666 RAISE EXCEPTION 'schema "%" does not exist', $1;
668 IF dbms_stats.is_system_schema($1) THEN
669 RAISE EXCEPTION 'can not restore statistics of relation in system schema "%"', $1;
673 SELECT dbms_stats.restore(m.id, m.relid)
674 FROM (SELECT max(r.id) AS id, r.relid
675 FROM pg_class c, pg_namespace n,
676 dbms_stats.relation_stats_backup r,
677 dbms_stats.backup_history b
678 WHERE c.oid = r.relid
679 AND c.relnamespace = n.oid
688 LANGUAGE plpgsql STRICT;
690 CREATE FUNCTION dbms_stats.restore_table_stats(
692 as_of_timestamp timestamp with time zone
693 ) RETURNS SETOF regclass AS
695 SELECT dbms_stats.restore(max(id), $1, NULL)
696 FROM dbms_stats.backup_history WHERE time <= $2
700 CREATE FUNCTION dbms_stats.restore_table_stats(
703 as_of_timestamp timestamp with time zone
704 ) RETURNS SETOF regclass AS
706 SELECT dbms_stats.restore_table_stats(dbms_stats.relname($1, $2)::regclass, $3)
710 CREATE FUNCTION dbms_stats.restore_column_stats(
713 as_of_timestamp timestamp with time zone
714 ) RETURNS SETOF regclass AS
716 SELECT dbms_stats.restore(max(id), $1, $2)
717 FROM dbms_stats.backup_history WHERE time <= $3
721 CREATE FUNCTION dbms_stats.restore_column_stats(
725 as_of_timestamp timestamp with time zone
726 ) RETURNS SETOF regclass AS
728 SELECT dbms_stats.restore(max(id), dbms_stats.relname($1, $2)::regclass, $3)
729 FROM dbms_stats.backup_history WHERE time <= $4
733 CREATE FUNCTION dbms_stats.restore_stats(
735 ) RETURNS SETOF regclass AS
738 restore_relid regclass;
740 restore_attname text;
741 restore_type regtype;
744 IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id = $1) THEN
745 RAISE EXCEPTION 'backup id % does not exist', $1;
748 LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
749 LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
754 JOIN dbms_stats.relation_stats_backup b ON (c.oid = b.relid)
756 ORDER BY c.oid::regclass::text
758 UPDATE dbms_stats._relation_stats_locked r
761 relpages = b.relpages,
762 reltuples = b.reltuples,
763 relallvisible = b.relallvisible,
764 curpages = b.curpages,
765 last_analyze = b.last_analyze,
766 last_autoanalyze = b.last_autoanalyze
767 FROM dbms_stats.relation_stats_backup b
768 WHERE r.relid = restore_relid
770 AND b.relid = restore_relid;
772 INSERT INTO dbms_stats._relation_stats_locked
781 FROM dbms_stats.relation_stats_backup b
783 AND b.relid = restore_relid;
785 RETURN NEXT restore_relid;
788 FOR restore_relid, restore_attnum, restore_type, cur_type IN
789 SELECT c.oid, a.attnum, b.statypid, a.atttypid
791 JOIN dbms_stats.column_stats_backup b ON (c.oid = b.starelid)
792 JOIN pg_attribute a ON (b.starelid = attrelid
793 AND b.staattnum = a.attnum)
796 IF restore_type <> cur_type THEN
797 SELECT attname INTO restore_attname
798 FROM pg_catalog.pg_attribute
799 WHERE attrelid = restore_relid
800 AND attnum = restore_attnum;
801 RAISE WARNING 'skip "%.%" because of type mismatch: "%" in backup and "%" in database',
802 restore_relid, restore_attname, restore_type, cur_type;
804 DELETE FROM dbms_stats._column_stats_locked
805 WHERE starelid = restore_relid
806 AND staattnum = restore_attnum;
807 INSERT INTO dbms_stats._column_stats_locked
808 SELECT starelid, staattnum, stainherit,
809 stanullfrac, stawidth, stadistinct,
810 stakind1, stakind2, stakind3, stakind4, stakind5,
811 staop1, staop2, staop3, staop4, staop5,
812 stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
813 stavalues1, stavalues2, stavalues3, stavalues4, stavalues5
814 FROM dbms_stats.column_stats_backup
816 AND starelid = restore_relid
817 AND staattnum = restore_attnum;
823 LANGUAGE plpgsql STRICT;
826 -- LOCK_STATS: Statistic lock functions
829 CREATE FUNCTION dbms_stats.lock(
832 ) RETURNS regclass AS
840 RAISE EXCEPTION 'relation is required';
843 RETURN dbms_stats.lock($1);
845 SELECT relkind INTO lock_relkind FROM pg_catalog.pg_class WHERE oid = $1;
847 RAISE EXCEPTION 'relation "%" does not exist', $1;
849 IF NOT dbms_stats.is_target_relkind(lock_relkind) THEN
850 RAISE EXCEPTION '"%" is not a table nor an index', $1;
852 IF EXISTS(SELECT * FROM pg_catalog.pg_index WHERE lock_relkind = 'i' AND indexrelid = $1 AND indexprs IS NULL) THEN
853 RAISE EXCEPTION '"%" is not an indexes on expressions', $1;
855 IF dbms_stats.is_system_catalog($1) THEN
856 RAISE EXCEPTION 'can not lock statistics of system catalog "%"', $1;
858 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
859 WHERE a.attrelid = $1 AND a.attname = $2;
860 IF set_attnum IS NULL THEN
861 RAISE EXCEPTION 'column "%" of "%" does not exist', $2, $1;
864 LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
865 LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
868 * If we don't have per-table statistics, create new one which has NULL for
869 * every statistic column_stats_effective.
871 IF NOT EXISTS(SELECT * FROM dbms_stats._relation_stats_locked ru
872 WHERE ru.relid = $1) THEN
873 INSERT INTO dbms_stats._relation_stats_locked
874 SELECT $1, dbms_stats.relname(nspname, relname),
875 NULL, NULL, NULL, NULL, NULL
876 FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
877 WHERE c.relnamespace = n.oid
882 * Process for per-column statistics
885 SELECT stainherit, stanullfrac, stawidth, stadistinct,
886 stakind1, stakind2, stakind3, stakind4, stakind5,
887 staop1, staop2, staop3, staop4, staop5,
888 stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
889 stavalues1, stavalues2, stavalues3, stavalues4, stavalues5
890 FROM dbms_stats.column_stats_effective
892 AND staattnum = set_attnum
894 UPDATE dbms_stats._column_stats_locked c
895 SET stanullfrac = r.stanullfrac,
896 stawidth = r.stawidth,
897 stadistinct = r.stadistinct,
898 stakind1 = r.stakind1,
899 stakind2 = r.stakind2,
900 stakind3 = r.stakind3,
901 stakind4 = r.stakind4,
902 stakind5 = r.stakind5,
908 stanumbers1 = r.stanumbers1,
909 stanumbers2 = r.stanumbers2,
910 stanumbers3 = r.stanumbers3,
911 stanumbers4 = r.stanumbers4,
912 stanumbers5 = r.stanumbers5,
913 stavalues1 = r.stavalues1,
914 stavalues2 = r.stavalues2,
915 stavalues3 = r.stavalues3,
916 stavalues4 = r.stavalues4,
917 stavalues5 = r.stavalues5
918 WHERE c.starelid = $1
919 AND c.staattnum = set_attnum
920 AND c.stainherit = r.stainherit;
923 INSERT INTO dbms_stats._column_stats_locked
953 /* If we don't have statistic at all, raise error. */
955 RAISE EXCEPTION 'no statistic for column "%" of "%" exists', $2, $1::regclass;
963 CREATE FUNCTION dbms_stats.lock(relid regclass)
971 RAISE EXCEPTION 'relation is required';
973 SELECT relkind INTO lock_relkind FROM pg_catalog.pg_class WHERE oid = $1;
975 RAISE EXCEPTION 'relation "%" does not exist', $1;
977 IF NOT dbms_stats.is_target_relkind(lock_relkind) THEN
978 RAISE EXCEPTION 'can not lock statistics of "%" with relkind "%"', $1, lock_relkind
979 USING HINT = 'only tables and indexes are supported';
981 IF dbms_stats.is_system_catalog($1) THEN
982 RAISE EXCEPTION 'can not lock statistics of system catalog "%"', $1;
985 LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
986 LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
988 UPDATE dbms_stats._relation_stats_locked r
989 SET relname = dbms_stats.relname(nspname, c.relname),
990 relpages = v.relpages,
991 reltuples = v.reltuples,
992 relallvisible = v.relallvisible,
993 curpages = v.curpages,
994 last_analyze = v.last_analyze,
995 last_autoanalyze = v.last_autoanalyze
996 FROM pg_catalog.pg_class c,
997 pg_catalog.pg_namespace n,
998 dbms_stats.relation_stats_effective v
1001 AND c.relnamespace = n.oid
1004 INSERT INTO dbms_stats._relation_stats_locked
1005 SELECT $1, dbms_stats.relname(nspname, c.relname),
1006 v.relpages, v.reltuples, v.relallvisible, v.curpages,
1007 v.last_analyze, v.last_autoanalyze
1008 FROM pg_catalog.pg_class c,
1009 pg_catalog.pg_namespace n,
1010 dbms_stats.relation_stats_effective v
1012 AND c.relnamespace = n.oid
1017 FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_index ind
1018 ON c.oid = ind.indexrelid
1021 AND ind.indexprs IS NULL) THEN
1026 SELECT staattnum, stainherit, stanullfrac,
1027 stawidth, stadistinct,
1028 stakind1, stakind2, stakind3, stakind4, stakind5,
1029 staop1, staop2, staop3, staop4, staop5,
1030 stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
1031 stavalues1, stavalues2, stavalues3, stavalues4, stavalues5
1032 FROM dbms_stats.column_stats_effective
1035 UPDATE dbms_stats._column_stats_locked c
1036 SET stanullfrac = i.stanullfrac,
1037 stawidth = i.stawidth,
1038 stadistinct = i.stadistinct,
1039 stakind1 = i.stakind1,
1040 stakind2 = i.stakind2,
1041 stakind3 = i.stakind3,
1042 stakind4 = i.stakind4,
1043 stakind5 = i.stakind5,
1049 stanumbers1 = i.stanumbers1,
1050 stanumbers2 = i.stanumbers2,
1051 stanumbers3 = i.stanumbers3,
1052 stanumbers4 = i.stanumbers4,
1053 stanumbers5 = i.stanumbers5,
1054 stavalues1 = i.stavalues1,
1055 stavalues2 = i.stavalues2,
1056 stavalues3 = i.stavalues3,
1057 stavalues4 = i.stavalues4,
1058 stavalues5 = i.stavalues5
1059 WHERE c.starelid = $1
1060 AND c.staattnum = i.staattnum
1061 AND c.stainherit = i.stainherit;
1064 INSERT INTO dbms_stats._column_stats_locked
1099 CREATE FUNCTION dbms_stats.lock_database_stats()
1100 RETURNS SETOF regclass AS
1102 SELECT dbms_stats.lock(c.oid)
1104 FROM pg_catalog.pg_class
1105 WHERE NOT dbms_stats.is_system_catalog(oid)
1106 AND dbms_stats.is_target_relkind(relkind)
1107 ORDER BY pg_class.oid
1112 CREATE FUNCTION dbms_stats.lock_schema_stats(
1114 ) RETURNS SETOF regclass AS
1117 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
1118 RAISE EXCEPTION 'schema "%" does not exist', $1;
1120 IF dbms_stats.is_system_schema($1) THEN
1121 RAISE EXCEPTION 'can not lock statistics of relation in system schema "%"', $1;
1125 SELECT dbms_stats.lock(cn.oid)
1127 FROM pg_class c, pg_namespace n
1128 WHERE c.relnamespace = n.oid
1129 AND dbms_stats.is_target_relkind(c.relkind)
1135 LANGUAGE plpgsql STRICT;
1137 CREATE FUNCTION dbms_stats.lock_table_stats(relid regclass)
1140 SELECT dbms_stats.lock($1)
1142 LANGUAGE sql STRICT;
1144 CREATE FUNCTION dbms_stats.lock_table_stats(
1147 ) RETURNS regclass AS
1149 SELECT dbms_stats.lock(dbms_stats.relname($1, $2)::regclass)
1151 LANGUAGE sql STRICT;
1153 CREATE FUNCTION dbms_stats.lock_column_stats(
1156 ) RETURNS regclass AS
1158 SELECT dbms_stats.lock($1, $2)
1160 LANGUAGE sql STRICT;
1162 CREATE FUNCTION dbms_stats.lock_column_stats(
1166 ) RETURNS regclass AS
1168 SELECT dbms_stats.lock(dbms_stats.relname($1, $2)::regclass, $3)
1170 LANGUAGE sql STRICT;
1173 -- UNLOCK_STATS: Statistic unlock functions
1176 CREATE FUNCTION dbms_stats.unlock(
1177 relid regclass DEFAULT NULL,
1178 attname text DEFAULT NULL
1179 ) RETURNS SETOF regclass AS
1185 IF $1 IS NULL AND $2 IS NOT NULL THEN
1186 RAISE EXCEPTION 'relation is required';
1188 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
1189 WHERE a.attrelid = $1 AND a.attname = $2;
1190 IF $2 IS NOT NULL AND set_attnum IS NULL THEN
1191 RAISE EXCEPTION 'column "%" of "%" does not exist', $2, $1;
1194 LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1195 LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1197 DELETE FROM dbms_stats._column_stats_locked
1198 WHERE (starelid = $1 OR $1 IS NULL)
1199 AND (staattnum = set_attnum OR $2 IS NULL);
1201 IF $1 IS NOT NULL AND $2 IS NOT NULL THEN
1207 FROM dbms_stats._relation_stats_locked ru
1208 WHERE (ru.relid = $1 OR $1 IS NULL) AND ($2 IS NULL)
1211 DELETE FROM dbms_stats._relation_stats_locked ru
1212 WHERE ru.relid = unlock_id;
1213 RETURN NEXT unlock_id;
1219 CREATE FUNCTION dbms_stats.unlock_database_stats()
1220 RETURNS SETOF regclass AS
1225 LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1226 LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1230 FROM dbms_stats._relation_stats_locked
1233 DELETE FROM dbms_stats._relation_stats_locked
1234 WHERE relid = unlock_id;
1235 RETURN NEXT unlock_id;
1239 LANGUAGE plpgsql STRICT;
1241 CREATE FUNCTION dbms_stats.unlock_schema_stats(
1243 ) RETURNS SETOF regclass AS
1248 IF NOT EXISTS(SELECT * FROM pg_namespace WHERE nspname = $1) THEN
1249 RAISE EXCEPTION 'schema "%" does not exist', $1;
1251 IF dbms_stats.is_system_schema($1) THEN
1252 RAISE EXCEPTION 'can not unlock statistics of relation in system schema "%"', $1;
1255 LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1256 LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1260 FROM dbms_stats._relation_stats_locked, pg_class c, pg_namespace n
1262 AND c.relnamespace = n.oid
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_table_stats(relid regclass)
1275 RETURNS SETOF regclass AS
1278 LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1279 LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1281 DELETE FROM dbms_stats._relation_stats_locked
1283 RETURNING relid::regclass
1285 LANGUAGE sql STRICT;
1287 CREATE FUNCTION dbms_stats.unlock_table_stats(
1290 ) RETURNS SETOF regclass AS
1293 LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1294 LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1296 DELETE FROM dbms_stats._relation_stats_locked
1297 WHERE relid = dbms_stats.relname($1, $2)::regclass
1298 RETURNING relid::regclass
1300 LANGUAGE sql STRICT;
1302 CREATE FUNCTION dbms_stats.unlock_column_stats(
1305 ) RETURNS SETOF regclass AS
1310 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
1311 WHERE a.attrelid = $1 AND a.attname = $2;
1312 IF $2 IS NOT NULL AND set_attnum IS NULL THEN
1313 RAISE EXCEPTION 'column "%" of "%" does not exist', $2, $1;
1316 LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1317 LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1319 DELETE FROM dbms_stats._column_stats_locked
1321 AND staattnum = set_attnum;
1326 LANGUAGE plpgsql STRICT;
1328 CREATE FUNCTION dbms_stats.unlock_column_stats(
1332 ) RETURNS SETOF regclass AS
1337 SELECT a.attnum INTO set_attnum FROM pg_catalog.pg_attribute a
1338 WHERE a.attrelid = dbms_stats.relname($1, $2)::regclass
1340 IF $3 IS NOT NULL AND set_attnum IS NULL THEN
1341 RAISE EXCEPTION 'column "%" of "%.%" does not exist', $3, $1, $2;
1344 LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1345 LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1347 DELETE FROM dbms_stats._column_stats_locked
1348 WHERE starelid = dbms_stats.relname($1, $2)::regclass
1349 AND staattnum = set_attnum;
1351 SELECT dbms_stats.relname($1, $2)::regclass;
1354 LANGUAGE plpgsql STRICT;
1357 -- IMPORT_STATS: Statistic import functions
1360 CREATE FUNCTION dbms_stats.import(
1361 nspname text DEFAULT NULL,
1362 relid regclass DEFAULT NULL,
1363 attname text DEFAULT NULL,
1364 src text DEFAULT NULL
1366 'MODULE_PATHNAME', 'dbms_stats_import'
1369 CREATE FUNCTION dbms_stats.import_database_stats(src text)
1372 SELECT dbms_stats.import(NULL, NULL, NULL, $1)
1376 CREATE FUNCTION dbms_stats.import_schema_stats(
1381 SELECT dbms_stats.import($1, NULL, NULL, $2)
1385 CREATE FUNCTION dbms_stats.import_table_stats(
1390 SELECT dbms_stats.import(NULL, $1, NULL, $2)
1394 CREATE FUNCTION dbms_stats.import_table_stats(
1400 SELECT dbms_stats.import(NULL, dbms_stats.relname($1, $2)::regclass, NULL, $3)
1404 CREATE FUNCTION dbms_stats.import_column_stats(
1410 SELECT dbms_stats.import(NULL, $1, $2, $3)
1414 CREATE FUNCTION dbms_stats.import_column_stats(
1421 SELECT dbms_stats.import(NULL, dbms_stats.relname($1, $2)::regclass, $3, $4)
1426 -- PURGE_STATS: Statistic purge function
1429 CREATE OR REPLACE FUNCTION dbms_stats.purge_stats(
1431 force bool DEFAULT false
1432 ) RETURNS SETOF dbms_stats.backup_history AS
1436 deleted dbms_stats.backup_history;
1439 RAISE EXCEPTION 'backup id is required';
1442 RAISE EXCEPTION 'force is not null';
1445 LOCK dbms_stats.backup_history IN SHARE UPDATE EXCLUSIVE MODE;
1446 LOCK dbms_stats.relation_stats_backup IN SHARE UPDATE EXCLUSIVE MODE;
1447 LOCK dbms_stats.column_stats_backup IN SHARE UPDATE EXCLUSIVE MODE;
1449 IF NOT EXISTS(SELECT * FROM dbms_stats.backup_history WHERE id = $1) THEN
1450 RAISE EXCEPTION 'backup id % does not exist', $1;
1452 IF NOT $2 AND NOT EXISTS(SELECT *
1453 FROM dbms_stats.backup_history
1456 RAISE WARNING 'at least one database-wise backup must be remain'
1457 USING HINT = 'use true as 2nd parameter, if you want to purge forcibly';
1462 SELECT * FROM dbms_stats.backup_history
1466 DELETE FROM dbms_stats.backup_history
1467 WHERE id = deleted.id;
1468 RETURN NEXT deleted;
1475 -- CLEAN_STATS: Clean orphan dummy statistic
1478 CREATE OR REPLACE FUNCTION dbms_stats.clean_up_stats() RETURNS SETOF text AS
1486 LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1487 LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
1489 -- We don't have to check that table-level dummy statistic of the table
1490 -- exists here, because the foreign key constraints defined on column-level
1491 -- dummy static table eusures that.
1492 FOR clean_rel_col, clean_relid, clean_attnum, clean_inherit IN
1493 SELECT r.relname || ', ' || v.staattnum::text,
1494 v.starelid, v.staattnum, v.stainherit
1495 FROM dbms_stats._column_stats_locked v
1496 JOIN dbms_stats._relation_stats_locked r ON (v.starelid = r.relid)
1500 WHERE a.attrelid = v.starelid
1501 AND a.attnum = v.staattnum
1502 AND a.attisdropped = false
1505 DELETE FROM dbms_stats._column_stats_locked
1506 WHERE starelid = clean_relid
1507 AND staattnum = clean_attnum
1508 AND stainherit = clean_inherit;
1509 RETURN NEXT clean_rel_col;
1513 DELETE FROM dbms_stats._relation_stats_locked r
1517 WHERE c.oid = r.relid)
1518 RETURNING relname || ',';
1524 GRANT USAGE ON schema dbms_stats TO PUBLIC;