3 * No.2-1 table definitions.
6 \d dbms_stats.backup_history
8 \d dbms_stats.column_stats_backup
10 \d dbms_stats.column_stats_locked
12 \d dbms_stats.relation_stats_backup
14 \d dbms_stats.relation_stats_locked
17 * No.2-2 view definitions.
20 \dS+ dbms_stats.column_stats_effective
22 \dS+ dbms_stats.relation_stats_effective
26 \dS+ dbms_stats.column_stats_locked
28 \dS+ dbms_stats.relation_stats_locked
31 * No.2-4 dbms_stats.anyarray
34 SELECT n.nspname, t.typname, t.typlen, t.typbyval, t.typtype,
35 t.typcategory, t.typispreferred, t.typisdefined, t.typdelim,
36 t.typrelid, t.typelem, t.typinput, t.typoutput, t.typreceive,
37 t.typsend, t.typmodin, t.typmodout, t.typanalyze, t.typalign,
38 t.typstorage, t.typnotnull, t.typbasetype, t.typtypmod, t.typndims,
39 t.typcollation, t.typdefaultbin, t.typdefault, t.typacl
40 FROM pg_type t, pg_namespace n
41 WHERE t.typnamespace = n.oid
42 AND n.nspname = 'dbms_stats'
43 AND t.typname = 'anyarray';
46 * No.5-1 dbms_stats.merge
48 UPDATE pg_statistic SET
49 stanullfrac = staattnum,
51 stadistinct = staattnum,
62 stanumbers1 = ARRAY[staattnum,4],
63 stanumbers2 = ARRAY[staattnum,1],
64 stanumbers3 = ARRAY[staattnum,2],
65 stanumbers4 = ARRAY[staattnum,3],
66 stanumbers5 = ARRAY[staattnum,5],
67 stavalues2 = array_cat(stavalues1,stavalues1),
68 stavalues3 = array_cat(array_cat(stavalues1,stavalues1),stavalues1),
69 stavalues4 = array_cat(array_cat(array_cat(stavalues1,stavalues1),stavalues1),stavalues1)
70 ,stavalues5 = array_cat(array_cat(array_cat(array_cat(stavalues1,stavalues1),stavalues1),stavalues1),stavalues1)
71 WHERE starelid = 'st0'::regclass;
72 SELECT dbms_stats.lock_table_stats('st0');
73 UPDATE dbms_stats.column_stats_locked SET
75 stanullfrac = -staattnum,
76 stawidth = -staattnum,
77 stadistinct = -staattnum,
88 stanumbers1 = ARRAY[-staattnum,22],
89 stanumbers2 = ARRAY[-staattnum,23],
90 stanumbers3 = ARRAY[-staattnum,24],
91 stanumbers4 = ARRAY[-staattnum,21],
92 stanumbers5 = ARRAY[-staattnum,25],
93 stavalues1 = stavalues3,
94 stavalues2 = stavalues2,
95 stavalues3 = stavalues1,
96 stavalues4 = stavalues4
97 ,stavalues5 = stavalues5
101 * Driver function dbms_stats.merge1
103 CREATE FUNCTION dbms_stats.merge1(
104 lhs dbms_stats.column_stats_locked,
105 rhs pg_catalog.pg_statistic
107 '$libdir/pg_dbms_stats', 'dbms_stats_merge'
110 SELECT * FROM columns_locked_v
111 WHERE starelid = 'st0'::regclass;
112 SELECT * FROM plain_columns_statistic_v
113 WHERE starelid = 'st0'::regclass;
115 SET client_min_messages TO LOG;
118 SELECT (m.merge).starelid::regclass,
120 (m.merge).stainherit,
121 (m.merge).stanullfrac,
123 (m.merge).stadistinct,
134 (m.merge).stanumbers1,
135 (m.merge).stanumbers2,
136 (m.merge).stanumbers3,
137 (m.merge).stanumbers4,
138 (m.merge).stanumbers5,
139 (m.merge).stavalues1,
140 (m.merge).stavalues2,
141 (m.merge).stavalues3,
143 ,(m.merge).stavalues5
144 FROM (SELECT dbms_stats.merge(NULL, s)
146 WHERE starelid = 'st0'::regclass
147 AND staattnum = '1'::int2) m;
150 SELECT (m.merge).starelid::regclass,
152 (m.merge).stainherit,
153 (m.merge).stanullfrac,
155 (m.merge).stadistinct,
166 (m.merge).stanumbers1,
167 (m.merge).stanumbers2,
168 (m.merge).stanumbers3,
169 (m.merge).stanumbers4,
170 (m.merge).stanumbers5,
171 (m.merge).stavalues1,
172 (m.merge).stavalues2,
173 (m.merge).stavalues3,
175 ,(m.merge).stavalues5
176 FROM (SELECT dbms_stats.merge(v, NULL)
177 FROM dbms_stats.column_stats_locked v
178 WHERE starelid = 'st0'::regclass
179 AND staattnum = '2'::int2) m;
182 SELECT dbms_stats.merge(NULL, NULL);
185 SELECT (m.merge).starelid::regclass,
187 (m.merge).stainherit,
188 (m.merge).stanullfrac,
190 (m.merge).stadistinct,
201 (m.merge).stanumbers1,
202 (m.merge).stanumbers2,
203 (m.merge).stanumbers3,
204 (m.merge).stanumbers4,
205 (m.merge).stanumbers5,
206 (m.merge).stavalues1,
207 (m.merge).stavalues2,
208 (m.merge).stavalues3,
210 ,(m.merge).stavalues5
211 FROM (SELECT dbms_stats.merge(v, s)
212 FROM dbms_stats.column_stats_locked v,
214 WHERE v.starelid = 'st0'::regclass
215 AND v.staattnum = '2'::int2
216 AND s.starelid = 'st0'::regclass
217 AND s.staattnum = '1'::int2) m;
220 SELECT (m.merge).starelid::regclass,
222 (m.merge).stainherit,
223 (m.merge).stanullfrac,
225 (m.merge).stadistinct,
236 (m.merge).stanumbers1,
237 (m.merge).stanumbers2,
238 (m.merge).stanumbers3,
239 (m.merge).stanumbers4,
240 (m.merge).stanumbers5,
241 (m.merge).stavalues1,
242 (m.merge).stavalues2,
243 (m.merge).stavalues3,
245 ,(m.merge).stavalues5
246 FROM (SELECT dbms_stats.merge(v, s)
247 FROM dbms_stats.column_stats_locked v,
249 WHERE v.starelid = 'st0'::regclass
250 AND v.staattnum = '2'::int2
251 AND s.starelid = 'st0'::regclass
252 AND s.staattnum = '1'::int2) m;
255 SELECT dbms_stats.merge1(v, s)
256 FROM dbms_stats.column_stats_locked v,
258 WHERE v.starelid = 'st0'::regclass
259 AND v.staattnum = '2'::int2
260 AND s.starelid = 'st0'::regclass
261 AND s.staattnum = '1'::int2;
264 SELECT dbms_stats.merge(NULL, (
265 s.starelid::regclass, s.staattnum, s.stainherit,
266 s.stanullfrac, s.stawidth, s.stadistinct,
267 s.stakind1, s.stakind2, s.stakind3, s.stakind4,
269 s.staop1, s.staop2, s.staop3,
271 NULL, s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
273 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
277 WHERE s.starelid = 'st0'::regclass
278 AND s.staattnum = '1'::int2;
281 SELECT (m.merge).starelid::regclass,
283 (m.merge).stainherit,
284 (m.merge).stanullfrac,
286 (m.merge).stadistinct,
297 (m.merge).stanumbers1,
298 (m.merge).stanumbers2,
299 (m.merge).stanumbers3,
300 (m.merge).stanumbers4,
301 (m.merge).stanumbers5,
302 (m.merge).stavalues1,
303 (m.merge).stavalues2,
304 (m.merge).stavalues3,
306 ,(m.merge).stavalues5
307 FROM (SELECT dbms_stats.merge(NULL, (
308 s.starelid::regclass, s.staattnum, s.stainherit,
309 s.stanullfrac, s.stawidth, s.stadistinct,
310 s.stakind1, s.stakind2, s.stakind3, s.stakind4,
312 s.staop1, s.staop2, s.staop3, s.staop4,
314 NULL, s.stanumbers2, s.stanumbers3, s.stanumbers4,
316 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
320 WHERE s.starelid = 'st0'::regclass
321 AND s.staattnum = '1'::int2) m;
324 SELECT dbms_stats.merge((
325 v.starelid::regclass, v.staattnum, v.stainherit,
326 v.stanullfrac, v.stawidth, v.stadistinct,
327 v.stakind1, v.stakind2, v.stakind3, v.stakind4,
329 v.staop1, v.staop2, v.staop3,
331 NULL, v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
333 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
336 FROM dbms_stats.column_stats_locked v
337 WHERE v.starelid = 'st0'::regclass
338 AND v.staattnum = '2'::int2;
341 SELECT (m.merge).starelid::regclass,
343 (m.merge).stainherit,
344 (m.merge).stanullfrac,
346 (m.merge).stadistinct,
357 (m.merge).stanumbers1,
358 (m.merge).stanumbers2,
359 (m.merge).stanumbers3,
360 (m.merge).stanumbers4,
361 (m.merge).stanumbers5,
362 (m.merge).stavalues1,
363 (m.merge).stavalues2,
364 (m.merge).stavalues3,
366 ,(m.merge).stavalues5
367 FROM (SELECT dbms_stats.merge((
368 v.starelid::regclass, v.staattnum, v.stainherit,
369 v.stanullfrac, v.stawidth, v.stadistinct,
370 v.stakind1, v.stakind2, v.stakind3, v.stakind4,
372 v.staop1, v.staop2, v.staop3, v.staop4,
374 NULL, v.stanumbers2, v.stanumbers3, v.stanumbers4,
376 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
379 FROM dbms_stats.column_stats_locked v
380 WHERE v.starelid = 'st0'::regclass
381 AND v.staattnum = '2'::int2) m;
384 SELECT dbms_stats.merge((
385 v.starelid::regclass, v.staattnum, v.stainherit,
386 v.stanullfrac, v.stawidth, v.stadistinct,
387 v.stakind1, v.stakind2, v.stakind3, v.stakind4,
389 v.staop1, v.staop2, v.staop3,
391 NULL, v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
393 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
396 s.starelid::regclass, s.staattnum, s.stainherit,
397 s.stanullfrac, s.stawidth, s.stadistinct,
398 s.stakind1, s.stakind2, s.stakind3, s.stakind4,
400 s.staop1, s.staop2, s.staop3,
402 NULL, s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
404 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
407 FROM dbms_stats.column_stats_locked v,
409 WHERE v.starelid = 'st0'::regclass
410 AND v.staattnum = '2'::int2
411 AND s.starelid = 'st0'::regclass
412 AND s.staattnum = '1'::int2;
415 SELECT (m.merge).starelid::regclass,
417 (m.merge).stainherit,
418 (m.merge).stanullfrac,
420 (m.merge).stadistinct,
431 (m.merge).stanumbers1,
432 (m.merge).stanumbers2,
433 (m.merge).stanumbers3,
434 (m.merge).stanumbers4,
435 (m.merge).stanumbers5,
436 (m.merge).stavalues1,
437 (m.merge).stavalues2,
438 (m.merge).stavalues3,
440 ,(m.merge).stavalues5
441 FROM (SELECT dbms_stats.merge((
442 v.starelid::regclass, v.staattnum, v.stainherit,
443 v.stanullfrac, v.stawidth, v.stadistinct,
444 v.stakind1, v.stakind2, v.stakind3, v.stakind4,
446 v.staop1, v.staop2, v.staop3, v.staop4,
448 NULL, v.stanumbers2, v.stanumbers3, v.stanumbers4,
450 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
453 s.starelid::regclass, s.staattnum, s.stainherit,
454 s.stanullfrac, s.stawidth, s.stadistinct,
455 s.stakind1, s.stakind2, s.stakind3, s.stakind4,
457 s.staop1, s.staop2, s.staop3, s.staop4,
459 NULL, s.stanumbers2, s.stanumbers3, s.stanumbers4,
461 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
464 FROM dbms_stats.column_stats_locked v,
466 WHERE v.starelid = 'st0'::regclass
467 AND v.staattnum = '2'::int2
468 AND s.starelid = 'st0'::regclass
469 AND s.staattnum = '1'::int2) m;
472 SELECT (m.merge).starelid::regclass,
474 (m.merge).stainherit,
475 (m.merge).stanullfrac,
477 (m.merge).stadistinct,
488 (m.merge).stanumbers1,
489 (m.merge).stanumbers2,
490 (m.merge).stanumbers3,
491 (m.merge).stanumbers4,
492 (m.merge).stanumbers5,
493 (m.merge).stavalues1,
494 (m.merge).stavalues2,
495 (m.merge).stavalues3,
497 ,(m.merge).stavalues5
498 FROM (SELECT dbms_stats.merge((
501 NULL, NULL, NULL, NULL,
502 NULL, NULL, NULL, NULL,
503 NULL, NULL, NULL, NULL,
504 NULL, NULL, NULL, NULL
505 ,NULL, NULL, NULL, NULL
508 WHERE s.starelid = 'st0'::regclass
509 AND s.staattnum = '1'::int2) m;
512 SELECT (m.merge).starelid::regclass,
514 (m.merge).stainherit,
515 (m.merge).stanullfrac,
517 (m.merge).stadistinct,
528 (m.merge).stanumbers1,
529 (m.merge).stanumbers2,
530 (m.merge).stanumbers3,
531 (m.merge).stanumbers4,
532 (m.merge).stanumbers5,
533 (m.merge).stavalues1,
534 (m.merge).stavalues2,
535 (m.merge).stavalues3,
537 ,(m.merge).stavalues5
538 FROM (SELECT dbms_stats.merge(v, (
541 NULL, NULL, NULL, NULL,
542 NULL, NULL, NULL, NULL,
543 NULL, NULL, NULL, NULL,
544 NULL, NULL, NULL, NULL,
545 NULL, NULL, NULL, NULL))
546 FROM dbms_stats.column_stats_locked v
547 WHERE v.starelid = 'st0'::regclass
548 AND v.staattnum = '2'::int2) m;
551 SELECT (m.merge).starelid::regclass,
553 (m.merge).stainherit,
554 (m.merge).stanullfrac,
556 (m.merge).stadistinct,
567 (m.merge).stanumbers1,
568 (m.merge).stanumbers2,
569 (m.merge).stanumbers3,
570 (m.merge).stanumbers4,
571 (m.merge).stanumbers5,
572 (m.merge).stavalues1,
573 (m.merge).stavalues2,
574 (m.merge).stavalues3,
576 ,(m.merge).stavalues5
577 FROM (SELECT dbms_stats.merge(v, s)
578 FROM dbms_stats.column_stats_locked v,
580 WHERE v.starelid = 'st0'::regclass
581 AND v.staattnum = '2'::int2
582 AND s.starelid = 'st0'::regclass
583 AND s.staattnum = '1'::int2) m;
586 SELECT dbms_stats.merge((
587 v.starelid::regclass, v.staattnum, v.stainherit,
588 v.stanullfrac, v.stawidth, v.stadistinct,
589 NULL, NULL, NULL, NULL,
590 NULL, NULL, NULL, NULL,
591 NULL, NULL, NULL, NULL,
592 NULL, NULL, NULL, NULL
593 ,NULL, NULL, NULL, NULL
595 s.starelid::regclass, s.staattnum, s.stainherit,
596 s.stanullfrac, s.stawidth, s.stadistinct,
597 NULL, NULL, NULL, NULL,
598 NULL, NULL, NULL, NULL,
599 NULL, NULL, NULL, NULL,
600 NULL, NULL, NULL, NULL,
601 NULL, NULL, NULL, NULL))
602 FROM dbms_stats.column_stats_locked v,
604 WHERE v.starelid = 'st0'::regclass
605 AND v.staattnum = '2'::int2
606 AND s.starelid = 'st0'::regclass
607 AND s.staattnum = '1'::int2;
610 SELECT (m.merge).starelid::regclass,
612 (m.merge).stainherit,
613 (m.merge).stanullfrac,
615 (m.merge).stadistinct,
626 (m.merge).stanumbers1,
627 (m.merge).stanumbers2,
628 (m.merge).stanumbers3,
629 (m.merge).stanumbers4,
630 (m.merge).stanumbers5,
631 (m.merge).stavalues1,
632 (m.merge).stavalues2,
633 (m.merge).stavalues3,
635 ,(m.merge).stavalues5
636 FROM (SELECT dbms_stats.merge((
637 v.starelid::regclass, v.staattnum, v.stainherit,
638 v.stanullfrac, v.stawidth, v.stadistinct,
639 v.stakind1, v.stakind2, v.stakind3, v.stakind4,
641 v.staop1, v.staop2, v.staop3,
643 NULL, v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
645 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
648 s.starelid::regclass, s.staattnum, s.stainherit,
649 s.stanullfrac, s.stawidth, s.stadistinct,
650 s.stakind1, s.stakind2, s.stakind3, s.stakind4,
652 s.staop1, s.staop2, s.staop3, s.staop4,
654 s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
656 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
659 FROM dbms_stats.column_stats_locked v,
661 WHERE v.starelid = 'st0'::regclass
662 AND v.staattnum = '1'::int2
663 AND s.starelid = 'st0'::regclass
664 AND s.staattnum = '1'::int2) m;
667 SELECT dbms_stats.merge((
668 v.starelid::regclass, v.staattnum, v.stainherit,
669 v.stanullfrac, v.stawidth, v.stadistinct,
670 v.stakind1, v.stakind2, v.stakind3, v.stakind4,
672 v.staop1, v.staop2, v.staop3,
674 NULL, v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
676 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
679 s.starelid::regclass, s.staattnum, s.stainherit,
680 s.stanullfrac, s.stawidth, s.stadistinct,
681 s.stakind1, s.stakind2, s.stakind3, s.stakind4,
683 s.staop1, s.staop2, s.staop3,
685 NULL, s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
687 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
690 FROM dbms_stats.column_stats_locked v,
692 WHERE v.starelid = 'st0'::regclass
693 AND v.staattnum = '1'::int2
694 AND s.starelid = 'st0'::regclass
695 AND s.staattnum = '1'::int2;
698 SELECT (m.merge).starelid::regclass,
700 (m.merge).stainherit,
701 (m.merge).stanullfrac,
703 (m.merge).stadistinct,
714 (m.merge).stanumbers1,
715 (m.merge).stanumbers2,
716 (m.merge).stanumbers3,
717 (m.merge).stanumbers4,
718 (m.merge).stanumbers5,
719 (m.merge).stavalues1,
720 (m.merge).stavalues2,
721 (m.merge).stavalues3,
723 ,(m.merge).stavalues5
724 FROM (SELECT dbms_stats.merge((
725 v.starelid::regclass, v.staattnum, v.stainherit,
726 v.stanullfrac, v.stawidth, v.stadistinct,
729 v.staop1, v.staop2, v.staop3, v.staop4,
731 v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
733 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
736 s.starelid::regclass, s.staattnum, s.stainherit,
737 s.stanullfrac, s.stawidth, s.stadistinct,
740 s.staop1, s.staop2, s.staop3, s.staop4,
742 s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
744 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
747 FROM dbms_stats.column_stats_locked v,
749 WHERE v.starelid = 'st0'::regclass
750 AND v.staattnum = '2'::int2
751 AND s.starelid = 'st0'::regclass
752 AND s.staattnum = '1'::int2) m;
755 SELECT (m.merge).starelid::regclass,
757 (m.merge).stainherit,
758 (m.merge).stanullfrac,
760 (m.merge).stadistinct,
771 (m.merge).stanumbers1,
772 (m.merge).stanumbers2,
773 (m.merge).stanumbers3,
774 (m.merge).stanumbers4,
775 (m.merge).stanumbers5,
776 (m.merge).stavalues1,
777 (m.merge).stavalues2,
778 (m.merge).stavalues3,
780 ,(m.merge).stavalues5
781 FROM (SELECT dbms_stats.merge((v.starelid::regclass, v.staattnum, v.stainherit,
782 v.stanullfrac, v.stawidth, v.stadistinct,
785 v.staop1, v.staop2, v.staop3, v.staop4,
787 v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
789 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
792 s.starelid::regclass, s.staattnum, s.stainherit,
793 s.stanullfrac, s.stawidth, s.stadistinct,
796 s.staop1, s.staop2, s.staop3, s.staop4,
798 s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
800 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
803 FROM dbms_stats.column_stats_locked v,
805 WHERE v.starelid = 'st0'::regclass
806 AND v.staattnum = '2'::int2
807 AND s.starelid = 'st0'::regclass
808 AND s.staattnum = '1'::int2) m;
811 SELECT dbms_stats.merge((v.starelid::regclass, '2', v.stainherit,
812 v.stanullfrac, v.stawidth, v.stadistinct,
815 v.staop1, v.staop2, v.staop3, v.staop4,
817 v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
819 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
822 s.starelid::regclass, s.staattnum, s.stainherit,
823 s.stanullfrac, s.stawidth, s.stadistinct,
826 s.staop1, s.staop2, s.staop3, s.staop4,
828 s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
830 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
833 FROM dbms_stats.column_stats_locked v,
835 WHERE v.starelid = 'st0'::regclass
836 AND v.staattnum = '1'::int2
837 AND s.starelid = 'st0'::regclass
838 AND s.staattnum = '1'::int2;
841 SELECT dbms_stats.merge((v.starelid::regclass, '2', v.stainherit,
842 v.stanullfrac, v.stawidth, v.stadistinct,
845 v.staop1, v.staop2, v.staop3, v.staop4,
847 v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
849 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
852 s.starelid::regclass, s.staattnum, s.stainherit,
853 s.stanullfrac, s.stawidth, s.stadistinct,
856 s.staop1, s.staop2, s.staop3, s.staop4,
858 s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
860 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
863 FROM dbms_stats.column_stats_locked v,
865 WHERE v.starelid = 'st0'::regclass
866 AND v.staattnum = '1'::int2
867 AND s.starelid = 'st0'::regclass
868 AND s.staattnum = '1'::int2;
869 RESET client_min_messages;
870 SELECT dbms_stats.unlock_database_stats();
873 * No.6-4 dbms_stats.is_target_relkind
876 SELECT dbms_stats.is_target_relkind(k::"char")
877 FROM (VALUES ('r'), ('i'), ('f'), ('m'),
878 ('S'), ('t'), ('v'), ('c')) t(k);
881 * No.7-1 dbms_stats.backup
883 DELETE FROM dbms_stats.backup_history;
884 INSERT INTO dbms_stats.backup_history(id, time, unit) values(1, '2012-01-01', 'd');
886 SELECT dbms_stats.backup(1, 's0.sft0'::regclass, NULL);
887 SELECT count(*) FROM dbms_stats.relation_stats_backup;
888 SELECT count(*) FROM dbms_stats.column_stats_backup;
891 -- #### 9.2 doesn't has materialized views
892 -- DELETE FROM dbms_stats.relation_stats_backup;
893 -- SELECT dbms_stats.backup(1, 's0.smv0'::regclass, NULL);
894 -- SELECT count(*) FROM dbms_stats.relation_stats_backup;
895 -- SELECT count(*) FROM dbms_stats.column_stats_backup;
898 DELETE FROM dbms_stats.relation_stats_backup;
899 SELECT dbms_stats.backup(1, NULL, 1::int2);
900 SELECT relid::regclass FROM dbms_stats.relation_stats_backup
903 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_backup
904 GROUP BY starelid, staattnum
905 ORDER BY starelid, staattnum;
908 DELETE FROM dbms_stats.relation_stats_backup;
909 SELECT dbms_stats.backup(1, NULL::regclass, NULL);
910 SELECT relid::regclass FROM dbms_stats.relation_stats_backup
913 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_backup
914 GROUP BY starelid, staattnum
915 ORDER BY starelid, staattnum;
918 DELETE FROM dbms_stats.relation_stats_backup;
919 \! psql contrib_regression -c "SELECT dbms_stats.backup(NULL, 's0.st0'::regclass, NULL)" > results/ut_no2_1_17.out 2>&1
920 SELECT count(*) FROM dbms_stats.relation_stats_backup;
921 SELECT count(*) FROM dbms_stats.column_stats_backup;
924 * No.8-1 dbms_stats.backup
926 SELECT setval('dbms_stats.backup_history_id_seq',1, false);
928 * Stab function dbms_stats.backup
930 ALTER FUNCTION dbms_stats.backup(
934 RENAME TO truth_func_backup;
936 CREATE OR REPLACE FUNCTION dbms_stats.backup(
943 RAISE NOTICE 'arguments are %, %, %', $1, $2, $3;
950 DELETE FROM dbms_stats.backup_history;
951 SELECT dbms_stats.backup('s0.st0'::regclass, 'id', 'dummy comment');
952 SELECT id, unit, comment FROM dbms_stats.backup_history;
955 DELETE FROM dbms_stats.backup_history;
956 SELECT dbms_stats.backup('s0.st0'::regclass, NULL, 'dummy comment');
957 SELECT id, unit, comment FROM dbms_stats.backup_history;
960 DELETE FROM dbms_stats.backup_history;
961 SELECT dbms_stats.backup(NULL::regclass, 'id', 'dummy comment');
962 SELECT id, unit, comment FROM dbms_stats.backup_history;
965 DELETE FROM dbms_stats.backup_history;
966 SELECT dbms_stats.backup(NULL::regclass, NULL, 'dummy comment');
967 SELECT id, unit, comment FROM dbms_stats.backup_history;
970 DELETE FROM dbms_stats.backup_history;
971 SELECT dbms_stats.backup(0, NULL, 'dummy comment');
972 SELECT id, unit, comment FROM dbms_stats.backup_history;
975 DELETE FROM dbms_stats.backup_history;
976 SELECT dbms_stats.backup('s0.st0'::regclass, NULL, 'dummy comment');
977 SELECT id, unit, comment FROM dbms_stats.backup_history;
980 DELETE FROM dbms_stats.backup_history;
981 SELECT dbms_stats.backup(
982 'pg_toast.pg_toast_2618'::regclass,
985 SELECT id, unit, comment FROM dbms_stats.backup_history;
988 DELETE FROM dbms_stats.backup_history;
989 SELECT dbms_stats.backup('s0.st0_idx'::regclass, NULL, 'dummy comment');
990 SELECT id, unit, comment FROM dbms_stats.backup_history;
993 DELETE FROM dbms_stats.backup_history;
994 SELECT dbms_stats.backup('s0.ss0'::regclass, NULL, 'dummy comment');
995 SELECT id, unit, comment FROM dbms_stats.backup_history;
998 DELETE FROM dbms_stats.backup_history;
999 SELECT dbms_stats.backup('s0.sc0'::regclass, NULL, 'dummy comment');
1000 SELECT id, unit, comment FROM dbms_stats.backup_history;
1003 DELETE FROM dbms_stats.backup_history;
1004 SELECT dbms_stats.backup('s0.sft0'::regclass, NULL, 'dummy comment');
1005 SELECT id, unit, comment FROM dbms_stats.backup_history;
1008 -- #### 9.2 doesn't has materialized views
1009 -- DELETE FROM dbms_stats.backup_history;
1010 -- SELECT dbms_stats.backup('s0.smv0'::regclass, NULL, 'dummy comment');
1011 -- SELECT id, unit, comment FROM dbms_stats.backup_history;
1014 DELETE FROM dbms_stats.backup_history;
1015 SELECT dbms_stats.backup('pg_catalog.pg_class'::regclass, NULL, 'dummy comment');
1016 SELECT id, unit, comment FROM dbms_stats.backup_history;
1019 DELETE FROM dbms_stats.backup_history;
1020 SELECT dbms_stats.backup('s0.st0'::regclass, 'dummy', 'dummy comment');
1021 SELECT id, unit, comment FROM dbms_stats.backup_history;
1024 DELETE FROM dbms_stats.backup_history;
1025 DELETE FROM pg_statistic
1026 WHERE starelid = 's0.st0'::regclass
1027 AND staattnum = 1::int2;
1028 SELECT count(*) FROM dbms_stats.column_stats_effective
1029 WHERE starelid = 's0.st0'::regclass
1030 AND staattnum = 1::int2;
1031 SELECT dbms_stats.backup('s0.st0'::regclass, 'id', 'dummy comment');
1032 SELECT id, unit, comment FROM dbms_stats.backup_history;
1035 * Stab function dbms_stats.backup
1037 ALTER FUNCTION dbms_stats.backup(
1041 RENAME TO truth_func_backup;
1042 CREATE OR REPLACE FUNCTION dbms_stats.backup(
1043 relid regclass DEFAULT NULL,
1044 attname text DEFAULT NULL,
1045 comment text DEFAULT NULL)
1049 IF $3 = '<NULL>' THEN
1050 RAISE NOTICE 'third argument is not NULL but string "<NULL>"';
1052 RAISE NOTICE 'arguments are %, %, %', $1, $2, $3;
1059 * No.8-3 dbms_stats.backup_schema_stats
1061 SELECT setval('dbms_stats.backup_history_id_seq',9, false);
1063 SELECT dbms_stats.backup_schema_stats('s0', 'comment');
1064 SELECT id, unit, comment FROM dbms_stats.backup_history
1068 SELECT dbms_stats.backup_schema_stats('s00', 'comment');
1069 SELECT id, unit, comment FROM dbms_stats.backup_history
1073 SELECT dbms_stats.backup_schema_stats('pg_catalog', 'comment');
1074 SELECT id, unit, comment FROM dbms_stats.backup_history
1079 * Delete stab function dbms_stats.backup
1081 DROP FUNCTION dbms_stats.backup(
1085 ALTER FUNCTION dbms_stats.truth_func_backup(
1090 DROP FUNCTION dbms_stats.backup(
1094 ALTER FUNCTION dbms_stats.truth_func_backup(
1102 * create backup statistics state A
1104 DELETE FROM dbms_stats.backup_history;
1106 INSERT INTO dbms_stats.backup_history(id, time, unit)
1107 VALUES (1, '2012-02-29 23:59:56.999999', 'd');
1109 SELECT setval('dbms_stats.backup_history_id_seq',1);
1110 SELECT dbms_stats.backup();
1111 UPDATE dbms_stats.backup_history
1112 SET time = '2012-02-29 23:59:57'
1114 SELECT dbms_stats.backup('s0.st0');
1115 UPDATE dbms_stats.backup_history
1116 SET time = '2012-02-29 23:59:57.000001'
1118 SELECT dbms_stats.backup();
1119 UPDATE dbms_stats.backup_history
1120 SET time = '2012-02-29 23:59:58'
1122 DELETE FROM dbms_stats.relation_stats_backup
1124 SELECT dbms_stats.backup('s0.st0', 'id');
1125 UPDATE dbms_stats.backup_history
1126 SET time = '2012-03-01 00:00:00'
1128 SELECT dbms_stats.backup('s0.st0');
1129 UPDATE dbms_stats.backup_history
1130 SET time = '2012-03-01 00:00:02'
1132 SELECT dbms_stats.backup('public.st0');
1133 UPDATE dbms_stats.backup_history
1134 SET time = '2012-03-01 00:00:04'
1136 INSERT INTO dbms_stats.backup_history(time, unit)
1137 VALUES ('2012-03-01 00:00:06', 's');
1138 SELECT dbms_stats.backup(8, c.oid, NULL)
1139 FROM pg_catalog.pg_class c,
1140 pg_catalog.pg_namespace n
1141 WHERE n.nspname = 's0'
1142 AND c.relnamespace = n.oid
1143 AND c.relkind IN ('r', 'i');
1145 SELECT * FROM dbms_stats.backup_history
1147 SELECT * FROM relations_backup_v;
1148 SELECT * FROM columns_backup_v;
1153 * No.9-1 dbms_stats.restore
1156 DELETE FROM dbms_stats.relation_stats_locked;
1157 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
1159 SELECT * FROM internal_locks;
1160 SELECT dbms_stats.restore(2, 's0.st0', NULL);
1161 SELECT * FROM internal_locks;
1163 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1166 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1169 DELETE FROM dbms_stats.relation_stats_locked;
1170 SELECT dbms_stats.restore(2, 'st0', NULL);
1171 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1174 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1177 DELETE FROM dbms_stats.relation_stats_locked;
1178 SELECT dbms_stats.restore(2, 's00.s0', NULL);
1179 SELECT count(*) FROM dbms_stats.column_stats_locked;
1180 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1183 DELETE FROM dbms_stats.relation_stats_locked;
1184 SELECT dbms_stats.restore(NULL, 's0.st0', NULL);
1185 SELECT count(*) FROM dbms_stats.column_stats_locked;
1186 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1189 DELETE FROM dbms_stats.relation_stats_locked;
1190 SELECT dbms_stats.restore(2, 's0.st0', 'id');
1191 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1194 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1197 DELETE FROM dbms_stats.relation_stats_locked;
1198 SELECT dbms_stats.restore(2, NULL, 'id');
1199 SELECT count(*) FROM dbms_stats.column_stats_locked;
1200 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1203 DELETE FROM dbms_stats.relation_stats_locked;
1204 SELECT dbms_stats.restore(2, 's0.st0', NULL);
1205 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1208 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1211 DELETE FROM dbms_stats.relation_stats_locked;
1212 SELECT dbms_stats.restore(2, NULL, NULL);
1213 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1216 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1219 DELETE FROM dbms_stats.relation_stats_locked;
1220 SELECT dbms_stats.restore(0, 's0.st0', NULL);
1221 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1224 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1227 DELETE FROM dbms_stats.relation_stats_locked;
1228 SELECT dbms_stats.restore(2, 0, 'id');
1229 SELECT count(*) FROM dbms_stats.column_stats_locked;
1230 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1233 DELETE FROM dbms_stats.relation_stats_locked;
1234 SELECT dbms_stats.restore(1, 's0.st0', NULL);
1235 SELECT count(*) FROM dbms_stats.column_stats_locked;
1236 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1239 DELETE FROM dbms_stats.relation_stats_locked;
1240 SELECT dbms_stats.restore(2, 's0.st0', 'dummy');
1241 SELECT count(*) FROM dbms_stats.column_stats_locked;
1242 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1245 DELETE FROM dbms_stats.relation_stats_locked;
1246 SELECT dbms_stats.restore(1, 's0.st0', 'id');
1247 SELECT count(*) FROM dbms_stats.column_stats_locked;
1248 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1251 DELETE FROM dbms_stats.relation_stats_locked;
1252 ALTER TABLE s1.st0 DROP COLUMN id;
1253 SELECT dbms_stats.restore(2, 's1.st0', 'id');
1254 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1257 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1260 DELETE FROM dbms_stats.relation_stats_locked;
1261 \set s1_st0_oid `psql contrib_regression -tA -c "SELECT c.oid FROM pg_class c, pg_namespace n WHERE c.relnamespace = n.oid AND n.nspname = 's1' AND c.relname = 'st0';"`
1263 -- SELECT dbms_stats.restore(2, :s1_st0_oid, NULL);
1264 -- To avoid test unstability caused by relation id alloction, the test
1265 -- above is omitted.
1267 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1268 SELECT count(*) FROM dbms_stats.column_stats_locked;
1269 CREATE TABLE s1.st0(id integer, num integer);
1270 INSERT INTO s1.st0 VALUES (1, 15), (2, 25), (3, 35), (4, 45);
1273 DELETE FROM dbms_stats.relation_stats_locked;
1274 SELECT dbms_stats.restore(2, 's0.st0', NULL);
1275 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1278 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1281 DELETE FROM dbms_stats.relation_stats_locked;
1282 INSERT INTO dbms_stats.relation_stats_backup(
1283 id, relid, relname, relpages, reltuples,
1287 'pg_toast.pg_toast_2618'::regclass,
1288 'pg_toast.pg_toast_2618', 1, 1,
1291 SELECT * FROM relations_backup_v
1293 AND relname = 'pg_toast.pg_toast_2618';
1294 SELECT dbms_stats.restore(2, 'pg_toast.pg_toast_2618', NULL);
1295 SELECT count(*) FROM dbms_stats.column_stats_locked;
1296 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1297 DELETE FROM dbms_stats.relation_stats_backup
1299 AND relname = 'pg_toast.pg_toast_2618';
1302 DELETE FROM dbms_stats.relation_stats_locked;
1303 SELECT dbms_stats.restore(2, 's0.st0_idx', NULL);
1304 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1307 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1310 DELETE FROM dbms_stats.relation_stats_locked;
1311 INSERT INTO dbms_stats.relation_stats_backup(
1312 id, relid, relname, relpages, reltuples,
1315 VALUES (2, 's0.ss0'::regclass, 's0.ss0', 1, 1,
1318 SELECT * FROM relations_backup_v
1320 AND relname = 's0.ss0';
1321 SELECT dbms_stats.restore(2, 's0.ss0', NULL);
1322 SELECT count(*) FROM dbms_stats.column_stats_locked;
1323 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1324 DELETE FROM dbms_stats.relation_stats_backup
1326 AND relname = 's0.ss0';
1329 DELETE FROM dbms_stats.relation_stats_locked;
1330 INSERT INTO dbms_stats.relation_stats_backup(
1331 id, relid, relname, relpages, reltuples,
1334 VALUES (2, 's0.sc0'::regclass, 's0.sc0', 1, 1,
1337 SELECT * FROM relations_backup_v
1339 AND relname = 's0.sc0';
1340 SELECT dbms_stats.restore(2, 's0.sc0', NULL);
1341 SELECT count(*) FROM dbms_stats.column_stats_locked;
1342 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1343 DELETE FROM dbms_stats.relation_stats_backup
1345 AND relname = 's0.sc0';
1348 DELETE FROM dbms_stats.relation_stats_locked;
1349 INSERT INTO dbms_stats.relation_stats_backup(
1350 id, relid, relname, relpages, reltuples,
1353 VALUES (3, 's0.sft0'::regclass, 's0.sft0', 1, 1,
1356 SELECT * FROM relations_backup_v
1358 AND relname = 's0.sft0';
1359 SELECT dbms_stats.restore(2, 's0.sft0', NULL);
1360 SELECT count(*) FROM dbms_stats.column_stats_locked;
1361 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1362 DELETE FROM dbms_stats.relation_stats_backup
1364 AND relname = 's0.sft0';
1367 -- #### 9.2 doesn't has materialized views
1368 -- DELETE FROM dbms_stats.relation_stats_locked;
1369 -- INSERT INTO dbms_stats.relation_stats_backup(
1370 -- id, relid, relname, relpages, reltuples,
1373 -- VALUES (3, 's0.smv0'::regclass, 's0.smv0', 1, 1,
1376 -- SELECT * FROM relations_backup_v
1378 -- AND relname = 's0.smv0';
1379 -- SELECT dbms_stats.restore(2, 's0.smv0', NULL);
1380 -- SELECT count(*) FROM dbms_stats.column_stats_locked;
1381 -- SELECT count(*) FROM dbms_stats.relation_stats_locked;
1382 -- DELETE FROM dbms_stats.relation_stats_backup
1384 -- AND relname = 's0.smv0';
1387 DELETE FROM dbms_stats.relation_stats_locked;
1388 INSERT INTO dbms_stats.relation_stats_backup(
1389 id, relid, relname, relpages, reltuples,
1392 VALUES (2, 'pg_catalog.pg_class'::regclass, 'pg_catalog.pg_class', 1, 1,
1395 SELECT * FROM relations_backup_v
1397 AND relname = 'pg_catalog.pg_class';
1398 SELECT dbms_stats.restore(2, 'pg_catalog.pg_class', NULL);
1399 SELECT count(*) FROM dbms_stats.column_stats_locked;
1400 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1401 DELETE FROM dbms_stats.relation_stats_backup
1403 AND relname = 'pg_catalog.pg_class';
1406 DELETE FROM dbms_stats.relation_stats_locked;
1407 INSERT INTO dbms_stats.relation_stats_locked(relid, relname)
1408 VALUES ('s0.st0'::regclass, 's0.st0');
1409 INSERT INTO dbms_stats.column_stats_locked(starelid, staattnum, stainherit)
1410 SELECT starelid::regclass, staattnum, stainherit
1411 FROM dbms_stats.column_stats_effective
1412 WHERE starelid = 's0.st0'::regclass;
1413 SELECT id, unit, comment FROM dbms_stats.backup_history
1415 SELECT * FROM columns_locked_v;
1416 SELECT * FROM relations_locked_v;
1417 SELECT dbms_stats.restore(2, 's0.st0', NULL);
1418 SELECT * FROM relations_locked_v;
1419 SELECT * FROM columns_locked_v;
1422 DELETE FROM dbms_stats.relation_stats_locked;
1423 SELECT id, unit, comment FROM dbms_stats.backup_history
1425 SELECT dbms_stats.restore(2, 's0.st0', NULL);
1426 SELECT * FROM relations_locked_v;
1427 SELECT * FROM columns_locked_v;
1430 * Stab function dbms_stats.restore
1432 CREATE OR REPLACE FUNCTION dbms_stats.restore(
1434 relid regclass DEFAULT NULL,
1435 attname text DEFAULT NULL)
1436 RETURNS SETOF regclass AS
1439 RAISE NOTICE 'arguments are "%, %, %"', $1, $2, $3;
1441 SELECT c.oid::regclass
1442 FROM pg_class c, dbms_stats.relation_stats_backup b
1443 WHERE (c.oid = $2 OR $2 IS NULL)
1445 AND c.relkind IN ('r', 'i')
1446 AND (b.id <= $1 OR $1 IS NOT NULL)
1448 ORDER BY c.oid::regclass::text;
1454 * No.10-1 dbms_stats.restore_database_stats
1457 SELECT dbms_stats.restore_database_stats('2012-02-29 23:59:57');
1459 SELECT dbms_stats.restore_database_stats('2012-02-29 23:59:57.000002');
1461 SELECT dbms_stats.restore_database_stats('2012-01-01 00:00:00');
1462 --#No.10-1-4 is skipped after lock tests
1463 --#No.10-1-5 is skipped after lock tests
1465 SELECT dbms_stats.restore_database_stats('2012-02-29 23:59:57');
1468 * No.10-2 dbms_stats.restore_schema_stats
1471 SELECT dbms_stats.restore_schema_stats('s0', '2012-02-29 23:59:57');
1473 SELECT dbms_stats.restore_schema_stats('s0', '2012-02-29 23:59:57.000002');
1475 SELECT dbms_stats.restore_schema_stats('s0', '2012-01-01 00:00:00');
1476 --#No.10-2-4 is skipped after lock tests
1477 --#No.10-2-5 is skipped after lock tests
1479 SELECT dbms_stats.restore_schema_stats('s0', '2012-02-29 23:59:57');
1481 SELECT dbms_stats.restore_schema_stats('s0', '2012-02-29 23:59:57');
1482 --#No.10-2-8 is skipped after lock tests
1484 SELECT dbms_stats.restore_schema_stats('s00', '2012-02-29 23:59:57');
1486 SELECT dbms_stats.restore_schema_stats('pg_catalog', '2012-02-29 23:59:57');
1489 * No.10-7 dbms_stats.restore_stats
1492 DELETE FROM dbms_stats.relation_stats_locked;
1493 SELECT dbms_stats.restore_stats(NULL);
1496 DELETE FROM dbms_stats.relation_stats_locked;
1497 SELECT dbms_stats.restore_stats(0);
1500 DELETE FROM dbms_stats.relation_stats_locked;
1501 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
1503 SELECT * FROM internal_locks;
1504 SELECT dbms_stats.restore_stats(2);
1505 SELECT * FROM internal_locks;
1507 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1510 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1513 DELETE FROM dbms_stats.relation_stats_locked;
1514 INSERT INTO dbms_stats.relation_stats_locked(relid, relname)
1515 SELECT relid::regclass, relname
1516 FROM dbms_stats.relation_stats_effective;
1517 INSERT INTO dbms_stats.column_stats_locked(starelid, staattnum, stainherit)
1518 SELECT starelid::regclass, staattnum, stainherit
1519 FROM dbms_stats.column_stats_effective;
1520 SELECT id, unit, comment FROM dbms_stats.backup_history
1522 SELECT * FROM columns_locked_v;
1523 SELECT * FROM relations_locked_v;
1524 SELECT dbms_stats.restore_stats(8);
1525 SELECT * FROM relations_locked_v;
1526 SELECT * FROM columns_locked_v;
1529 DELETE FROM dbms_stats.relation_stats_locked;
1530 SELECT id, unit, comment FROM dbms_stats.backup_history
1532 SELECT dbms_stats.restore_stats(8);
1533 SELECT * FROM relations_locked_v;
1534 SELECT * FROM columns_locked_v;
1537 * No.11-1 dbms_stats.lock(relid, attname)
1540 DELETE FROM dbms_stats.relation_stats_locked;
1541 SELECT dbms_stats.lock(NULL, NULL);
1543 ALTER FUNCTION dbms_stats.lock(relid regclass)
1544 RENAME TO truth_lock;
1545 CREATE FUNCTION dbms_stats.lock(relid regclass)
1549 RAISE NOTICE 'arguments are %', $1;
1554 DELETE FROM dbms_stats.relation_stats_locked;
1555 SELECT dbms_stats.lock('s0.st0', NULL);
1556 DROP FUNCTION dbms_stats.lock(relid regclass);
1557 ALTER FUNCTION dbms_stats.truth_lock(relid regclass)
1560 DELETE FROM dbms_stats.relation_stats_locked;
1561 SELECT dbms_stats.lock(NULL, 'id');
1563 DELETE FROM dbms_stats.relation_stats_locked;
1564 SELECT dbms_stats.lock('s0.st0', 'id');
1565 SELECT * FROM relations_locked_v;
1566 SELECT * FROM columns_locked_v c;
1568 DELETE FROM dbms_stats.relation_stats_locked;
1569 SELECT dbms_stats.lock(0, 'id');
1571 DELETE FROM dbms_stats.relation_stats_locked;
1572 SELECT dbms_stats.lock('s0.st0', 'id');
1573 SELECT * FROM relations_locked_v;
1574 SELECT * FROM columns_locked_v c;
1576 DELETE FROM dbms_stats.relation_stats_locked;
1577 SELECT dbms_stats.lock('pg_toast.pg_toast_2618', 'id');
1579 DELETE FROM dbms_stats.relation_stats_locked;
1580 SELECT dbms_stats.lock('s0.st0_idx', 'id');
1582 DELETE FROM dbms_stats.relation_stats_locked;
1583 SELECT dbms_stats.lock('st1_exp', 'lower');
1584 SELECT * FROM relations_locked_v;
1585 SELECT * FROM columns_locked_v c;
1586 DELETE FROM dbms_stats.relation_stats_locked;
1589 DELETE FROM dbms_stats.relation_stats_locked;
1590 SELECT dbms_stats.lock('s0.ss0', 'id');
1592 DELETE FROM dbms_stats.relation_stats_locked;
1593 SELECT dbms_stats.lock('s0.sc0', 'id');
1595 DELETE FROM dbms_stats.relation_stats_locked;
1596 SELECT dbms_stats.lock('s0.sft0', 'id');
1597 SELECT * FROM relations_locked_v;
1598 SELECT * FROM columns_locked_v c;
1600 -- #### 9.2 doesn't has materialized views
1601 -- DELETE FROM dbms_stats.relation_stats_locked;
1602 -- SELECT dbms_stats.lock('s0.smv0', 'id');
1603 -- SELECT * FROM relations_locked_v;
1604 -- SELECT * FROM columns_locked_v c;
1606 DELETE FROM dbms_stats.relation_stats_locked;
1607 SELECT dbms_stats.lock('pg_catalog.pg_class', 'id');
1609 DELETE FROM dbms_stats.relation_stats_locked;
1610 SELECT dbms_stats.lock('s0.st0', 'dummy');
1612 DELETE FROM dbms_stats.relation_stats_locked;
1613 DELETE FROM pg_statistic
1614 WHERE starelid = 's0.st0'::regclass;
1615 SELECT dbms_stats.lock('s0.st0', 'id');
1618 DELETE FROM dbms_stats.relation_stats_locked;
1619 INSERT INTO dbms_stats.relation_stats_locked(
1620 relid, relname, relpages, reltuples,
1623 VALUES('s0.st0'::regclass, 's0.st0', 1, 1640,
1626 SELECT dbms_stats.lock_column_stats('s0.st0','id');
1627 UPDATE dbms_stats.column_stats_locked
1628 SET (stanullfrac, stawidth, stadistinct,
1629 stakind1, stakind2, stakind3, stakind4,
1631 staop1, staop2, staop3, staop4,
1633 stanumbers1, stanumbers2, stanumbers3, stanumbers4,
1635 stavalues1, stavalues2, stavalues3, stavalues4
1639 NULL, NULL, NULL, NULL,
1640 NULL, NULL, NULL, NULL,
1641 NULL, NULL, NULL, NULL,
1642 NULL, NULL, NULL, NULL,
1643 NULL, NULL, NULL, NULL)
1644 WHERE starelid = 's0.st0'::regclass;
1645 SELECT dbms_stats.lock('s0.st0', 'id');
1646 SELECT * FROM relations_locked_v;
1647 SELECT * FROM columns_locked_v c;
1649 DELETE FROM dbms_stats.relation_stats_locked;
1650 SELECT dbms_stats.lock('s0.st0', 'id');
1651 SELECT * FROM relations_locked_v
1652 WHERE relid = 's0.st0'::regclass;
1653 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1656 * No.11-2 dbms_stats.lock(relid)
1659 DELETE FROM dbms_stats.relation_stats_locked;
1660 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
1662 SELECT * FROM internal_locks;
1663 SELECT dbms_stats.lock('s0.st0');
1664 SELECT * FROM relations_locked_v;
1665 SELECT * FROM columns_locked_v c;
1666 SELECT * FROM internal_locks;
1670 DELETE FROM dbms_stats.relation_stats_locked;
1671 SELECT dbms_stats.lock(NULL);
1673 DELETE FROM dbms_stats.relation_stats_locked;
1674 SELECT dbms_stats.lock('0');
1676 DELETE FROM dbms_stats.relation_stats_locked;
1677 SELECT dbms_stats.lock('s0.st0');
1678 SELECT * FROM relations_locked_v;
1679 SELECT * FROM columns_locked_v c;
1681 DELETE FROM dbms_stats.relation_stats_locked;
1682 SELECT dbms_stats.lock('pg_toast.pg_toast_2618');
1684 DELETE FROM dbms_stats.relation_stats_locked;
1685 SELECT dbms_stats.lock('s0.st0_idx');
1686 SELECT * FROM relations_locked_v;
1687 SELECT * FROM columns_locked_v c;
1689 DELETE FROM dbms_stats.relation_stats_locked;
1690 SELECT dbms_stats.lock('s0.ss0');
1692 DELETE FROM dbms_stats.relation_stats_locked;
1693 SELECT dbms_stats.lock('s0.sc0');
1695 DELETE FROM dbms_stats.relation_stats_locked;
1696 SELECT dbms_stats.lock('s0.sft0');
1697 SELECT * FROM relations_locked_v;
1698 SELECT * FROM columns_locked_v c;
1700 -- #### 9.2 doesn't has materialized views
1701 -- DELETE FROM dbms_stats.relation_stats_locked;
1702 -- SELECT dbms_stats.lock('s0.smv0');
1703 -- SELECT * FROM relations_locked_v;
1704 -- SELECT * FROM columns_locked_v c;
1706 DELETE FROM dbms_stats.relation_stats_locked;
1707 SELECT dbms_stats.lock('pg_catalog.pg_class');
1709 DELETE FROM dbms_stats.relation_stats_locked;
1710 SELECT dbms_stats.lock_table_stats('s0.st0');
1711 UPDATE dbms_stats.relation_stats_locked
1712 SET (relpages, reltuples,
1718 WHERE relid = 's0.st0'::regclass;
1719 SELECT dbms_stats.lock('s0.st0');
1720 SELECT * FROM relations_locked_v;
1721 SELECT * FROM columns_locked_v c;
1723 DELETE FROM dbms_stats.relation_stats_locked;
1724 SELECT dbms_stats.lock('s0.st0');
1725 SELECT * FROM relations_locked_v;
1726 SELECT * FROM columns_locked_v c;
1729 * Stab function dbms_stats.lock
1731 ALTER FUNCTION dbms_stats.lock(relid regclass)
1732 RENAME TO truth_lock;
1733 CREATE FUNCTION dbms_stats.lock(relid regclass)
1737 RAISE NOTICE 'arguments are %', $1;
1743 ALTER FUNCTION dbms_stats.lock(relid regclass, attname text)
1744 RENAME TO truth_lock;
1745 CREATE FUNCTION dbms_stats.lock(
1751 RAISE NOTICE 'arguments are %, %', $1, $2;
1758 * No.12-1 dbms_stats.lock_database_stats
1761 SELECT dbms_stats.lock_database_stats();
1764 * No.12-2 dbms_stats.lock_schema_stats
1767 SELECT dbms_stats.lock_schema_stats('s0');
1769 SELECT dbms_stats.lock_schema_stats('s00');
1771 SELECT dbms_stats.lock_schema_stats('pg_catalog');
1774 * No.12-3 dbms_stats.lock_table_stats(regclass)
1777 SELECT dbms_stats.lock_table_stats('s0.st0');
1779 SELECT dbms_stats.lock_table_stats('st0');
1781 SELECT dbms_stats.lock_table_stats('s00.s0');
1784 * No.12-4 dbms_stats.lock_table_stats(schemaname, tablename)
1787 SELECT dbms_stats.lock_table_stats('s0', 'st0');
1790 * No.12-5 dbms_stats.lock_column_stats(regclass, attname)
1793 SELECT dbms_stats.lock_column_stats('s0.st0', 'id');
1795 SELECT dbms_stats.lock_column_stats('st0', 'id');
1797 SELECT dbms_stats.lock_column_stats('s00.s0', 'id');
1800 * No.12-6 dbms_stats.lock_column_stats(schemaname, tablename, int2)
1803 SELECT dbms_stats.lock_column_stats('s0', 'st0', 'id');
1806 * Delete Stab function lock
1808 DROP FUNCTION dbms_stats.lock(relid regclass);
1809 ALTER FUNCTION dbms_stats.truth_lock(relid regclass)
1811 DROP FUNCTION dbms_stats.lock(relid regclass, attname text);
1812 ALTER FUNCTION dbms_stats.truth_lock(relid regclass, attname text)
1816 * No.13-1 dbms_stats.unlock
1819 DELETE FROM dbms_stats.relation_stats_locked;
1820 SELECT dbms_stats.lock_database_stats();
1821 SELECT * FROM dbms_stats.backup_history
1823 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1824 SELECT count(*) FROM dbms_stats.column_stats_backup;
1825 SELECT dbms_stats.unlock();
1826 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1827 SELECT count(*) FROM dbms_stats.column_stats_locked;
1828 SELECT * FROM dbms_stats.backup_history
1830 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1831 SELECT count(*) FROM dbms_stats.column_stats_backup;
1834 DELETE FROM dbms_stats.relation_stats_locked;
1835 SELECT dbms_stats.lock_database_stats();
1836 SELECT dbms_stats.unlock();
1837 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1838 SELECT count(*) FROM dbms_stats.column_stats_locked;
1841 DELETE FROM dbms_stats.relation_stats_locked;
1842 SELECT dbms_stats.lock_database_stats();
1843 DELETE FROM dbms_stats.column_stats_locked;
1844 SELECT dbms_stats.unlock();
1845 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1846 SELECT count(*) FROM dbms_stats.column_stats_locked;
1849 DELETE FROM dbms_stats.relation_stats_locked;
1850 SELECT dbms_stats.unlock();
1851 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1852 SELECT count(*) FROM dbms_stats.column_stats_locked;
1855 DELETE FROM dbms_stats.relation_stats_locked;
1856 SELECT dbms_stats.lock_database_stats();
1857 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1860 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1863 SELECT dbms_stats.unlock('s0.st0');
1864 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1867 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1872 DELETE FROM dbms_stats.relation_stats_locked;
1873 SELECT dbms_stats.lock_database_stats();
1874 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1877 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1880 SELECT dbms_stats.unlock('st0');
1881 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1884 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1889 DELETE FROM dbms_stats.relation_stats_locked;
1890 SELECT dbms_stats.lock_database_stats();
1891 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1892 SELECT count(*) FROM dbms_stats.column_stats_locked;
1893 SELECT dbms_stats.unlock('s00.s0');
1894 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1895 SELECT count(*) FROM dbms_stats.column_stats_locked;
1898 DELETE FROM dbms_stats.relation_stats_locked;
1899 SELECT dbms_stats.lock_database_stats();
1900 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1903 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1904 SELECT dbms_stats.unlock('s0.st0', 'id');
1905 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1908 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1911 DELETE FROM dbms_stats.relation_stats_locked;
1912 SELECT dbms_stats.lock_database_stats();
1913 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1916 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1917 SELECT dbms_stats.unlock('s0.st0', 'dummy');
1918 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1921 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1924 DELETE FROM dbms_stats.relation_stats_locked;
1925 SELECT dbms_stats.lock_database_stats();
1926 DELETE FROM dbms_stats.column_stats_locked;
1927 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1930 SELECT dbms_stats.unlock('s0.st0', 'id');
1931 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1934 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_locked
1935 GROUP BY starelid, staattnum
1936 ORDER BY starelid, staattnum;
1939 DELETE FROM dbms_stats.relation_stats_locked;
1940 SELECT dbms_stats.lock_database_stats();
1941 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1944 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_locked
1945 GROUP BY starelid, staattnum
1946 ORDER BY starelid, staattnum;
1947 SELECT dbms_stats.unlock(NULL, 'id');
1948 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1951 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_locked
1952 GROUP BY starelid, staattnum
1953 ORDER BY starelid, staattnum;
1956 DELETE FROM dbms_stats.relation_stats_locked;
1957 SELECT dbms_stats.lock_database_stats();
1958 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1961 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_locked
1962 GROUP BY starelid, staattnum
1963 ORDER BY starelid, staattnum;
1964 SELECT dbms_stats.unlock('s0.st0', NULL);
1965 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1968 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_locked
1969 GROUP BY starelid, staattnum
1970 ORDER BY starelid, staattnum;
1973 DELETE FROM dbms_stats.relation_stats_locked;
1974 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
1975 SELECT dbms_stats.lock_database_stats();
1976 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1977 SELECT count(*) FROM dbms_stats.column_stats_locked;
1979 SELECT * FROM internal_locks;
1980 SELECT dbms_stats.unlock();
1981 SELECT * FROM internal_locks;
1983 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1984 SELECT count(*) FROM dbms_stats.column_stats_locked;
1987 * No.14-1 dbms_stats.unlock_database_stats
1990 DELETE FROM dbms_stats.relation_stats_locked;
1991 SELECT dbms_stats.lock_database_stats();
1992 SELECT * FROM dbms_stats.backup_history
1994 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1995 SELECT count(*) FROM dbms_stats.column_stats_backup;
1996 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1997 SELECT count(*) FROM dbms_stats.column_stats_locked;
1998 SELECT dbms_stats.unlock_database_stats();
1999 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2000 SELECT count(*) FROM dbms_stats.column_stats_locked;
2001 SELECT * FROM dbms_stats.backup_history
2003 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2004 SELECT count(*) FROM dbms_stats.column_stats_backup;
2007 DELETE FROM dbms_stats.relation_stats_locked;
2008 SELECT dbms_stats.lock_database_stats();
2009 DELETE FROM dbms_stats.column_stats_locked;
2010 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2011 SELECT dbms_stats.unlock_database_stats();
2012 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2013 SELECT count(*) FROM dbms_stats.column_stats_locked;
2016 DELETE FROM dbms_stats.relation_stats_locked;
2017 SELECT dbms_stats.unlock_database_stats();
2018 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2019 SELECT count(*) FROM dbms_stats.column_stats_locked;
2022 DELETE FROM dbms_stats.relation_stats_locked;
2023 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
2024 SELECT dbms_stats.lock_database_stats();
2025 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2026 SELECT count(*) FROM dbms_stats.column_stats_locked;
2028 SELECT * FROM internal_locks;
2029 SELECT dbms_stats.unlock_database_stats();
2030 SELECT * FROM internal_locks;
2032 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2033 SELECT count(*) FROM dbms_stats.column_stats_locked;
2036 * No.14-2 dbms_stats.unlock_schema_stats
2039 DELETE FROM dbms_stats.relation_stats_locked;
2040 SELECT dbms_stats.lock_database_stats();
2041 SELECT * FROM dbms_stats.backup_history
2043 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2044 SELECT count(*) FROM dbms_stats.column_stats_backup;
2045 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2048 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2051 SELECT dbms_stats.unlock_schema_stats('s0');
2052 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2055 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2058 SELECT * FROM dbms_stats.backup_history
2060 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2061 SELECT count(*) FROM dbms_stats.column_stats_backup;
2064 DELETE FROM dbms_stats.relation_stats_locked;
2065 SELECT dbms_stats.lock_database_stats();
2066 DELETE FROM dbms_stats.column_stats_locked;
2067 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2070 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2073 SELECT dbms_stats.unlock_schema_stats('s0');
2074 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2077 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2082 DELETE FROM dbms_stats.relation_stats_locked;
2083 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2086 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2089 SELECT dbms_stats.unlock_schema_stats('s0');
2090 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2093 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2098 DELETE FROM dbms_stats.relation_stats_locked;
2099 SELECT dbms_stats.lock_database_stats();
2100 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2103 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2106 SELECT dbms_stats.unlock_schema_stats('s0');
2107 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2110 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2115 DELETE FROM dbms_stats.relation_stats_locked;
2116 SELECT dbms_stats.lock_database_stats();
2117 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2120 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2123 SELECT dbms_stats.unlock_schema_stats('s00');
2124 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2127 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2132 DELETE FROM dbms_stats.relation_stats_locked;
2133 SELECT dbms_stats.lock_database_stats();
2134 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2137 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2140 SELECT dbms_stats.unlock_schema_stats('pg_catalog');
2141 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2144 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2149 DELETE FROM dbms_stats.relation_stats_locked;
2150 SELECT dbms_stats.lock_database_stats();
2151 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2154 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2157 SELECT dbms_stats.unlock_schema_stats(NULL);
2158 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2161 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2166 DELETE FROM dbms_stats.relation_stats_locked;
2167 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
2168 SELECT dbms_stats.lock_database_stats();
2169 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2172 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2176 SELECT * FROM internal_locks;
2177 SELECT dbms_stats.unlock_schema_stats('s0');
2178 SELECT * FROM internal_locks;
2180 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2183 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2188 * No.14-3 dbms_stats.unlock_table_stats(regclass)
2191 DELETE FROM dbms_stats.relation_stats_locked;
2192 SELECT dbms_stats.lock_database_stats();
2193 SELECT * FROM dbms_stats.backup_history
2195 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2196 SELECT count(*) FROM dbms_stats.column_stats_backup;
2197 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2200 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2203 SELECT dbms_stats.unlock_table_stats('s0.st0');
2204 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2207 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2210 SELECT * FROM dbms_stats.backup_history
2212 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2213 SELECT count(*) FROM dbms_stats.column_stats_backup;
2216 DELETE FROM dbms_stats.relation_stats_locked;
2217 SELECT dbms_stats.lock_database_stats();
2218 DELETE FROM dbms_stats.column_stats_locked;
2219 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2222 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2225 SELECT dbms_stats.unlock_table_stats('s0.st0');
2226 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2229 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2234 DELETE FROM dbms_stats.relation_stats_locked;
2235 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2238 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2241 SELECT dbms_stats.unlock_table_stats('s0.st0');
2242 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2245 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2250 DELETE FROM dbms_stats.relation_stats_locked;
2251 SELECT dbms_stats.lock_database_stats();
2252 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2255 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2258 SELECT dbms_stats.unlock_table_stats('s0.st0');
2259 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2262 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2267 DELETE FROM dbms_stats.relation_stats_locked;
2268 SELECT dbms_stats.lock_database_stats();
2269 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2272 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2275 SELECT dbms_stats.unlock_table_stats('st0');
2276 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2279 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2284 DELETE FROM dbms_stats.relation_stats_locked;
2285 SELECT dbms_stats.lock_database_stats();
2286 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2289 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2292 SELECT dbms_stats.unlock_table_stats('s00.s0');
2293 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2296 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2301 DELETE FROM dbms_stats.relation_stats_locked;
2302 SELECT dbms_stats.lock_database_stats();
2303 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2306 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2309 SELECT dbms_stats.unlock_table_stats(NULL);
2310 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2313 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2318 DELETE FROM dbms_stats.relation_stats_locked;
2319 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
2320 SELECT dbms_stats.lock_database_stats();
2321 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2324 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2328 SELECT * FROM internal_locks;
2329 SELECT dbms_stats.unlock_table_stats('s0.st0');
2330 SELECT * FROM internal_locks;
2332 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2335 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2340 * No.14-4 dbms_stats.unlock_table_stats(schemaname, tablename)
2343 DELETE FROM dbms_stats.relation_stats_locked;
2344 SELECT dbms_stats.lock_database_stats();
2345 SELECT * FROM dbms_stats.backup_history
2347 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2348 SELECT count(*) FROM dbms_stats.column_stats_backup;
2349 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2352 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2355 SELECT dbms_stats.unlock_table_stats('s0','st0');
2356 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2359 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2362 SELECT * FROM dbms_stats.backup_history
2364 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2365 SELECT count(*) FROM dbms_stats.column_stats_backup;
2368 DELETE FROM dbms_stats.relation_stats_locked;
2369 SELECT dbms_stats.lock_database_stats();
2370 DELETE FROM dbms_stats.column_stats_locked;
2371 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2374 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2377 SELECT dbms_stats.unlock_table_stats('s0', 'st0');
2378 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2381 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2386 DELETE FROM dbms_stats.relation_stats_locked;
2387 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2390 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2393 SELECT dbms_stats.unlock_table_stats('s0', 'st0');
2394 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2397 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2402 DELETE FROM dbms_stats.relation_stats_locked;
2403 SELECT dbms_stats.lock_database_stats();
2404 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2407 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2410 SELECT dbms_stats.unlock_table_stats('s0', 'st0');
2411 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2414 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2419 DELETE FROM dbms_stats.relation_stats_locked;
2420 SELECT dbms_stats.lock_database_stats();
2421 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2424 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2427 SELECT dbms_stats.unlock_table_stats('s00', 's0');
2428 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2431 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2436 DELETE FROM dbms_stats.relation_stats_locked;
2437 SELECT dbms_stats.lock_database_stats();
2438 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2441 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2444 SELECT dbms_stats.unlock_table_stats(NULL, 'st0');
2445 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2448 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2453 DELETE FROM dbms_stats.relation_stats_locked;
2454 SELECT dbms_stats.lock_database_stats();
2455 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2458 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2461 SELECT dbms_stats.unlock_table_stats('s0', NULL);
2462 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2465 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2470 DELETE FROM dbms_stats.relation_stats_locked;
2471 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
2472 SELECT dbms_stats.lock_database_stats();
2473 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2476 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2480 SELECT * FROM internal_locks;
2481 SELECT dbms_stats.unlock_table_stats('s0', 'st0');
2482 SELECT * FROM internal_locks;
2484 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2487 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2492 * No.14-5 dbms_stats.unlock_column_stats(regclass, attname)
2495 DELETE FROM dbms_stats.relation_stats_locked;
2496 SELECT dbms_stats.lock_database_stats();
2497 SELECT * FROM dbms_stats.backup_history
2499 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2500 SELECT count(*) FROM dbms_stats.column_stats_backup;
2501 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2504 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2505 SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
2506 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2507 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2510 SELECT * FROM dbms_stats.backup_history
2512 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2513 SELECT count(*) FROM dbms_stats.column_stats_backup;
2516 DELETE FROM dbms_stats.relation_stats_locked;
2517 SELECT dbms_stats.lock_database_stats();
2518 DELETE FROM dbms_stats.column_stats_locked;
2519 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2522 SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
2523 SELECT count(*) FROM dbms_stats.column_stats_locked;
2524 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2529 DELETE FROM dbms_stats.relation_stats_locked;
2530 SELECT dbms_stats.lock_database_stats();
2531 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2534 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2535 SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
2536 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2537 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2542 DELETE FROM dbms_stats.relation_stats_locked;
2543 SELECT dbms_stats.lock_database_stats();
2544 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2547 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2548 SELECT dbms_stats.unlock_column_stats('st0', 'id');
2549 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2550 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2555 DELETE FROM dbms_stats.relation_stats_locked;
2556 SELECT dbms_stats.lock_database_stats();
2557 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2560 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2561 SELECT dbms_stats.unlock_column_stats('s0.st0', 'dummy');
2562 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2563 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2568 DELETE FROM dbms_stats.relation_stats_locked;
2569 SELECT dbms_stats.lock_database_stats();
2570 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2573 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2574 SELECT dbms_stats.unlock_column_stats('s00.s0', 'id');
2575 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2576 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2581 DELETE FROM dbms_stats.relation_stats_locked;
2582 SELECT dbms_stats.lock_database_stats();
2583 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2586 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2587 SELECT dbms_stats.unlock_column_stats(NULL, 'id');
2588 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2589 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2594 DELETE FROM dbms_stats.relation_stats_locked;
2595 SELECT dbms_stats.lock_database_stats();
2596 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2599 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2600 SELECT dbms_stats.unlock_column_stats('s0.st0', NULL);
2601 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2602 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2607 DELETE FROM dbms_stats.relation_stats_locked;
2608 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
2609 SELECT dbms_stats.lock_database_stats();
2610 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2613 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2615 SELECT * FROM internal_locks;
2616 SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
2617 SELECT * FROM internal_locks;
2619 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2620 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2625 * No.14-6 dbms_stats.unlock_column_stats(schemaname, tablename, attname)
2628 DELETE FROM dbms_stats.relation_stats_locked;
2629 SELECT dbms_stats.lock_database_stats();
2630 SELECT * FROM dbms_stats.backup_history
2632 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2635 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2636 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
2637 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2638 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2641 SELECT * FROM dbms_stats.backup_history
2643 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2644 SELECT count(*) FROM dbms_stats.column_stats_backup;
2647 DELETE FROM dbms_stats.relation_stats_locked;
2648 SELECT dbms_stats.lock_database_stats();
2649 DELETE FROM dbms_stats.column_stats_locked;
2650 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2653 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
2654 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2655 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2660 DELETE FROM dbms_stats.relation_stats_locked;
2661 SELECT dbms_stats.lock_database_stats();
2662 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2665 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2666 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
2667 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2668 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2673 DELETE FROM dbms_stats.relation_stats_locked;
2674 SELECT dbms_stats.lock_database_stats();
2675 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2678 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2679 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'dummy');
2680 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2681 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2686 DELETE FROM dbms_stats.relation_stats_locked;
2687 SELECT dbms_stats.lock_database_stats();
2688 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2691 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2692 SELECT dbms_stats.unlock_column_stats(NULL, 'st0', 'id');
2693 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2694 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2699 DELETE FROM dbms_stats.relation_stats_locked;
2700 SELECT dbms_stats.lock_database_stats();
2701 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2704 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2705 SELECT dbms_stats.unlock_column_stats('s0', NULL, 'id');
2706 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2707 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2712 DELETE FROM dbms_stats.relation_stats_locked;
2713 SELECT dbms_stats.lock_database_stats();
2714 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2717 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2718 SELECT dbms_stats.unlock_column_stats('s0', 'st0', NULL);
2719 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2720 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2725 DELETE FROM dbms_stats.relation_stats_locked;
2726 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
2727 SELECT dbms_stats.lock_database_stats();
2728 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2731 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2733 SELECT * FROM internal_locks;
2734 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
2735 SELECT * FROM internal_locks;
2737 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2738 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2742 -- No.15 error description. -- abnormal case.
2743 RESET SESSION AUTHORIZATION;
2744 CREATE TABLE s0.st4 (a int, b text);
2745 CREATE VIEW s0.vst4 AS select * FROM s0.st4;
2746 GRANT SELECT ON s0.vst4 TO regular_user;
2748 ALTER TABLE dbms_stats.relation_stats_locked OWNER TO regular_user;
2749 /* reconnection needed to flush cache */
2752 EXPLAIN (COSTS OFF) SELECT * FROM s0.vst4 WHERE a = 1;
2755 ALTER TABLE dbms_stats.relation_stats_locked OWNER TO super_user;
2756 DROP TABLE s0.st4 CASCADE;