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('f');
879 * No.7-1 dbms_stats.backup
881 DELETE FROM dbms_stats.backup_history;
882 INSERT INTO dbms_stats.backup_history(id, time, unit) values(1, '2012-01-01', 'd');
884 SELECT dbms_stats.backup(1, 's0.sft0'::regclass, NULL);
885 SELECT count(*) FROM dbms_stats.relation_stats_backup;
886 SELECT count(*) FROM dbms_stats.column_stats_backup;
889 DELETE FROM dbms_stats.relation_stats_backup;
890 SELECT dbms_stats.backup(1, NULL, 1::int2);
891 SELECT relid::regclass FROM dbms_stats.relation_stats_backup
894 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_backup
895 GROUP BY starelid, staattnum
896 ORDER BY starelid, staattnum;
899 DELETE FROM dbms_stats.relation_stats_backup;
900 SELECT dbms_stats.backup(1, NULL::regclass, NULL);
901 SELECT relid::regclass FROM dbms_stats.relation_stats_backup
904 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_backup
905 GROUP BY starelid, staattnum
906 ORDER BY starelid, staattnum;
909 DELETE FROM dbms_stats.relation_stats_backup;
910 \! psql contrib_regression -c "SELECT dbms_stats.backup(NULL, 's0.st0'::regclass, NULL)" > results/ut_no2_1_17.out 2>&1
911 SELECT count(*) FROM dbms_stats.relation_stats_backup;
912 SELECT count(*) FROM dbms_stats.column_stats_backup;
915 * No.8-1 dbms_stats.backup
917 SELECT setval('dbms_stats.backup_history_id_seq',1, false);
919 * Stab function dbms_stats.backup
921 ALTER FUNCTION dbms_stats.backup(
925 RENAME TO truth_func_backup;
927 CREATE OR REPLACE FUNCTION dbms_stats.backup(
934 RAISE NOTICE 'arguments are %, %, %', $1, $2, $3;
941 DELETE FROM dbms_stats.backup_history;
942 SELECT dbms_stats.backup('s0.st0'::regclass, 'id', 'dummy comment');
943 SELECT id, unit, comment FROM dbms_stats.backup_history;
946 DELETE FROM dbms_stats.backup_history;
947 SELECT dbms_stats.backup('s0.st0'::regclass, NULL, 'dummy comment');
948 SELECT id, unit, comment FROM dbms_stats.backup_history;
951 DELETE FROM dbms_stats.backup_history;
952 SELECT dbms_stats.backup(NULL::regclass, 'id', 'dummy comment');
953 SELECT id, unit, comment FROM dbms_stats.backup_history;
956 DELETE FROM dbms_stats.backup_history;
957 SELECT dbms_stats.backup(NULL::regclass, NULL, 'dummy comment');
958 SELECT id, unit, comment FROM dbms_stats.backup_history;
961 DELETE FROM dbms_stats.backup_history;
962 SELECT dbms_stats.backup(0, NULL, 'dummy comment');
963 SELECT id, unit, comment FROM dbms_stats.backup_history;
966 DELETE FROM dbms_stats.backup_history;
967 SELECT dbms_stats.backup('s0.st0'::regclass, NULL, 'dummy comment');
968 SELECT id, unit, comment FROM dbms_stats.backup_history;
971 DELETE FROM dbms_stats.backup_history;
972 SELECT dbms_stats.backup(
973 'pg_toast.pg_toast_2618'::regclass,
976 SELECT id, unit, comment FROM dbms_stats.backup_history;
979 DELETE FROM dbms_stats.backup_history;
980 SELECT dbms_stats.backup('s0.st0_idx'::regclass, NULL, 'dummy comment');
981 SELECT id, unit, comment FROM dbms_stats.backup_history;
984 DELETE FROM dbms_stats.backup_history;
985 SELECT dbms_stats.backup('s0.ss0'::regclass, NULL, 'dummy comment');
986 SELECT id, unit, comment FROM dbms_stats.backup_history;
989 DELETE FROM dbms_stats.backup_history;
990 SELECT dbms_stats.backup('s0.sc0'::regclass, NULL, 'dummy comment');
991 SELECT id, unit, comment FROM dbms_stats.backup_history;
994 DELETE FROM dbms_stats.backup_history;
995 SELECT dbms_stats.backup('s0.sft0'::regclass, NULL, 'dummy comment');
996 SELECT id, unit, comment FROM dbms_stats.backup_history;
999 DELETE FROM dbms_stats.backup_history;
1000 SELECT dbms_stats.backup('pg_catalog.pg_class'::regclass, NULL, 'dummy comment');
1001 SELECT id, unit, comment FROM dbms_stats.backup_history;
1004 DELETE FROM dbms_stats.backup_history;
1005 SELECT dbms_stats.backup('s0.st0'::regclass, 'dummy', 'dummy comment');
1006 SELECT id, unit, comment FROM dbms_stats.backup_history;
1009 DELETE FROM dbms_stats.backup_history;
1010 DELETE FROM pg_statistic
1011 WHERE starelid = 's0.st0'::regclass
1012 AND staattnum = 1::int2;
1013 SELECT count(*) FROM dbms_stats.column_stats_effective
1014 WHERE starelid = 's0.st0'::regclass
1015 AND staattnum = 1::int2;
1016 SELECT dbms_stats.backup('s0.st0'::regclass, 'id', 'dummy comment');
1017 SELECT id, unit, comment FROM dbms_stats.backup_history;
1020 * Stab function dbms_stats.backup
1022 ALTER FUNCTION dbms_stats.backup(
1026 RENAME TO truth_func_backup;
1027 CREATE OR REPLACE FUNCTION dbms_stats.backup(
1028 relid regclass DEFAULT NULL,
1029 attname text DEFAULT NULL,
1030 comment text DEFAULT NULL)
1034 IF $3 = '<NULL>' THEN
1035 RAISE NOTICE 'third argument is not NULL but string "<NULL>"';
1037 RAISE NOTICE 'arguments are %, %, %', $1, $2, $3;
1044 * No.8-3 dbms_stats.backup_schema_stats
1046 SELECT setval('dbms_stats.backup_history_id_seq',9, false);
1048 SELECT dbms_stats.backup_schema_stats('s0', 'comment');
1049 SELECT id, unit, comment FROM dbms_stats.backup_history
1053 SELECT dbms_stats.backup_schema_stats('s00', 'comment');
1054 SELECT id, unit, comment FROM dbms_stats.backup_history
1058 SELECT dbms_stats.backup_schema_stats('pg_catalog', 'comment');
1059 SELECT id, unit, comment FROM dbms_stats.backup_history
1064 * Delete stab function dbms_stats.backup
1066 DROP FUNCTION dbms_stats.backup(
1070 ALTER FUNCTION dbms_stats.truth_func_backup(
1075 DROP FUNCTION dbms_stats.backup(
1079 ALTER FUNCTION dbms_stats.truth_func_backup(
1087 * create backup statistics state A
1089 DELETE FROM dbms_stats.backup_history;
1091 INSERT INTO dbms_stats.backup_history(id, time, unit)
1092 VALUES (1, '2012-02-29 23:59:56.999999', 'd');
1094 SELECT setval('dbms_stats.backup_history_id_seq',1);
1095 SELECT dbms_stats.backup();
1096 UPDATE dbms_stats.backup_history
1097 SET time = '2012-02-29 23:59:57'
1099 SELECT dbms_stats.backup('s0.st0');
1100 UPDATE dbms_stats.backup_history
1101 SET time = '2012-02-29 23:59:57.000001'
1103 SELECT dbms_stats.backup();
1104 UPDATE dbms_stats.backup_history
1105 SET time = '2012-02-29 23:59:58'
1107 DELETE FROM dbms_stats.relation_stats_backup
1109 SELECT dbms_stats.backup('s0.st0', 'id');
1110 UPDATE dbms_stats.backup_history
1111 SET time = '2012-03-01 00:00:00'
1113 SELECT dbms_stats.backup('s0.st0');
1114 UPDATE dbms_stats.backup_history
1115 SET time = '2012-03-01 00:00:02'
1117 SELECT dbms_stats.backup('public.st0');
1118 UPDATE dbms_stats.backup_history
1119 SET time = '2012-03-01 00:00:04'
1121 INSERT INTO dbms_stats.backup_history(time, unit)
1122 VALUES ('2012-03-01 00:00:06', 's');
1123 SELECT dbms_stats.backup(8, c.oid, NULL)
1124 FROM pg_catalog.pg_class c,
1125 pg_catalog.pg_namespace n
1126 WHERE n.nspname = 's0'
1127 AND c.relnamespace = n.oid
1128 AND c.relkind IN ('r', 'i');
1130 SELECT * FROM dbms_stats.backup_history
1132 SELECT * FROM relations_backup_v;
1133 SELECT * FROM columns_backup_v;
1138 * No.9-1 dbms_stats.restore
1141 DELETE FROM dbms_stats._relation_stats_locked;
1143 SELECT relation::regclass, mode
1144 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
1145 WHERE mode LIKE '%ExclusiveLock%'
1146 ORDER BY relation::regclass::text, mode;
1147 SELECT dbms_stats.restore(2, 's0.st0', NULL);
1148 SELECT relation::regclass, mode
1149 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
1150 WHERE mode LIKE '%ExclusiveLock%'
1151 ORDER BY relation::regclass::text, mode;
1153 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1156 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1159 DELETE FROM dbms_stats._relation_stats_locked;
1160 SELECT dbms_stats.restore(2, 'st0', NULL);
1161 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1164 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1167 DELETE FROM dbms_stats._relation_stats_locked;
1168 SELECT dbms_stats.restore(2, 's00.s0', NULL);
1169 SELECT count(*) FROM dbms_stats.column_stats_locked;
1170 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1173 DELETE FROM dbms_stats._relation_stats_locked;
1174 SELECT dbms_stats.restore(NULL, 's0.st0', NULL);
1175 SELECT count(*) FROM dbms_stats.column_stats_locked;
1176 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1179 DELETE FROM dbms_stats._relation_stats_locked;
1180 SELECT dbms_stats.restore(2, 's0.st0', 'id');
1181 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1184 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1187 DELETE FROM dbms_stats._relation_stats_locked;
1188 SELECT dbms_stats.restore(2, NULL, 'id');
1189 SELECT count(*) FROM dbms_stats.column_stats_locked;
1190 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1193 DELETE FROM dbms_stats._relation_stats_locked;
1194 SELECT dbms_stats.restore(2, 's0.st0', NULL);
1195 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1198 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1201 DELETE FROM dbms_stats._relation_stats_locked;
1202 SELECT dbms_stats.restore(2, NULL, NULL);
1203 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1206 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1209 DELETE FROM dbms_stats._relation_stats_locked;
1210 SELECT dbms_stats.restore(0, 's0.st0', NULL);
1211 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1214 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1217 DELETE FROM dbms_stats._relation_stats_locked;
1218 SELECT dbms_stats.restore(2, 0, 'id');
1219 SELECT count(*) FROM dbms_stats.column_stats_locked;
1220 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1223 DELETE FROM dbms_stats._relation_stats_locked;
1224 SELECT dbms_stats.restore(1, 's0.st0', NULL);
1225 SELECT count(*) FROM dbms_stats.column_stats_locked;
1226 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1229 DELETE FROM dbms_stats._relation_stats_locked;
1230 SELECT dbms_stats.restore(2, 's0.st0', 'dummy');
1231 SELECT count(*) FROM dbms_stats.column_stats_locked;
1232 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1235 DELETE FROM dbms_stats._relation_stats_locked;
1236 SELECT dbms_stats.restore(1, 's0.st0', 'id');
1237 SELECT count(*) FROM dbms_stats.column_stats_locked;
1238 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1241 DELETE FROM dbms_stats._relation_stats_locked;
1242 ALTER TABLE s1.st0 DROP COLUMN id;
1243 SELECT dbms_stats.restore(2, 's1.st0', 'id');
1244 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1247 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1250 DELETE FROM dbms_stats._relation_stats_locked;
1251 \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';"`
1253 -- SELECT dbms_stats.restore(2, :s1_st0_oid, NULL);
1254 -- To avoid test unstability caused by relation id alloction, the test
1255 -- above is omitted.
1257 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1258 SELECT count(*) FROM dbms_stats.column_stats_locked;
1259 CREATE TABLE s1.st0(id integer, num integer);
1260 INSERT INTO s1.st0 VALUES (1, 15), (2, 25), (3, 35), (4, 45);
1263 DELETE FROM dbms_stats._relation_stats_locked;
1264 SELECT dbms_stats.restore(2, 's0.st0', NULL);
1265 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1268 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1271 DELETE FROM dbms_stats._relation_stats_locked;
1272 INSERT INTO dbms_stats.relation_stats_backup(
1273 id, relid, relname, relpages, reltuples,
1277 'pg_toast.pg_toast_2618'::regclass,
1278 'pg_toast.pg_toast_2618', 1, 1,
1281 SELECT * FROM relations_backup_v
1283 AND relname = 'pg_toast.pg_toast_2618';
1284 SELECT dbms_stats.restore(2, 'pg_toast.pg_toast_2618', NULL);
1285 SELECT count(*) FROM dbms_stats.column_stats_locked;
1286 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1287 DELETE FROM dbms_stats.relation_stats_backup
1289 AND relname = 'pg_toast.pg_toast_2618';
1292 DELETE FROM dbms_stats._relation_stats_locked;
1293 SELECT dbms_stats.restore(2, 's0.st0_idx', NULL);
1294 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1297 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1300 DELETE FROM dbms_stats._relation_stats_locked;
1301 INSERT INTO dbms_stats.relation_stats_backup(
1302 id, relid, relname, relpages, reltuples,
1305 VALUES (2, 's0.ss0'::regclass, 's0.ss0', 1, 1,
1308 SELECT * FROM relations_backup_v
1310 AND relname = 's0.ss0';
1311 SELECT dbms_stats.restore(2, 's0.ss0', NULL);
1312 SELECT count(*) FROM dbms_stats.column_stats_locked;
1313 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1314 DELETE FROM dbms_stats.relation_stats_backup
1316 AND relname = 's0.ss0';
1319 DELETE FROM dbms_stats._relation_stats_locked;
1320 INSERT INTO dbms_stats.relation_stats_backup(
1321 id, relid, relname, relpages, reltuples,
1324 VALUES (2, 's0.sc0'::regclass, 's0.sc0', 1, 1,
1327 SELECT * FROM relations_backup_v
1329 AND relname = 's0.sc0';
1330 SELECT dbms_stats.restore(2, 's0.sc0', NULL);
1331 SELECT count(*) FROM dbms_stats.column_stats_locked;
1332 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1333 DELETE FROM dbms_stats.relation_stats_backup
1335 AND relname = 's0.sc0';
1338 DELETE FROM dbms_stats._relation_stats_locked;
1339 INSERT INTO dbms_stats.relation_stats_backup(
1340 id, relid, relname, relpages, reltuples,
1343 VALUES (3, 's0.sft0'::regclass, 's0.sft0', 1, 1,
1346 SELECT * FROM relations_backup_v
1348 AND relname = 's0.sft0';
1349 SELECT dbms_stats.restore(2, 's0.sft0', NULL);
1350 SELECT count(*) FROM dbms_stats.column_stats_locked;
1351 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1352 DELETE FROM dbms_stats.relation_stats_backup
1354 AND relname = 's0.sft0';
1357 DELETE FROM dbms_stats._relation_stats_locked;
1358 INSERT INTO dbms_stats.relation_stats_backup(
1359 id, relid, relname, relpages, reltuples,
1362 VALUES (2, 'pg_catalog.pg_class'::regclass, 'pg_catalog.pg_class', 1, 1,
1365 SELECT * FROM relations_backup_v
1367 AND relname = 'pg_catalog.pg_class';
1368 SELECT dbms_stats.restore(2, 'pg_catalog.pg_class', NULL);
1369 SELECT count(*) FROM dbms_stats.column_stats_locked;
1370 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1371 DELETE FROM dbms_stats.relation_stats_backup
1373 AND relname = 'pg_catalog.pg_class';
1376 DELETE FROM dbms_stats._relation_stats_locked;
1377 INSERT INTO dbms_stats._relation_stats_locked(relid, relname)
1378 VALUES ('s0.st0'::regclass, 's0.st0');
1379 INSERT INTO dbms_stats._column_stats_locked(starelid, staattnum, stainherit)
1380 SELECT starelid::regclass, staattnum, stainherit
1381 FROM dbms_stats.column_stats_effective
1382 WHERE starelid = 's0.st0'::regclass;
1383 SELECT id, unit, comment FROM dbms_stats.backup_history
1385 SELECT * FROM columns_locked_v;
1386 SELECT * FROM relations_locked_v;
1387 SELECT dbms_stats.restore(2, 's0.st0', NULL);
1388 SELECT * FROM relations_locked_v;
1389 SELECT * FROM columns_locked_v;
1392 DELETE FROM dbms_stats._relation_stats_locked;
1393 SELECT id, unit, comment FROM dbms_stats.backup_history
1395 SELECT dbms_stats.restore(2, 's0.st0', NULL);
1396 SELECT * FROM relations_locked_v;
1397 SELECT * FROM columns_locked_v;
1400 * Stab function dbms_stats.restore
1402 CREATE OR REPLACE FUNCTION dbms_stats.restore(
1404 relid regclass DEFAULT NULL,
1405 attname text DEFAULT NULL)
1406 RETURNS SETOF regclass AS
1409 RAISE NOTICE 'arguments are "%, %, %"', $1, $2, $3;
1411 SELECT c.oid::regclass
1412 FROM pg_class c, dbms_stats.relation_stats_backup b
1413 WHERE (c.oid = $2 OR $2 IS NULL)
1415 AND c.relkind IN ('r', 'i')
1416 AND (b.id <= $1 OR $1 IS NOT NULL)
1418 ORDER BY c.oid::regclass::text;
1424 * No.10-1 dbms_stats.restore_database_stats
1427 SELECT dbms_stats.restore_database_stats('2012-02-29 23:59:57');
1429 SELECT dbms_stats.restore_database_stats('2012-02-29 23:59:57.000002');
1431 SELECT dbms_stats.restore_database_stats('2012-01-01 00:00:00');
1432 --#No.10-1-4 is skipped after lock tests
1433 --#No.10-1-5 is skipped after lock tests
1435 SELECT dbms_stats.restore_database_stats('2012-02-29 23:59:57');
1438 * No.10-2 dbms_stats.restore_schema_stats
1441 SELECT dbms_stats.restore_schema_stats('s0', '2012-02-29 23:59:57');
1443 SELECT dbms_stats.restore_schema_stats('s0', '2012-02-29 23:59:57.000002');
1445 SELECT dbms_stats.restore_schema_stats('s0', '2012-01-01 00:00:00');
1446 --#No.10-2-4 is skipped after lock tests
1447 --#No.10-2-5 is skipped after lock tests
1449 SELECT dbms_stats.restore_schema_stats('s0', '2012-02-29 23:59:57');
1451 SELECT dbms_stats.restore_schema_stats('s0', '2012-02-29 23:59:57');
1452 --#No.10-2-8 is skipped after lock tests
1454 SELECT dbms_stats.restore_schema_stats('s00', '2012-02-29 23:59:57');
1456 SELECT dbms_stats.restore_schema_stats('pg_catalog', '2012-02-29 23:59:57');
1459 * No.10-7 dbms_stats.restore_stats
1462 DELETE FROM dbms_stats._relation_stats_locked;
1463 SELECT dbms_stats.restore_stats(NULL);
1466 DELETE FROM dbms_stats._relation_stats_locked;
1467 SELECT dbms_stats.restore_stats(0);
1470 DELETE FROM dbms_stats._relation_stats_locked;
1472 SELECT relation::regclass, mode
1473 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
1474 WHERE mode LIKE '%ExclusiveLock%'
1475 ORDER BY relation::regclass::text, mode;
1476 SELECT dbms_stats.restore_stats(2);
1477 SELECT relation::regclass, mode
1478 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
1479 WHERE mode LIKE '%ExclusiveLock%'
1480 ORDER BY relation::regclass::text, mode;
1482 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1485 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1488 DELETE FROM dbms_stats._relation_stats_locked;
1489 INSERT INTO dbms_stats._relation_stats_locked(relid, relname)
1490 SELECT relid::regclass, relname
1491 FROM dbms_stats.relation_stats_effective;
1492 INSERT INTO dbms_stats._column_stats_locked(starelid, staattnum, stainherit)
1493 SELECT starelid::regclass, staattnum, stainherit
1494 FROM dbms_stats.column_stats_effective;
1495 SELECT id, unit, comment FROM dbms_stats.backup_history
1497 SELECT * FROM columns_locked_v;
1498 SELECT * FROM relations_locked_v;
1499 SELECT dbms_stats.restore_stats(8);
1500 SELECT * FROM relations_locked_v;
1501 SELECT * FROM columns_locked_v;
1504 DELETE FROM dbms_stats._relation_stats_locked;
1505 SELECT id, unit, comment FROM dbms_stats.backup_history
1507 SELECT dbms_stats.restore_stats(8);
1508 SELECT * FROM relations_locked_v;
1509 SELECT * FROM columns_locked_v;
1512 * No.11-1 dbms_stats.lock(relid, attname)
1515 DELETE FROM dbms_stats._relation_stats_locked;
1516 SELECT dbms_stats.lock(NULL, NULL);
1518 ALTER FUNCTION dbms_stats.lock(relid regclass)
1519 RENAME TO truth_lock;
1520 CREATE FUNCTION dbms_stats.lock(relid regclass)
1524 RAISE NOTICE 'arguments are %', $1;
1529 DELETE FROM dbms_stats._relation_stats_locked;
1530 SELECT dbms_stats.lock('s0.st0', NULL);
1531 DROP FUNCTION dbms_stats.lock(relid regclass);
1532 ALTER FUNCTION dbms_stats.truth_lock(relid regclass)
1535 DELETE FROM dbms_stats._relation_stats_locked;
1536 SELECT dbms_stats.lock(NULL, 'id');
1538 DELETE FROM dbms_stats._relation_stats_locked;
1539 SELECT dbms_stats.lock('s0.st0', 'id');
1540 SELECT * FROM relations_locked_v;
1541 SELECT * FROM columns_locked_v c;
1543 DELETE FROM dbms_stats._relation_stats_locked;
1544 SELECT dbms_stats.lock(0, 'id');
1546 DELETE FROM dbms_stats._relation_stats_locked;
1547 SELECT dbms_stats.lock('s0.st0', 'id');
1548 SELECT * FROM relations_locked_v;
1549 SELECT * FROM columns_locked_v c;
1551 DELETE FROM dbms_stats._relation_stats_locked;
1552 SELECT dbms_stats.lock('pg_toast.pg_toast_2618', 'id');
1554 DELETE FROM dbms_stats._relation_stats_locked;
1555 SELECT dbms_stats.lock('s0.st0_idx', 'id');
1557 DELETE FROM dbms_stats._relation_stats_locked;
1558 SELECT dbms_stats.lock('st1_exp', 'lower');
1559 SELECT * FROM relations_locked_v;
1560 SELECT * FROM columns_locked_v c;
1561 DELETE FROM dbms_stats._relation_stats_locked;
1564 DELETE FROM dbms_stats._relation_stats_locked;
1565 SELECT dbms_stats.lock('s0.ss0', 'id');
1567 DELETE FROM dbms_stats._relation_stats_locked;
1568 SELECT dbms_stats.lock('s0.sc0', 'id');
1570 DELETE FROM dbms_stats._relation_stats_locked;
1571 SELECT dbms_stats.lock('s0.sft0', 'id');
1572 SELECT * FROM relations_locked_v;
1573 SELECT * FROM columns_locked_v c;
1575 DELETE FROM dbms_stats._relation_stats_locked;
1576 SELECT dbms_stats.lock('pg_catalog.pg_class', 'id');
1578 DELETE FROM dbms_stats._relation_stats_locked;
1579 SELECT dbms_stats.lock('s0.st0', 'dummy');
1581 DELETE FROM dbms_stats._relation_stats_locked;
1582 DELETE FROM pg_statistic
1583 WHERE starelid = 's0.st0'::regclass;
1584 SELECT dbms_stats.lock('s0.st0', 'id');
1587 DELETE FROM dbms_stats._relation_stats_locked;
1588 INSERT INTO dbms_stats._relation_stats_locked(
1589 relid, relname, relpages, reltuples,
1592 VALUES('s0.st0'::regclass, 's0.st0', 1, 1640,
1595 SELECT dbms_stats.lock_column_stats('s0.st0','id');
1596 UPDATE dbms_stats._column_stats_locked
1597 SET (stanullfrac, stawidth, stadistinct,
1598 stakind1, stakind2, stakind3, stakind4,
1600 staop1, staop2, staop3, staop4,
1602 stanumbers1, stanumbers2, stanumbers3, stanumbers4,
1604 stavalues1, stavalues2, stavalues3, stavalues4
1608 NULL, NULL, NULL, NULL,
1609 NULL, NULL, NULL, NULL,
1610 NULL, NULL, NULL, NULL,
1611 NULL, NULL, NULL, NULL,
1612 NULL, NULL, NULL, NULL)
1613 WHERE starelid = 's0.st0'::regclass;
1614 SELECT dbms_stats.lock('s0.st0', 'id');
1615 SELECT * FROM relations_locked_v;
1616 SELECT * FROM columns_locked_v c;
1618 DELETE FROM dbms_stats._relation_stats_locked;
1619 SELECT dbms_stats.lock('s0.st0', 'id');
1620 SELECT * FROM relations_locked_v
1621 WHERE relid = 's0.st0'::regclass;
1622 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1625 * No.11-2 dbms_stats.lock(relid)
1628 DELETE FROM dbms_stats._relation_stats_locked;
1630 SELECT relation::regclass, mode
1631 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
1632 WHERE mode LIKE '%ExclusiveLock%'
1633 ORDER BY relation::regclass::text, mode;
1634 SELECT dbms_stats.lock('s0.st0');
1635 SELECT * FROM relations_locked_v;
1636 SELECT * FROM columns_locked_v c;
1637 SELECT relation::regclass, mode
1638 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
1639 WHERE mode LIKE '%ExclusiveLock%'
1640 ORDER BY relation::regclass::text, mode;
1644 DELETE FROM dbms_stats._relation_stats_locked;
1645 SELECT dbms_stats.lock(NULL);
1647 DELETE FROM dbms_stats._relation_stats_locked;
1648 SELECT dbms_stats.lock('0');
1650 DELETE FROM dbms_stats._relation_stats_locked;
1651 SELECT dbms_stats.lock('s0.st0');
1652 SELECT * FROM relations_locked_v;
1653 SELECT * FROM columns_locked_v c;
1655 DELETE FROM dbms_stats._relation_stats_locked;
1656 SELECT dbms_stats.lock('pg_toast.pg_toast_2618');
1658 DELETE FROM dbms_stats._relation_stats_locked;
1659 SELECT dbms_stats.lock('s0.st0_idx');
1660 SELECT * FROM relations_locked_v;
1661 SELECT * FROM columns_locked_v c;
1663 DELETE FROM dbms_stats._relation_stats_locked;
1664 SELECT dbms_stats.lock('s0.ss0');
1666 DELETE FROM dbms_stats._relation_stats_locked;
1667 SELECT dbms_stats.lock('s0.sc0');
1669 DELETE FROM dbms_stats._relation_stats_locked;
1670 SELECT dbms_stats.lock('s0.sft0');
1671 SELECT * FROM relations_locked_v;
1672 SELECT * FROM columns_locked_v c;
1674 DELETE FROM dbms_stats._relation_stats_locked;
1675 SELECT dbms_stats.lock('pg_catalog.pg_class');
1677 DELETE FROM dbms_stats._relation_stats_locked;
1678 SELECT dbms_stats.lock_table_stats('s0.st0');
1679 UPDATE dbms_stats._relation_stats_locked
1680 SET (relpages, reltuples,
1686 WHERE relid = 's0.st0'::regclass;
1687 SELECT dbms_stats.lock('s0.st0');
1688 SELECT * FROM relations_locked_v;
1689 SELECT * FROM columns_locked_v c;
1691 DELETE FROM dbms_stats._relation_stats_locked;
1692 SELECT dbms_stats.lock('s0.st0');
1693 SELECT * FROM relations_locked_v;
1694 SELECT * FROM columns_locked_v c;
1697 * Stab function dbms_stats.lock
1699 ALTER FUNCTION dbms_stats.lock(relid regclass)
1700 RENAME TO truth_lock;
1701 CREATE FUNCTION dbms_stats.lock(relid regclass)
1705 RAISE NOTICE 'arguments are %', $1;
1711 ALTER FUNCTION dbms_stats.lock(relid regclass, attname text)
1712 RENAME TO truth_lock;
1713 CREATE FUNCTION dbms_stats.lock(
1719 RAISE NOTICE 'arguments are %, %', $1, $2;
1726 * No.12-1 dbms_stats.lock_database_stats
1729 SELECT dbms_stats.lock_database_stats();
1732 * No.12-2 dbms_stats.lock_schema_stats
1735 SELECT dbms_stats.lock_schema_stats('s0');
1737 SELECT dbms_stats.lock_schema_stats('s00');
1739 SELECT dbms_stats.lock_schema_stats('pg_catalog');
1742 * No.12-3 dbms_stats.lock_table_stats(regclass)
1745 SELECT dbms_stats.lock_table_stats('s0.st0');
1747 SELECT dbms_stats.lock_table_stats('st0');
1749 SELECT dbms_stats.lock_table_stats('s00.s0');
1752 * No.12-4 dbms_stats.lock_table_stats(schemaname, tablename)
1755 SELECT dbms_stats.lock_table_stats('s0', 'st0');
1758 * No.12-5 dbms_stats.lock_column_stats(regclass, attname)
1761 SELECT dbms_stats.lock_column_stats('s0.st0', 'id');
1763 SELECT dbms_stats.lock_column_stats('st0', 'id');
1765 SELECT dbms_stats.lock_column_stats('s00.s0', 'id');
1768 * No.12-6 dbms_stats.lock_column_stats(schemaname, tablename, int2)
1771 SELECT dbms_stats.lock_column_stats('s0', 'st0', 'id');
1774 * Delete Stab function lock
1776 DROP FUNCTION dbms_stats.lock(relid regclass);
1777 ALTER FUNCTION dbms_stats.truth_lock(relid regclass)
1779 DROP FUNCTION dbms_stats.lock(relid regclass, attname text);
1780 ALTER FUNCTION dbms_stats.truth_lock(relid regclass, attname text)
1784 * No.13-1 dbms_stats.unlock
1787 DELETE FROM dbms_stats._relation_stats_locked;
1788 SELECT dbms_stats.lock_database_stats();
1789 SELECT * FROM dbms_stats.backup_history
1791 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1792 SELECT count(*) FROM dbms_stats.column_stats_backup;
1793 SELECT dbms_stats.unlock();
1794 SELECT count(*) FROM dbms_stats._relation_stats_locked;
1795 SELECT count(*) FROM dbms_stats._column_stats_locked;
1796 SELECT * FROM dbms_stats.backup_history
1798 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1799 SELECT count(*) FROM dbms_stats.column_stats_backup;
1802 DELETE FROM dbms_stats._relation_stats_locked;
1803 SELECT dbms_stats.lock_database_stats();
1804 SELECT dbms_stats.unlock();
1805 SELECT count(*) FROM dbms_stats._relation_stats_locked;
1806 SELECT count(*) FROM dbms_stats._column_stats_locked;
1809 DELETE FROM dbms_stats._relation_stats_locked;
1810 SELECT dbms_stats.lock_database_stats();
1811 DELETE FROM dbms_stats._column_stats_locked;
1812 SELECT dbms_stats.unlock();
1813 SELECT count(*) FROM dbms_stats._relation_stats_locked;
1814 SELECT count(*) FROM dbms_stats._column_stats_locked;
1817 DELETE FROM dbms_stats._relation_stats_locked;
1818 SELECT dbms_stats.unlock();
1819 SELECT count(*) FROM dbms_stats._relation_stats_locked;
1820 SELECT count(*) FROM dbms_stats._column_stats_locked;
1823 DELETE FROM dbms_stats._relation_stats_locked;
1824 SELECT dbms_stats.lock_database_stats();
1825 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
1828 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
1831 SELECT dbms_stats.unlock('s0.st0');
1832 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
1835 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
1840 DELETE FROM dbms_stats._relation_stats_locked;
1841 SELECT dbms_stats.lock_database_stats();
1842 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
1845 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
1848 SELECT dbms_stats.unlock('st0');
1849 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
1852 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
1857 DELETE FROM dbms_stats._relation_stats_locked;
1858 SELECT dbms_stats.lock_database_stats();
1859 SELECT count(*) FROM dbms_stats._relation_stats_locked;
1860 SELECT count(*) FROM dbms_stats._column_stats_locked;
1861 SELECT dbms_stats.unlock('s00.s0');
1862 SELECT count(*) FROM dbms_stats._relation_stats_locked;
1863 SELECT count(*) FROM dbms_stats._column_stats_locked;
1866 DELETE FROM dbms_stats._relation_stats_locked;
1867 SELECT dbms_stats.lock_database_stats();
1868 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
1871 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1872 SELECT dbms_stats.unlock('s0.st0', 'id');
1873 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
1876 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1879 DELETE FROM dbms_stats._relation_stats_locked;
1880 SELECT dbms_stats.lock_database_stats();
1881 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
1884 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1885 SELECT dbms_stats.unlock('s0.st0', 'dummy');
1886 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
1889 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1892 DELETE FROM dbms_stats._relation_stats_locked;
1893 SELECT dbms_stats.lock_database_stats();
1894 DELETE FROM dbms_stats._column_stats_locked;
1895 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
1898 SELECT dbms_stats.unlock('s0.st0', 'id');
1899 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
1902 SELECT starelid::regclass, staattnum FROM dbms_stats._column_stats_locked
1903 GROUP BY starelid, staattnum
1907 DELETE FROM dbms_stats._relation_stats_locked;
1908 SELECT dbms_stats.lock_database_stats();
1909 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
1912 SELECT starelid::regclass, staattnum FROM dbms_stats._column_stats_locked
1913 GROUP BY starelid, staattnum
1915 SELECT dbms_stats.unlock(NULL, 'id');
1916 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
1919 SELECT starelid::regclass, staattnum FROM dbms_stats._column_stats_locked
1920 GROUP BY starelid, staattnum
1924 DELETE FROM dbms_stats._relation_stats_locked;
1925 SELECT dbms_stats.lock_database_stats();
1926 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
1929 SELECT starelid::regclass, staattnum FROM dbms_stats._column_stats_locked
1930 GROUP BY starelid, staattnum
1932 SELECT dbms_stats.unlock('s0.st0', NULL);
1933 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
1936 SELECT starelid::regclass, staattnum FROM dbms_stats._column_stats_locked
1937 GROUP BY starelid, staattnum
1941 DELETE FROM dbms_stats._relation_stats_locked;
1942 SELECT dbms_stats.lock_database_stats();
1943 SELECT count(*) FROM dbms_stats._relation_stats_locked;
1944 SELECT count(*) FROM dbms_stats._column_stats_locked;
1946 SELECT relation::regclass, mode
1947 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
1948 WHERE mode LIKE '%ExclusiveLock%'
1949 ORDER BY relation::regclass::text, mode;
1950 SELECT dbms_stats.unlock();
1951 SELECT relation::regclass, mode
1952 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
1953 WHERE mode LIKE '%ExclusiveLock%'
1954 ORDER BY relation::regclass::text, mode;
1956 SELECT count(*) FROM dbms_stats._relation_stats_locked;
1957 SELECT count(*) FROM dbms_stats._column_stats_locked;
1960 * No.14-1 dbms_stats.unlock_database_stats
1963 DELETE FROM dbms_stats._relation_stats_locked;
1964 SELECT dbms_stats.lock_database_stats();
1965 SELECT * FROM dbms_stats.backup_history
1967 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1968 SELECT count(*) FROM dbms_stats.column_stats_backup;
1969 SELECT count(*) FROM dbms_stats._relation_stats_locked;
1970 SELECT count(*) FROM dbms_stats._column_stats_locked;
1971 SELECT dbms_stats.unlock_database_stats();
1972 SELECT count(*) FROM dbms_stats._relation_stats_locked;
1973 SELECT count(*) FROM dbms_stats._column_stats_locked;
1974 SELECT * FROM dbms_stats.backup_history
1976 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1977 SELECT count(*) FROM dbms_stats.column_stats_backup;
1980 DELETE FROM dbms_stats._relation_stats_locked;
1981 SELECT dbms_stats.lock_database_stats();
1982 DELETE FROM dbms_stats._column_stats_locked;
1983 SELECT count(*) FROM dbms_stats._relation_stats_locked;
1984 SELECT dbms_stats.unlock_database_stats();
1985 SELECT count(*) FROM dbms_stats._relation_stats_locked;
1986 SELECT count(*) FROM dbms_stats._column_stats_locked;
1989 DELETE FROM dbms_stats._relation_stats_locked;
1990 SELECT dbms_stats.unlock_database_stats();
1991 SELECT count(*) FROM dbms_stats._relation_stats_locked;
1992 SELECT count(*) FROM dbms_stats._column_stats_locked;
1995 DELETE FROM dbms_stats._relation_stats_locked;
1996 SELECT dbms_stats.lock_database_stats();
1997 SELECT count(*) FROM dbms_stats._relation_stats_locked;
1998 SELECT count(*) FROM dbms_stats._column_stats_locked;
2000 SELECT relation::regclass, mode
2001 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
2002 WHERE mode LIKE '%ExclusiveLock%'
2003 ORDER BY relation::regclass::text, mode;
2004 SELECT dbms_stats.unlock_database_stats();
2005 SELECT relation::regclass, mode
2006 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
2007 WHERE mode LIKE '%ExclusiveLock%'
2008 ORDER BY relation::regclass::text, mode;
2010 SELECT count(*) FROM dbms_stats._relation_stats_locked;
2011 SELECT count(*) FROM dbms_stats._column_stats_locked;
2014 * No.14-2 dbms_stats.unlock_schema_stats
2017 DELETE FROM dbms_stats._relation_stats_locked;
2018 SELECT dbms_stats.lock_database_stats();
2019 SELECT * FROM dbms_stats.backup_history
2021 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2022 SELECT count(*) FROM dbms_stats.column_stats_backup;
2023 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2026 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2029 SELECT dbms_stats.unlock_schema_stats('s0');
2030 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2033 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2036 SELECT * FROM dbms_stats.backup_history
2038 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2039 SELECT count(*) FROM dbms_stats.column_stats_backup;
2042 DELETE FROM dbms_stats._relation_stats_locked;
2043 SELECT dbms_stats.lock_database_stats();
2044 DELETE FROM dbms_stats._column_stats_locked;
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
2060 DELETE FROM dbms_stats._relation_stats_locked;
2061 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2064 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2067 SELECT dbms_stats.unlock_schema_stats('s0');
2068 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2071 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2076 DELETE FROM dbms_stats._relation_stats_locked;
2077 SELECT dbms_stats.lock_database_stats();
2078 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2081 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2084 SELECT dbms_stats.unlock_schema_stats('s0');
2085 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2088 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2093 DELETE FROM dbms_stats._relation_stats_locked;
2094 SELECT dbms_stats.lock_database_stats();
2095 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2098 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2101 SELECT dbms_stats.unlock_schema_stats('s00');
2102 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2105 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2110 DELETE FROM dbms_stats._relation_stats_locked;
2111 SELECT dbms_stats.lock_database_stats();
2112 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2115 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2118 SELECT dbms_stats.unlock_schema_stats('pg_catalog');
2119 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2122 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2127 DELETE FROM dbms_stats._relation_stats_locked;
2128 SELECT dbms_stats.lock_database_stats();
2129 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2132 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2135 SELECT dbms_stats.unlock_schema_stats(NULL);
2136 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2139 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2144 DELETE FROM dbms_stats._relation_stats_locked;
2145 SELECT dbms_stats.lock_database_stats();
2146 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2149 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2153 SELECT relation::regclass, mode
2154 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
2155 WHERE mode LIKE '%ExclusiveLock%'
2156 ORDER BY relation::regclass::text, mode;
2157 SELECT dbms_stats.unlock_schema_stats('s0');
2158 SELECT relation::regclass, mode
2159 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
2160 WHERE mode LIKE '%ExclusiveLock%'
2161 ORDER BY relation::regclass::text, mode;
2163 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2166 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2171 * No.14-3 dbms_stats.unlock_table_stats(regclass)
2174 DELETE FROM dbms_stats._relation_stats_locked;
2175 SELECT dbms_stats.lock_database_stats();
2176 SELECT * FROM dbms_stats.backup_history
2178 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2179 SELECT count(*) FROM dbms_stats.column_stats_backup;
2180 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2183 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2186 SELECT dbms_stats.unlock_table_stats('s0.st0');
2187 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2190 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
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;
2199 DELETE FROM dbms_stats._relation_stats_locked;
2200 SELECT dbms_stats.lock_database_stats();
2201 DELETE FROM dbms_stats._column_stats_locked;
2202 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2205 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2208 SELECT dbms_stats.unlock_table_stats('s0.st0');
2209 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2212 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2217 DELETE FROM dbms_stats._relation_stats_locked;
2218 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2221 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2224 SELECT dbms_stats.unlock_table_stats('s0.st0');
2225 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2228 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2233 DELETE FROM dbms_stats._relation_stats_locked;
2234 SELECT dbms_stats.lock_database_stats();
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('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('s00.s0');
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(NULL);
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
2310 SELECT relation::regclass, mode
2311 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
2312 WHERE mode LIKE '%ExclusiveLock%'
2313 ORDER BY relation::regclass::text, mode;
2314 SELECT dbms_stats.unlock_table_stats('s0.st0');
2315 SELECT relation::regclass, mode
2316 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
2317 WHERE mode LIKE '%ExclusiveLock%'
2318 ORDER BY relation::regclass::text, mode;
2320 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2323 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2328 * No.14-4 dbms_stats.unlock_table_stats(schemaname, tablename)
2331 DELETE FROM dbms_stats._relation_stats_locked;
2332 SELECT dbms_stats.lock_database_stats();
2333 SELECT * FROM dbms_stats.backup_history
2335 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2336 SELECT count(*) FROM dbms_stats.column_stats_backup;
2337 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2340 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2343 SELECT dbms_stats.unlock_table_stats('s0','st0');
2344 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2347 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2350 SELECT * FROM dbms_stats.backup_history
2352 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2353 SELECT count(*) FROM dbms_stats.column_stats_backup;
2356 DELETE FROM dbms_stats._relation_stats_locked;
2357 SELECT dbms_stats.lock_database_stats();
2358 DELETE FROM dbms_stats._column_stats_locked;
2359 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2362 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2365 SELECT dbms_stats.unlock_table_stats('s0', 'st0');
2366 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2369 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2374 DELETE FROM dbms_stats._relation_stats_locked;
2375 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2378 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2381 SELECT dbms_stats.unlock_table_stats('s0', 'st0');
2382 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2385 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2390 DELETE FROM dbms_stats._relation_stats_locked;
2391 SELECT dbms_stats.lock_database_stats();
2392 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2395 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2398 SELECT dbms_stats.unlock_table_stats('s0', 'st0');
2399 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2402 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2407 DELETE FROM dbms_stats._relation_stats_locked;
2408 SELECT dbms_stats.lock_database_stats();
2409 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2412 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2415 SELECT dbms_stats.unlock_table_stats('s00', 's0');
2416 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2419 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2424 DELETE FROM dbms_stats._relation_stats_locked;
2425 SELECT dbms_stats.lock_database_stats();
2426 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2429 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2432 SELECT dbms_stats.unlock_table_stats(NULL, 'st0');
2433 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2436 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2441 DELETE FROM dbms_stats._relation_stats_locked;
2442 SELECT dbms_stats.lock_database_stats();
2443 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2446 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2449 SELECT dbms_stats.unlock_table_stats('s0', NULL);
2450 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2453 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2458 DELETE FROM dbms_stats._relation_stats_locked;
2459 SELECT dbms_stats.lock_database_stats();
2460 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2463 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2467 SELECT relation::regclass, mode
2468 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
2469 WHERE mode LIKE '%ExclusiveLock%'
2470 ORDER BY relation::regclass::text, mode;
2471 SELECT dbms_stats.unlock_table_stats('s0', 'st0');
2472 SELECT relation::regclass, mode
2473 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
2474 WHERE mode LIKE '%ExclusiveLock%'
2475 ORDER BY relation::regclass::text, mode;
2477 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2480 SELECT starelid::regclass, count(*) FROM dbms_stats._column_stats_locked
2485 * No.14-5 dbms_stats.unlock_column_stats(regclass, attname)
2488 DELETE FROM dbms_stats._relation_stats_locked;
2489 SELECT dbms_stats.lock_database_stats();
2490 SELECT * FROM dbms_stats.backup_history
2492 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2493 SELECT count(*) FROM dbms_stats.column_stats_backup;
2494 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2497 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2498 SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
2499 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2500 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2503 SELECT * FROM dbms_stats.backup_history
2505 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2506 SELECT count(*) FROM dbms_stats.column_stats_backup;
2509 DELETE FROM dbms_stats._relation_stats_locked;
2510 SELECT dbms_stats.lock_database_stats();
2511 DELETE FROM dbms_stats._column_stats_locked;
2512 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2515 SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
2516 SELECT count(*) FROM dbms_stats.column_stats_locked;
2517 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2522 DELETE FROM dbms_stats._relation_stats_locked;
2523 SELECT dbms_stats.lock_database_stats();
2524 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2527 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2528 SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
2529 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2530 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2535 DELETE FROM dbms_stats._relation_stats_locked;
2536 SELECT dbms_stats.lock_database_stats();
2537 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2540 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2541 SELECT dbms_stats.unlock_column_stats('st0', 'id');
2542 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2543 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2548 DELETE FROM dbms_stats._relation_stats_locked;
2549 SELECT dbms_stats.lock_database_stats();
2550 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2553 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2554 SELECT dbms_stats.unlock_column_stats('s0.st0', 'dummy');
2555 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2556 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2561 DELETE FROM dbms_stats._relation_stats_locked;
2562 SELECT dbms_stats.lock_database_stats();
2563 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2566 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2567 SELECT dbms_stats.unlock_column_stats('s00.s0', 'id');
2568 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2569 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2574 DELETE FROM dbms_stats._relation_stats_locked;
2575 SELECT dbms_stats.lock_database_stats();
2576 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2579 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2580 SELECT dbms_stats.unlock_column_stats(NULL, 'id');
2581 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2582 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2587 DELETE FROM dbms_stats._relation_stats_locked;
2588 SELECT dbms_stats.lock_database_stats();
2589 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2592 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2593 SELECT dbms_stats.unlock_column_stats('s0.st0', NULL);
2594 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2595 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2600 DELETE FROM dbms_stats._relation_stats_locked;
2601 SELECT dbms_stats.lock_database_stats();
2602 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2605 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2607 SELECT relation::regclass, mode
2608 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
2609 WHERE mode LIKE '%ExclusiveLock%'
2610 ORDER BY relation::regclass::text, mode;
2611 SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
2612 SELECT relation::regclass, mode
2613 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
2614 WHERE mode LIKE '%ExclusiveLock%'
2615 ORDER BY relation::regclass::text, mode;
2617 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2618 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2623 * No.14-6 dbms_stats.unlock_column_stats(schemaname, tablename, attname)
2626 DELETE FROM dbms_stats._relation_stats_locked;
2627 SELECT dbms_stats.lock_database_stats();
2628 SELECT * FROM dbms_stats.backup_history
2630 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2633 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2634 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
2635 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2636 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2639 SELECT * FROM dbms_stats.backup_history
2641 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2642 SELECT count(*) FROM dbms_stats.column_stats_backup;
2645 DELETE FROM dbms_stats._relation_stats_locked;
2646 SELECT dbms_stats.lock_database_stats();
2647 DELETE FROM dbms_stats._column_stats_locked;
2648 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2651 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
2652 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2653 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2658 DELETE FROM dbms_stats._relation_stats_locked;
2659 SELECT dbms_stats.lock_database_stats();
2660 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2663 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2664 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
2665 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2666 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2671 DELETE FROM dbms_stats._relation_stats_locked;
2672 SELECT dbms_stats.lock_database_stats();
2673 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2676 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2677 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'dummy');
2678 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2679 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2684 DELETE FROM dbms_stats._relation_stats_locked;
2685 SELECT dbms_stats.lock_database_stats();
2686 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2689 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2690 SELECT dbms_stats.unlock_column_stats(NULL, 'st0', 'id');
2691 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2692 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2697 DELETE FROM dbms_stats._relation_stats_locked;
2698 SELECT dbms_stats.lock_database_stats();
2699 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2702 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2703 SELECT dbms_stats.unlock_column_stats('s0', NULL, 'id');
2704 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2705 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2710 DELETE FROM dbms_stats._relation_stats_locked;
2711 SELECT dbms_stats.lock_database_stats();
2712 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2715 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2716 SELECT dbms_stats.unlock_column_stats('s0', 'st0', NULL);
2717 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2718 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2723 DELETE FROM dbms_stats._relation_stats_locked;
2724 SELECT dbms_stats.lock_database_stats();
2725 SELECT relid::regclass FROM dbms_stats._relation_stats_locked
2728 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2730 SELECT relation::regclass, mode
2731 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
2732 WHERE mode LIKE '%ExclusiveLock%'
2733 ORDER BY relation::regclass::text, mode;
2734 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
2735 SELECT relation::regclass, mode
2736 FROM pg_locks l join pg_class c on (l.relation = c.oid and c.relkind = 'r')
2737 WHERE mode LIKE '%ExclusiveLock%'
2738 ORDER BY relation::regclass::text, mode;
2740 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2741 SELECT relid::regclass FROM dbms_stats._relation_stats_locked