4 * No.2-3 dbms_stats.backup_history_id_seq
7 SELECT setval('dbms_stats.backup_history_id_seq', 0, false);
9 SELECT setval('dbms_stats.backup_history_id_seq', 1, false);
10 INSERT INTO dbms_stats.backup_history(time, unit)
11 VALUES ('2011-01-01', 't');
12 SELECT id, time, unit FROM dbms_stats.backup_history
15 INSERT INTO dbms_stats.backup_history(time, unit)
16 VALUES ('2011-01-02', 'c'), ('2011-01-03', 'd');
17 SELECT id, time, unit FROM dbms_stats.backup_history
20 SELECT setval('dbms_stats.backup_history_id_seq', 1, false);
21 DELETE FROM dbms_stats.backup_history;
24 * No.3-1 dbms_stats.use_locked_stats
26 DELETE FROM dbms_stats.relation_stats_locked;
27 EXPLAIN (costs false) SELECT * FROM s0.st2 WHERE id < 1;
28 SELECT dbms_stats.lock_table_stats('s0.st2'::regclass);
29 UPDATE dbms_stats.relation_stats_locked SET curpages = 10000;
32 SET pg_dbms_stats.use_locked_stats TO ON;
33 EXPLAIN (costs false) SELECT * FROM s0.st2 WHERE id < 1;
35 SET pg_dbms_stats.use_locked_stats TO OFF;
36 EXPLAIN (costs false) SELECT * FROM s0.st2 WHERE id < 1;
38 /* Reconnection as regular user */
40 SHOW pg_dbms_stats.use_locked_stats;
41 SET pg_dbms_stats.use_locked_stats TO OFF;
42 SHOW pg_dbms_stats.use_locked_stats;
43 EXPLAIN (costs false) SELECT * FROM s0.st2 WHERE id < 1;
44 RESET pg_dbms_stats.use_locked_stats;
45 EXPLAIN (costs false) SELECT * FROM s0.st2 WHERE id < 1;
47 /* Reconnection as super user */
49 DELETE FROM dbms_stats.relation_stats_locked;
52 * No.4-1 DATA TYPE dbms_stats.anyarray
54 CREATE TABLE st3(id integer, name char(1000), num_arr char(5)[]);
55 INSERT INTO st3 SELECT i, i , ARRAY[i::char, 'a'] FROM generate_series(1,10) g(i);
57 SELECT staattnum, stavalues1 FROM pg_statistic
58 WHERE starelid = 'public.st3'::regclass
60 \copy (SELECT stavalues1::dbms_stats.anyarray FROM dbms_stats.column_stats_effective WHERE starelid = 'st3'::regclass) TO 'results/anyarray_test.cp' binary
61 CREATE TABLE st4 (arr dbms_stats.anyarray);
63 SELECT t.typname, n.nspname,
64 t.typlen, t.typbyval, t.typtype,
65 t.typcategory, t.typispreferred, t.typispreferred,
66 t.typdelim, t.typrelid, t.typmodin,
67 t.typmodout, t.typanalyze, t.typalign,
68 t.typstorage, t.typnotnull, t.typbasetype, t.typtypmod,
69 t.typndims, t.typcollation, t.typdefaultbin, t.typdefault
70 FROM pg_type t, pg_namespace n
71 WHERE t.typname = 'anyarray'
72 AND t.typnamespace = n.oid
75 INSERT INTO st4 VALUES(NULL);
79 SELECT stavalues1::dbms_stats.anyarray
80 FROM dbms_stats.column_stats_effective
81 WHERE starelid = 'st3'::regclass
83 SELECT count(*) FROM st4;
85 SELECT stavalues1::dbms_stats.anyarray
86 FROM dbms_stats.column_stats_effective
87 WHERE starelid = 'st3'::regclass
92 SELECT stavalues1::dbms_stats.anyarray
93 FROM dbms_stats.column_stats_effective
94 WHERE starelid = 'st3'::regclass
96 SELECT count(*) FROM st4;
98 SELECT stavalues1::dbms_stats.anyarray
99 FROM dbms_stats.column_stats_effective
100 WHERE starelid = 'st3'::regclass
105 SELECT stavalues1::dbms_stats.anyarray
106 FROM dbms_stats.column_stats_effective
107 WHERE starelid = 'st3'::regclass
109 SELECT count(*) FROM st4;
111 SELECT stavalues1::dbms_stats.anyarray
112 FROM dbms_stats.column_stats_effective
113 WHERE starelid = 'st3'::regclass
118 SELECT stavalues1::dbms_stats.anyarray
119 FROM dbms_stats.column_stats_effective
120 WHERE starelid = 'st3'::regclass
122 SELECT count(*) FROM st4;
124 SELECT stavalues1::dbms_stats.anyarray
125 FROM dbms_stats.column_stats_effective
126 WHERE starelid = 'st3'::regclass
131 SELECT stavalues1::dbms_stats.anyarray
132 FROM dbms_stats.column_stats_effective
133 WHERE starelid = 'st3'::regclass;
134 \copy st4(arr) FROM 'results/anyarray_test.cp' binary
140 SELECT dbms_stats.unlock_database_stats();
141 SELECT dbms_stats.lock_table_stats('st1');
144 * No.5-2 invalid calls of dbms_stats.invalidate_column_cache
147 SELECT dbms_stats.invalidate_column_cache();
151 * Driver function dbms_stats.invalidate_cache1
153 CREATE TRIGGER invalidate_cache1
154 AFTER INSERT OR DELETE OR UPDATE
156 FOR EACH ROW EXECUTE PROCEDURE dbms_stats.invalidate_column_cache();
157 INSERT INTO pt0 VALUES (1,'2012/12/12');
158 DROP TRIGGER invalidate_cache1 ON pt0;
162 * Driver function dbms_stats.invalidate_cache2
164 CREATE TRIGGER invalidate_cache2
165 BEFORE INSERT OR DELETE OR UPDATE
167 FOR EACH STATEMENT EXECUTE PROCEDURE dbms_stats.invalidate_column_cache();
168 INSERT INTO pt0 VALUES (1,'2012/12/12');
169 DROP TRIGGER invalidate_cache2 ON pt0;
173 * Driver function dbms_stats.invalidate_cache3
175 CREATE TRIGGER invalidate_cache3
178 FOR EACH STATEMENT EXECUTE PROCEDURE dbms_stats.invalidate_column_cache();
180 DROP TRIGGER invalidate_cache3 ON pt0;
183 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
184 INSERT INTO dbms_stats.relation_stats_locked (relid, relname) VALUES (0, 'dummy');
185 INSERT INTO dbms_stats.column_stats_locked (starelid, staattnum, stainherit)
187 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
190 INSERT INTO dbms_stats.column_stats_locked (starelid, staattnum, stainherit)
191 VALUES ('st1_idx'::regclass, 1, true);
192 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
195 INSERT INTO dbms_stats.column_stats_locked (starelid, staattnum, stainherit)
196 VALUES ('complex'::regclass, 1, true);
197 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
200 UPDATE dbms_stats.column_stats_locked SET stanullfrac = 1
201 WHERE starelid = 'st1'::regclass
203 AND stainherit = false;
205 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
208 DELETE FROM dbms_stats.column_stats_locked
209 WHERE starelid = 'st1'::regclass
211 AND stainherit = false;
212 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
215 INSERT INTO dbms_stats.column_stats_locked
216 (starelid, staattnum, stainherit, stanullfrac)
217 VALUES ('st1'::regclass, 1, false, 1);
218 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
221 PREPARE p2 AS SELECT str FROM st1 WHERE lower(str) IS NULL;
222 EXPLAIN (costs false) SELECT str FROM st1 WHERE lower(str) IS NULL;
223 EXPLAIN (costs false) EXECUTE p2;
224 INSERT INTO dbms_stats.relation_stats_locked (relid, relname)
225 VALUES ('st1_exp'::regclass, 'dummy');
226 INSERT INTO dbms_stats.column_stats_locked
227 (starelid, staattnum, stainherit, stanullfrac)
228 VALUES ('st1_exp'::regclass, 1, false, 1);
229 EXPLAIN (costs false) SELECT str FROM st1 WHERE lower(str) IS NULL;
230 EXPLAIN (costs false) EXECUTE p2;
234 SELECT dbms_stats.unlock_database_stats();
238 * No.5-3 dbms_stats.invalidate_relation_cache
241 SELECT dbms_stats.invalidate_relation_cache();
245 * Driver function dbms_stats.invalidate_cache1
247 CREATE TRIGGER invalidate_cache1
248 AFTER INSERT OR DELETE OR UPDATE
250 FOR EACH ROW EXECUTE PROCEDURE dbms_stats.invalidate_relation_cache();
251 INSERT INTO pt0 VALUES (1,'2012/12/12');
252 DROP TRIGGER invalidate_cache1 ON pt0;
256 * Driver function dbms_stats.invalidate_cache2
258 CREATE TRIGGER invalidate_cache2
259 BEFORE INSERT OR DELETE OR UPDATE
261 FOR EACH STATEMENT EXECUTE PROCEDURE dbms_stats.invalidate_relation_cache();
262 INSERT INTO pt0 VALUES (1,'2012/12/12');
263 DROP TRIGGER invalidate_cache2 ON pt0;
267 * Driver function dbms_stats.invalidate_cache3
269 CREATE TRIGGER invalidate_cache3
272 FOR EACH STATEMENT EXECUTE PROCEDURE dbms_stats.invalidate_relation_cache();
274 DROP TRIGGER invalidate_cache3 ON pt0;
277 SELECT dbms_stats.unlock_database_stats();
278 SELECT dbms_stats.lock_table_stats('st1');
279 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
280 INSERT INTO dbms_stats.relation_stats_locked (relid, relname) VALUES (0, 'dummy');
281 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
284 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
285 INSERT INTO dbms_stats.relation_stats_locked (relid, relname)
286 VALUES ('st1_idx'::regclass, 'st1_idx');
287 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
290 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
291 INSERT INTO dbms_stats.relation_stats_locked (relid, relname)
292 VALUES ('complex'::regclass, 'complex');
293 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
296 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
297 UPDATE dbms_stats.relation_stats_locked SET curpages = 1
298 WHERE relid = 'st1'::regclass;
300 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
303 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
304 DELETE FROM dbms_stats.relation_stats_locked
305 WHERE relid = 'st1'::regclass;
306 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
309 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
310 INSERT INTO dbms_stats.relation_stats_locked (relid, relname, curpages)
311 VALUES ('st1'::regclass, 'st1', 1);
312 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
315 SELECT dbms_stats.unlock_database_stats();
316 SELECT dbms_stats.lock_table_stats('st1');
317 SELECT relname, curpages FROM dbms_stats.relation_stats_locked
318 WHERE relid = 'st1'::regclass;
319 SELECT pg_sleep(0.7);
320 SELECT reset_stat_and_cache();
322 UPDATE dbms_stats.relation_stats_locked SET curpages = 1000
323 WHERE relid = 'st1_exp'::regclass;
324 SELECT pg_sleep(0.7);
325 SELECT * FROM lockd_io;
326 SELECT reset_stat_and_cache();
327 SELECT relname, curpages FROM dbms_stats.relation_stats_locked
328 WHERE relid = 'st1'::regclass;
329 SELECT pg_sleep(0.7);
330 SELECT * FROM lockd_io;
333 * No.5-4 StatsCacheRelCallback
336 UPDATE dbms_stats.relation_stats_locked SET curpages = 1
337 WHERE relid = 'st1'::regclass;
339 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
341 SET pg_dbms_stats.use_locked_stats to NO;
342 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
343 SET pg_dbms_stats.use_locked_stats to YES;
344 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
346 SELECT dbms_stats.unlock_database_stats();
347 SELECT dbms_stats.lock_table_stats('st1');
349 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
350 \! psql contrib_regression -c "UPDATE dbms_stats.column_stats_locked SET stanullfrac = 1 WHERE starelid = 'st1'::regclass"
351 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
354 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
355 \! psql contrib_regression -c "DELETE FROM dbms_stats.column_stats_locked WHERE starelid = 'st1'::regclass"
356 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
359 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
360 \! psql contrib_regression -c "INSERT INTO dbms_stats.column_stats_locked (starelid, staattnum, stainherit, stanullfrac) VALUES ('st1'::regclass, 1, false, 1)"
361 EXPLAIN (costs false) SELECT * FROM st1 WHERE val IS NULL;
363 SELECT dbms_stats.unlock_database_stats();
366 CREATE TABLE s0.droptest(id integer);
367 INSERT INTO s0.droptest VALUES (1),(2),(3);
369 SELECT * FROM s0.droptest
371 SELECT pg_sleep(0.7);
372 SELECT reset_stat_and_cache();
373 ALTER TABLE s0.droptest RENAME TO test;
374 SELECT pg_sleep(0.7);
375 SELECT * FROM lockd_io;
376 SELECT reset_stat_and_cache();
377 SELECT * FROM s0.test
379 SELECT pg_sleep(0.7);
380 SELECT * FROM lockd_io;
381 ALTER TABLE s0.test RENAME TO droptest;
385 SELECT * FROM s0.droptest
387 SELECT pg_sleep(0.7);
388 SELECT reset_stat_and_cache();
389 ALTER TABLE s0.droptest RENAME id TO test;
390 SELECT pg_sleep(0.7);
391 SELECT * FROM lockd_io;
392 SELECT reset_stat_and_cache();
393 SELECT * FROM s0.droptest
395 SELECT pg_sleep(0.7);
396 SELECT * FROM lockd_io;
397 ALTER TABLE s0.droptest RENAME test TO id;
400 INSERT INTO s0.droptest VALUES (4);
401 SELECT * FROM s0.droptest
403 SELECT pg_sleep(0.7);
404 SELECT reset_stat_and_cache();
406 SELECT pg_sleep(0.7);
407 SELECT * FROM lockd_io;
408 SELECT reset_stat_and_cache();
409 SELECT * FROM s0.droptest
411 SELECT pg_sleep(1.0);
412 SELECT * FROM lockd_io;
415 DELETE FROM s0.droptest;
416 INSERT INTO s0.droptest VALUES (4),(5);
417 SELECT * FROM s0.droptest
419 SELECT pg_sleep(0.7);
420 SELECT reset_stat_and_cache();
422 SELECT pg_sleep(0.7);
423 SELECT * FROM lockd_io;
424 SELECT reset_stat_and_cache();
425 SELECT * FROM s0.droptest
427 SELECT pg_sleep(0.7);
428 SELECT * FROM lockd_io;
431 DROP TABLE s0.droptest;
434 * No.6-1 dbms_stats.relname
437 SELECT dbms_stats.relname('aaa', 'bbb');
439 SELECT dbms_stats.relname(NULL, 'bbb');
441 SELECT dbms_stats.relname('aaa', NULL);
443 SELECT dbms_stats.relname(NULL, NULL);
445 SELECT dbms_stats.relname('', '');
447 SELECT dbms_stats.relname('aAa', 'bBb');
449 SELECT dbms_stats.relname('a a', 'b b');
451 SELECT dbms_stats.relname('a.a', 'b.b');
453 SELECT dbms_stats.relname(E'a\na', E'b\nb');
455 SELECT dbms_stats.relname('a"a', 'b"b');
457 SELECT dbms_stats.relname('あいう', '亞伊卯');
460 * No.6-2 dbms_stats.is_system_schema
463 SELECT dbms_stats.is_system_schema('pg_catalog');
465 SELECT dbms_stats.is_system_schema('pg_toast');
467 SELECT dbms_stats.is_system_schema('information_schema');
469 SELECT dbms_stats.is_system_schema('dbms_stats');
471 SELECT dbms_stats.is_system_schema(NULL);
473 SELECT dbms_stats.is_system_schema('');
475 SELECT dbms_stats.is_system_schema('s0');
478 * Driver function dbms_stats.is_system_schema1
480 CREATE FUNCTION dbms_stats.is_system_schema1(schemaname text)
482 '$libdir/pg_dbms_stats', 'dbms_stats_is_system_schema'
483 LANGUAGE C IMMUTABLE STRICT;
484 SELECT dbms_stats.is_system_schema1('s0');
485 DROP FUNCTION dbms_stats.is_system_schema1(schemaname text);
488 * No.6-3 dbms_stats.is_system_catalog
491 SELECT dbms_stats.is_system_catalog('s0.st0');
493 SELECT dbms_stats.is_system_catalog('st0');
495 SELECT dbms_stats.is_system_catalog('s00.s0');
497 SELECT dbms_stats.is_system_catalog(NULL);
500 * Driver function dbms_stats.is_system_catalog1
502 CREATE FUNCTION dbms_stats.is_system_catalog1(relid regclass)
504 '$libdir/pg_dbms_stats', 'dbms_stats_is_system_catalog'
506 SELECT dbms_stats.is_system_catalog1('s0.st0');
507 DROP FUNCTION dbms_stats.is_system_catalog1(relid regclass);
511 * No.6-4 dbms_stats.is_target_relkind
514 SELECT dbms_stats.is_target_relkind('r');
516 SELECT dbms_stats.is_target_relkind('i');
518 SELECT dbms_stats.is_target_relkind('S');
520 SELECT dbms_stats.is_target_relkind('v');
522 SELECT dbms_stats.is_target_relkind('c');
524 SELECT dbms_stats.is_target_relkind('t');
526 SELECT dbms_stats.is_target_relkind('a');
528 SELECT dbms_stats.is_target_relkind('');
530 SELECT dbms_stats.is_target_relkind(NULL);
531 --#No.6-4-10 result varies according to a version
532 --#No.6-4-11 result varies according to a version
535 * No.7-1 dbms_stats.backup
537 INSERT INTO dbms_stats.backup_history(id, time, unit) values(1, '2012-01-01', 'd');
539 DELETE FROM dbms_stats.relation_stats_backup;
540 SELECT dbms_stats.backup(1, 's0.st0'::regclass, NULL);
541 SELECT relid::regclass FROM dbms_stats.relation_stats_backup
544 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_backup
545 GROUP BY starelid, staattnum
546 ORDER BY starelid, staattnum;
549 DELETE FROM dbms_stats.relation_stats_backup;
550 SELECT dbms_stats.backup(1, 'st0'::regclass, NULL);
551 SELECT relid::regclass FROM dbms_stats.relation_stats_backup
554 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_backup
555 GROUP BY starelid, staattnum
556 ORDER BY starelid, staattnum;
559 DELETE FROM dbms_stats.relation_stats_backup;
560 SELECT dbms_stats.backup(1, 'public.notfound'::regclass, NULL);
561 SELECT count(*) FROM dbms_stats.relation_stats_backup;
562 SELECT count(*) FROM dbms_stats.column_stats_backup;
565 DELETE FROM dbms_stats.relation_stats_backup;
566 SELECT dbms_stats.backup(1, 's0.st0'::regclass, NULL);
567 SELECT relid::regclass FROM dbms_stats.relation_stats_backup
570 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_backup
571 GROUP BY starelid, staattnum
572 ORDER BY starelid, staattnum;
575 DELETE FROM dbms_stats.relation_stats_backup;
576 SELECT dbms_stats.backup(1, 'pg_toast.pg_toast_2618'::regclass, NULL);
577 SELECT relid::regclass FROM dbms_stats.relation_stats_backup
580 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_backup
581 GROUP BY starelid, staattnum
582 ORDER BY starelid, staattnum;
585 DELETE FROM dbms_stats.relation_stats_backup;
586 SELECT dbms_stats.backup(1, 's0.st0_idx'::regclass, NULL);
587 SELECT relid::regclass FROM dbms_stats.relation_stats_backup
590 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_backup
591 GROUP BY starelid, staattnum
592 ORDER BY starelid, staattnum;
595 DELETE FROM dbms_stats.relation_stats_backup;
596 SELECT dbms_stats.backup(1, 's0.ss0'::regclass, NULL);
597 SELECT count(*) FROM dbms_stats.relation_stats_backup;
598 SELECT count(*) FROM dbms_stats.column_stats_backup;
601 DELETE FROM dbms_stats.relation_stats_backup;
602 SELECT dbms_stats.backup(1, 's0.sc0'::regclass, NULL);
603 SELECT count(*) FROM dbms_stats.relation_stats_backup;
604 SELECT count(*) FROM dbms_stats.column_stats_backup;
606 --#No.7-1-9 ut-<PG Version>
607 --#No.7-1-10 ut-<PG Version>
610 DELETE FROM dbms_stats.relation_stats_backup;
611 SELECT dbms_stats.backup(1, 's0.st0'::regclass, 1::int2);
612 SELECT relid::regclass FROM dbms_stats.relation_stats_backup
615 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_backup
616 GROUP BY starelid, staattnum
617 ORDER BY starelid, staattnum;
619 --#No.7-1-12 ut-<PG Version>
622 DELETE FROM dbms_stats.relation_stats_backup;
623 SELECT dbms_stats.backup(1, 's0.st0'::regclass, NULL);
624 SELECT relid::regclass FROM dbms_stats.relation_stats_backup
627 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_backup
628 GROUP BY starelid, staattnum
629 ORDER BY starelid, staattnum;
631 --#No.7-1-14 ut-<PG Version>
634 DELETE FROM dbms_stats.relation_stats_backup;
635 SELECT dbms_stats.backup(1, 'pg_catalog.pg_class'::regclass, NULL);
636 SELECT count(*) FROM dbms_stats.relation_stats_backup;
637 SELECT count(*) FROM dbms_stats.column_stats_backup;
640 SELECT dbms_stats.backup(1, 's0.st0'::regclass, NULL);
641 DELETE FROM dbms_stats.column_stats_backup;
642 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_backup
643 GROUP BY starelid, staattnum
644 ORDER BY starelid, staattnum;
645 SELECT count(*) FROM dbms_stats.relation_stats_backup;
646 -- SELECT dbms_stats.backup(1, 's0.st0'::regclass, NULL);
647 -- To avoid test unstability caused by relation id allocation, unique
648 -- constraint which used to be checked above is now checked more
649 -- directly in the following step.
650 SELECT ic.relname idxname, i.indisprimary
652 JOIN pg_class c ON (c.oid = i.indrelid)
653 JOIN pg_namespace n ON (n.oid = c.relnamespace)
654 JOIN pg_class ic ON (ic.oid = i.indexrelid)
655 WHERE n.nspname = 'dbms_stats' AND c.relname = 'relation_stats_backup';
656 SELECT count(*) FROM dbms_stats.relation_stats_backup;
657 SELECT count(*) FROM dbms_stats.column_stats_backup;
659 --#No.7-1-18 ut-<PG Version>
662 * Stab function dbms_stats.backup
664 ALTER FUNCTION dbms_stats.backup(
668 RENAME TO truth_func_backup;
670 CREATE OR REPLACE FUNCTION dbms_stats.backup(
677 RAISE NOTICE 'arguments are %, %, %', $1, $2, $3;
683 ALTER FUNCTION dbms_stats.backup(
687 RENAME TO truth_func_backup;
688 CREATE OR REPLACE FUNCTION dbms_stats.backup(
689 relid regclass DEFAULT NULL,
690 attname text DEFAULT NULL,
691 comment text DEFAULT NULL)
695 IF $3 = '<NULL>' THEN
696 RAISE NOTICE 'third argument is not NULL but string "<NULL>"';
698 RAISE NOTICE 'arguments are %, %, %', $1, $2, $3;
705 * No.8-2 dbms_stats.backup_database_stats
707 SELECT setval('dbms_stats.backup_history_id_seq',8);
709 SELECT dbms_stats.backup_database_stats('comment');
712 * No.8-4 dbms_stats.backup_table_stats(regclass,comment)
715 SELECT dbms_stats.backup_table_stats('s0.st0', 'comment');
717 SELECT dbms_stats.backup_table_stats('st0', 'comment');
719 SELECT dbms_stats.backup_table_stats('s00.s0', 'comment');
722 * No.8-5 dbms_stats.backup_table_stats(schemaname, tablename, comment)
725 SELECT dbms_stats.backup_table_stats('s0', 'st0', 'comment');
727 SELECT dbms_stats.backup_table_stats('s00', 's0', 'comment');
730 * No.8-6 dbms_stats.backup_column_stats(regclass, attname, comment)
733 SELECT dbms_stats.backup_column_stats('s0.st0', 'id', 'comment');
735 SELECT dbms_stats.backup_column_stats('st0', 'id', 'comment');
737 SELECT dbms_stats.backup_column_stats('s00.s0', 'id', 'comment');
740 * No.8-7 dbms_stats.backup_column_stats(schemaname, tablename, attname, comment)
743 SELECT dbms_stats.backup_column_stats('s0', 'st0', 'id', 'comment');
745 SELECT dbms_stats.backup_column_stats('s00', 's0', 'id', 'comment');
748 * Delete stab function dbms_stats.backup
750 DROP FUNCTION dbms_stats.backup(
754 ALTER FUNCTION dbms_stats.truth_func_backup(
759 DROP FUNCTION dbms_stats.backup(
763 ALTER FUNCTION dbms_stats.truth_func_backup(
771 * create backup statistics state A
773 DELETE FROM dbms_stats.backup_history;
775 INSERT INTO dbms_stats.backup_history(id, time, unit)
776 VALUES (1, '2012-02-29 23:59:56.999999', 'd');
778 SELECT setval('dbms_stats.backup_history_id_seq',1);
779 SELECT dbms_stats.backup();
780 UPDATE dbms_stats.backup_history
781 SET time = '2012-02-29 23:59:57'
783 SELECT dbms_stats.backup('s0.st0');
784 UPDATE dbms_stats.backup_history
785 SET time = '2012-02-29 23:59:57.000001'
787 SELECT dbms_stats.backup();
788 UPDATE dbms_stats.backup_history
789 SET time = '2012-02-29 23:59:58'
791 DELETE FROM dbms_stats.relation_stats_backup
793 SELECT dbms_stats.backup('s0.st0', 'id');
794 UPDATE dbms_stats.backup_history
795 SET time = '2012-03-01 00:00:00'
797 SELECT dbms_stats.backup('s0.st0');
798 UPDATE dbms_stats.backup_history
799 SET time = '2012-03-01 00:00:02'
801 SELECT dbms_stats.backup('public.st0');
802 UPDATE dbms_stats.backup_history
803 SET time = '2012-03-01 00:00:04'
805 INSERT INTO dbms_stats.backup_history(time, unit)
806 VALUES ('2012-03-01 00:00:06', 's');
807 SELECT dbms_stats.backup(8, c.oid, NULL)
808 FROM pg_catalog.pg_class c,
809 pg_catalog.pg_namespace n
810 WHERE n.nspname = 's0'
811 AND c.relnamespace = n.oid
812 AND c.relkind IN ('r', 'i');
814 SELECT * FROM dbms_stats.backup_history
820 * Stab function dbms_stats.restore
822 ALTER FUNCTION dbms_stats.restore(int8, regclass, text)
823 RENAME TO truth_func_restore;
824 CREATE FUNCTION dbms_stats.restore(int8, regclass DEFAULT NULL, text DEFAULT NULL)
825 RETURNS SETOF regclass AS
828 RAISE NOTICE 'arguments are "%, %, %"', $1, $2, $3;
830 SELECT c.oid::regclass
831 FROM pg_class c, dbms_stats.relation_stats_backup b
832 WHERE (c.oid = $2 OR $2 IS NULL)
834 AND c.relkind IN ('r', 'i')
835 AND (b.id <= $1 OR $1 IS NOT NULL)
837 ORDER BY c.oid::regclass::text;
843 * No.10-3 dbms_stats.restore_table_stats(regclass, as_of_timestamp)
846 SELECT dbms_stats.restore_table_stats('s0.st0', '2012-02-29 23:59:57');
848 SELECT dbms_stats.restore_table_stats('s0.st0', '2012-02-29 23:59:57.000002');
850 SELECT dbms_stats.restore_table_stats('s0.st0', '2012-01-01 00:00:00');
851 --#No.10-3-4 is skipped after lock tests
853 SELECT dbms_stats.restore_table_stats('s0.st0', '2012-02-29 23:59:57');
855 SELECT dbms_stats.restore_table_stats('st0', '2012-02-29 23:59:57');
857 SELECT dbms_stats.restore_table_stats('s00.s0', '2012-02-29 23:59:57');
859 * Stab dbms_stats.restore_table_stats(regclass, as_of_timestamp)
861 ALTER FUNCTION dbms_stats.restore_table_stats(regclass,
862 timestamp with time zone)
863 RENAME TO truth_func_restore_table_stats;
864 CREATE OR REPLACE FUNCTION dbms_stats.restore_table_stats(
866 as_of_timestamp timestamp with time zone)
867 RETURNS SETOF regclass AS
870 RAISE NOTICE 'arguments are %, %', $1, $2;
878 * No.10-4 dbms_stats.restore_table_stats(schemaname, tablename, as_of_timestamp)
881 SELECT dbms_stats.restore_table_stats('s0', 'st0', '2012-02-29 23:59:57');
882 DROP FUNCTION dbms_stats.restore_table_stats(regclass,
883 timestamp with time zone);
884 ALTER FUNCTION dbms_stats.truth_func_restore_table_stats(regclass,
885 timestamp with time zone)
886 RENAME TO restore_table_stats;
889 * No.10-5 dbms_stats.restore_column_stats(regclass, attname, as_of_timestamp)
892 SELECT dbms_stats.restore_column_stats('s0.st0', 'id', '2012-02-29 23:59:57');
894 SELECT dbms_stats.restore_column_stats('s0.st0', 'id', '2012-02-29 23:59:57.000002');
896 SELECT dbms_stats.restore_column_stats('s0.st0', 'id', '2012-01-01 00:00:00');
897 --#No.10-5-4 is skipped after lock tests
899 SELECT dbms_stats.restore_column_stats('s0.st0', 'id', '2012-02-29 23:59:57');
901 SELECT dbms_stats.restore_column_stats('st0', 'id', '2012-02-29 23:59:57');
903 SELECT dbms_stats.restore_column_stats('s00.s0', 'id', '2012-02-29 23:59:57');
906 * No.10-6 dbms_stats.restore_column_stats(
907 * schemaname, tablename, attname, as_of_timestamp)
910 SELECT dbms_stats.restore_column_stats('s0', 'st0', 'id', '2012-02-29 23:59:57');
913 * No.15-1 dbms_stats.purge_stats
916 SELECT * FROM dbms_stats.backup_history;
918 SELECT relation::regclass, mode
920 WHERE relation::regclass::text LIKE 'dbms_stats.\_%\_locked'
921 OR relation::regclass::text LIKE 'dbms_stats.backup_history'
922 OR relation::regclass::text LIKE 'dbms_stats.%\_backup'
923 ORDER BY relation::regclass::text, mode;
924 SELECT id, unit, comment FROM dbms_stats.purge_stats(2);
925 SELECT relation::regclass, mode
927 WHERE relation::regclass::text LIKE 'dbms_stats.\_%\_locked'
928 OR relation::regclass::text LIKE 'dbms_stats.backup_history'
929 OR relation::regclass::text LIKE 'dbms_stats.%\_backup'
930 ORDER BY relation::regclass::text, mode;
932 SELECT * FROM dbms_stats.backup_history;
934 SELECT id, unit, comment FROM dbms_stats.purge_stats(NULL);
936 SELECT id, unit, comment FROM dbms_stats.purge_stats(-1);
938 SELECT id, unit, comment FROM dbms_stats.purge_stats(2, NULL);
940 SELECT * FROM dbms_stats.backup_history;
941 SELECT id, unit, comment FROM dbms_stats.purge_stats(3);
942 SELECT * FROM dbms_stats.backup_history;
944 SELECT * FROM dbms_stats.backup_history;
945 SELECT id, unit, comment FROM dbms_stats.purge_stats(6);
946 SELECT * FROM dbms_stats.backup_history;
948 SELECT * FROM dbms_stats.backup_history;
949 SELECT id, unit, comment FROM dbms_stats.purge_stats(8);
950 SELECT * FROM dbms_stats.backup_history;
952 SELECT * FROM dbms_stats.backup_history;
953 SELECT id, unit, comment FROM dbms_stats.purge_stats(8, true);
954 SELECT * FROM dbms_stats.backup_history;
957 * create backup statistics state A
959 DELETE FROM dbms_stats.backup_history;
961 INSERT INTO dbms_stats.backup_history(id, time, unit)
962 VALUES (1, '2012-02-29 23:59:56.999999', 'd');
964 SELECT setval('dbms_stats.backup_history_id_seq',1);
965 SELECT dbms_stats.backup();
966 UPDATE dbms_stats.backup_history
967 SET time = '2012-02-29 23:59:57'
969 SELECT dbms_stats.backup('s0.st0');
970 UPDATE dbms_stats.backup_history
971 SET time = '2012-02-29 23:59:57.000001'
973 SELECT dbms_stats.backup();
974 UPDATE dbms_stats.backup_history
975 SET time = '2012-02-29 23:59:58'
977 DELETE FROM dbms_stats.relation_stats_backup
979 SELECT dbms_stats.backup('s0.st0', 'id');
980 UPDATE dbms_stats.backup_history
981 SET time = '2012-03-01 00:00:00'
983 SELECT dbms_stats.backup('s0.st0');
984 UPDATE dbms_stats.backup_history
985 SET time = '2012-03-01 00:00:02'
987 SELECT dbms_stats.backup('public.st0');
988 UPDATE dbms_stats.backup_history
989 SET time = '2012-03-01 00:00:04'
991 INSERT INTO dbms_stats.backup_history(time, unit)
992 VALUES ('2012-03-01 00:00:06', 's');
993 SELECT dbms_stats.backup(8, c.oid, NULL)
994 FROM pg_catalog.pg_class c,
995 pg_catalog.pg_namespace n
996 WHERE n.nspname = 's0'
997 AND c.relnamespace = n.oid
998 AND c.relkind IN ('r', 'i');
1001 * restore test when only backup data does not exist 's0' schema
1003 DELETE FROM dbms_stats.column_stats_backup;
1004 DELETE FROM dbms_stats.relation_stats_backup
1005 WHERE relname LIKE 's0.%';
1006 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1007 SELECT count(*) FROM dbms_stats.column_stats_backup;
1009 SELECT dbms_stats.restore_schema_stats('s0', '2012-03-01 00:00:04');
1012 * restore test when there are only backup hisotory
1014 DELETE FROM dbms_stats.relation_stats_backup;
1015 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1016 SELECT count(*) FROM dbms_stats.column_stats_backup;
1018 SELECT dbms_stats.restore_database_stats('2012-02-29 23:59:58');
1020 SELECT dbms_stats.restore_schema_stats('s0', '2012-02-29 23:59:58');
1022 * restore when Backup does not exist
1024 DELETE FROM dbms_stats.backup_history;
1025 SELECT count(*) FROM dbms_stats.backup_history;
1026 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1027 SELECT count(*) FROM dbms_stats.column_stats_backup;
1030 SELECT dbms_stats.restore_database_stats('2012-02-29 23:59:57');
1032 SELECT dbms_stats.restore_schema_stats('s0', '2012-02-29 23:59:57');
1034 SELECT dbms_stats.restore_table_stats('s0.st0', '2012-02-29 23:59:57');
1036 SELECT dbms_stats.restore_column_stats('s0.st0', 'id', '2012-02-29 23:59:57');
1039 * Delete stab function dbms_stats.restore
1041 DROP FUNCTION dbms_stats.restore(int8, regclass, text);
1042 ALTER FUNCTION dbms_stats.truth_func_restore(int8, regclass, text)
1046 * No.18-1 dbms_stats.clean_up_stats
1048 CREATE TABLE clean_test(id integer, num integer);
1049 INSERT INTO clean_test SELECT i, i FROM generate_series(1,10) t(i);
1053 SELECT dbms_stats.lock_table_stats('clean_test');
1054 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1055 SELECT count(*) FROM dbms_stats.column_stats_locked;
1056 SELECT dbms_stats.clean_up_stats();
1057 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1058 SELECT count(*) FROM dbms_stats.column_stats_locked;
1061 DELETE FROM dbms_stats.relation_stats_locked;
1062 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1063 SELECT count(*) FROM dbms_stats.column_stats_locked;
1064 SELECT dbms_stats.clean_up_stats();
1065 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1066 SELECT count(*) FROM dbms_stats.column_stats_locked;
1068 SELECT dbms_stats.lock_table_stats('clean_test');
1069 DROP TABLE clean_test;
1070 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1071 SELECT dbms_stats.clean_up_stats();
1072 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1074 DELETE FROM dbms_stats.relation_stats_locked;
1075 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1076 SELECT dbms_stats.clean_up_stats();
1077 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1079 CREATE TABLE clean_test(id integer, num integer);
1080 INSERT INTO clean_test SELECT i, i FROM generate_series(1,10) t(i);
1082 SELECT dbms_stats.lock_table_stats('clean_test');
1083 ALTER TABLE clean_test DROP COLUMN num;
1084 ALTER TABLE clean_test ADD num integer;
1085 UPDATE dbms_stats.column_stats_locked
1087 WHERE starelid = 'clean_test'::regclass
1089 UPDATE clean_test SET num = id;
1090 SELECT count(*) FROM pg_statistic
1091 WHERE starelid = 'clean_test'::regclass;
1092 SELECT count(*) FROM dbms_stats.column_stats_locked
1093 WHERE starelid = 'clean_test'::regclass;
1094 SELECT dbms_stats.clean_up_stats();
1095 SELECT count(*) FROM dbms_stats.column_stats_locked
1096 WHERE starelid = 'clean_test'::regclass;
1098 DELETE FROM dbms_stats.column_stats_locked
1099 WHERE starelid = 'clean_test'::regclass
1101 SELECT count(*) FROM pg_statistic
1102 WHERE starelid = 'clean_test'::regclass;
1103 SELECT count(*) FROM dbms_stats.column_stats_locked
1104 WHERE starelid = 'clean_test'::regclass;
1105 SELECT dbms_stats.clean_up_stats();
1106 SELECT count(*) FROM dbms_stats.column_stats_locked
1107 WHERE starelid = 'clean_test'::regclass;
1110 SELECT dbms_stats.lock_table_stats('clean_test');
1111 ALTER TABLE clean_test DROP COLUMN num;
1112 SELECT count(*) FROM dbms_stats.column_stats_locked
1113 WHERE starelid = 'clean_test'::regclass;
1114 SELECT dbms_stats.clean_up_stats();
1115 SELECT count(*) FROM dbms_stats.column_stats_locked
1116 WHERE starelid = 'clean_test'::regclass;
1118 DELETE FROM dbms_stats.column_stats_locked
1119 WHERE starelid = 'clean_test'::regclass
1121 SELECT count(*) FROM dbms_stats.column_stats_locked
1122 WHERE starelid = 'clean_test'::regclass;
1123 SELECT dbms_stats.clean_up_stats();
1124 SELECT count(*) FROM dbms_stats.column_stats_locked
1125 WHERE starelid = 'clean_test'::regclass;
1126 DELETE FROM dbms_stats.relation_stats_locked;
1127 DROP TABLE clean_test;
1130 * No.19-1 dummy statistics view for general users privileges.
1132 SET SESSION AUTHORIZATION regular_user;
1134 SELECT count(*) FROM dbms_stats.relation_stats_locked WHERE false;
1136 SELECT count(*) FROM dbms_stats.column_stats_locked WHERE false;
1138 SELECT count(*) FROM dbms_stats.stats WHERE false;
1139 RESET SESSION AUTHORIZATION;
1142 * No.20-1 confirm change at 1.3.5.
1144 SELECT CURRENT_USER;
1145 CREATE TABLE s0.st4 (a int, b text);
1146 CREATE INDEX i_st4_a on s0.st4 (a);
1147 CREATE VIEW s0.vst4 AS select * FROM s0.st4;
1148 GRANT SELECT ON s0.vst4 TO regular_user;
1149 INSERT INTO s0.st4 (SELECT a, a::text FROM generate_series(0, 999) a);
1151 SELECT dbms_stats.lock('s0.st4');
1153 INSERT INTO s0.st4 (SELECT 1, a::text FROM generate_series(0, 999) a);
1156 EXPLAIN (COSTS OFF) SELECT * FROM s0.vst4 WHERE a = 1;
1157 EXPLAIN (COSTS OFF) SELECT * FROM s0.st4 WHERE a = 1;
1159 SET SESSION AUTHORIZATION regular_user;
1161 EXPLAIN (COSTS OFF) SELECT * FROM s0.st4 WHERE a = 1;
1162 EXPLAIN (COSTS OFF) SELECT * FROM s0.vst4 WHERE a = 1;
1164 SET pg_dbms_stats.use_locked_stats TO off;
1165 EXPLAIN (COSTS OFF) SELECT * FROM s0.vst4 WHERE a = 1;
1167 ALTER TABLE dbms_stats.relation_stats_locked OWNER TO super_user;
1169 SELECT dbms_stats.unlock('s0.st4');
1170 DROP TABLE s0.st4 CASCADE;