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
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,
60 stanumbers1 = ARRAY[staattnum,4],
61 stanumbers2 = ARRAY[staattnum,1],
62 stanumbers3 = ARRAY[staattnum,2],
63 stanumbers4 = ARRAY[staattnum,3],
64 stavalues2 = array_cat(stavalues1,stavalues1),
65 stavalues3 = array_cat(array_cat(stavalues1,stavalues1),stavalues1),
66 stavalues4 = array_cat(array_cat(array_cat(stavalues1,stavalues1),stavalues1),stavalues1)
67 WHERE starelid = 'st0'::regclass;
68 SELECT dbms_stats.lock_table_stats('st0');
69 UPDATE dbms_stats.column_stats_locked SET
71 stanullfrac = -staattnum,
72 stawidth = -staattnum,
73 stadistinct = -staattnum,
82 stanumbers1 = ARRAY[-staattnum,22],
83 stanumbers2 = ARRAY[-staattnum,23],
84 stanumbers3 = ARRAY[-staattnum,24],
85 stanumbers4 = ARRAY[-staattnum,21],
86 stavalues1 = stavalues3,
87 stavalues2 = stavalues2,
88 stavalues3 = stavalues1,
89 stavalues4 = stavalues4
93 * Driver function dbms_stats.merge1
95 CREATE FUNCTION dbms_stats.merge1(
96 lhs dbms_stats.column_stats_locked,
97 rhs pg_catalog.pg_statistic
99 '$libdir/pg_dbms_stats', 'dbms_stats_merge'
102 SELECT * FROM columns_locked_v
103 WHERE starelid = 'st0'::regclass;
104 SELECT * FROM plain_columns_statistic_v
105 WHERE starelid = 'st0'::regclass;
107 SET client_min_messages TO LOG;
110 SELECT (m.merge).starelid::regclass,
112 (m.merge).stainherit,
113 (m.merge).stanullfrac,
115 (m.merge).stadistinct,
124 (m.merge).stanumbers1,
125 (m.merge).stanumbers2,
126 (m.merge).stanumbers3,
127 (m.merge).stanumbers4,
128 (m.merge).stavalues1,
129 (m.merge).stavalues2,
130 (m.merge).stavalues3,
132 FROM (SELECT dbms_stats.merge(NULL, s)
134 WHERE starelid = 'st0'::regclass
135 AND staattnum = '1'::int2) m;
138 SELECT (m.merge).starelid::regclass,
140 (m.merge).stainherit,
141 (m.merge).stanullfrac,
143 (m.merge).stadistinct,
152 (m.merge).stanumbers1,
153 (m.merge).stanumbers2,
154 (m.merge).stanumbers3,
155 (m.merge).stanumbers4,
156 (m.merge).stavalues1,
157 (m.merge).stavalues2,
158 (m.merge).stavalues3,
160 FROM (SELECT dbms_stats.merge(v, NULL)
161 FROM dbms_stats.column_stats_locked v
162 WHERE starelid = 'st0'::regclass
163 AND staattnum = '2'::int2) m;
166 SELECT dbms_stats.merge(NULL, NULL);
169 SELECT (m.merge).starelid::regclass,
171 (m.merge).stainherit,
172 (m.merge).stanullfrac,
174 (m.merge).stadistinct,
183 (m.merge).stanumbers1,
184 (m.merge).stanumbers2,
185 (m.merge).stanumbers3,
186 (m.merge).stanumbers4,
187 (m.merge).stavalues1,
188 (m.merge).stavalues2,
189 (m.merge).stavalues3,
191 FROM (SELECT dbms_stats.merge(v, s)
192 FROM dbms_stats.column_stats_locked v,
194 WHERE v.starelid = 'st0'::regclass
195 AND v.staattnum = '2'::int2
196 AND s.starelid = 'st0'::regclass
197 AND s.staattnum = '1'::int2) m;
200 SELECT (m.merge).starelid::regclass,
202 (m.merge).stainherit,
203 (m.merge).stanullfrac,
205 (m.merge).stadistinct,
214 (m.merge).stanumbers1,
215 (m.merge).stanumbers2,
216 (m.merge).stanumbers3,
217 (m.merge).stanumbers4,
218 (m.merge).stavalues1,
219 (m.merge).stavalues2,
220 (m.merge).stavalues3,
222 FROM (SELECT dbms_stats.merge(v, s)
223 FROM dbms_stats.column_stats_locked v,
225 WHERE v.starelid = 'st0'::regclass
226 AND v.staattnum = '2'::int2
227 AND s.starelid = 'st0'::regclass
228 AND s.staattnum = '1'::int2) m;
231 SELECT dbms_stats.merge1(v, s)
232 FROM dbms_stats.column_stats_locked v,
234 WHERE v.starelid = 'st0'::regclass
235 AND v.staattnum = '2'::int2
236 AND s.starelid = 'st0'::regclass
237 AND s.staattnum = '1'::int2;
240 SELECT dbms_stats.merge(NULL, (
241 s.starelid::regclass, s.staattnum, s.stainherit,
242 s.stanullfrac, s.stawidth, s.stadistinct,
243 s.stakind1, s.stakind2, s.stakind3, s.stakind4,
244 s.staop1, s.staop2, s.staop3,
245 NULL, s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
246 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
249 WHERE s.starelid = 'st0'::regclass
250 AND s.staattnum = '1'::int2;
253 SELECT (m.merge).starelid::regclass,
255 (m.merge).stainherit,
256 (m.merge).stanullfrac,
258 (m.merge).stadistinct,
267 (m.merge).stanumbers1,
268 (m.merge).stanumbers2,
269 (m.merge).stanumbers3,
270 (m.merge).stanumbers4,
271 (m.merge).stavalues1,
272 (m.merge).stavalues2,
273 (m.merge).stavalues3,
275 FROM (SELECT dbms_stats.merge(NULL, (
276 s.starelid::regclass, s.staattnum, s.stainherit,
277 s.stanullfrac, s.stawidth, s.stadistinct,
278 s.stakind1, s.stakind2, s.stakind3, s.stakind4,
279 s.staop1, s.staop2, s.staop3, s.staop4,
280 NULL, s.stanumbers2, s.stanumbers3, s.stanumbers4,
281 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
284 WHERE s.starelid = 'st0'::regclass
285 AND s.staattnum = '1'::int2) m;
288 SELECT dbms_stats.merge((
289 v.starelid::regclass, v.staattnum, v.stainherit,
290 v.stanullfrac, v.stawidth, v.stadistinct,
291 v.stakind1, v.stakind2, v.stakind3, v.stakind4,
292 v.staop1, v.staop2, v.staop3,
293 NULL, v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
294 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
296 FROM dbms_stats.column_stats_locked v
297 WHERE v.starelid = 'st0'::regclass
298 AND v.staattnum = '2'::int2;
301 SELECT (m.merge).starelid::regclass,
303 (m.merge).stainherit,
304 (m.merge).stanullfrac,
306 (m.merge).stadistinct,
315 (m.merge).stanumbers1,
316 (m.merge).stanumbers2,
317 (m.merge).stanumbers3,
318 (m.merge).stanumbers4,
319 (m.merge).stavalues1,
320 (m.merge).stavalues2,
321 (m.merge).stavalues3,
323 FROM (SELECT dbms_stats.merge((
324 v.starelid::regclass, v.staattnum, v.stainherit,
325 v.stanullfrac, v.stawidth, v.stadistinct,
326 v.stakind1, v.stakind2, v.stakind3, v.stakind4,
327 v.staop1, v.staop2, v.staop3, v.staop4,
328 NULL, v.stanumbers2, v.stanumbers3, v.stanumbers4,
329 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
331 FROM dbms_stats.column_stats_locked v
332 WHERE v.starelid = 'st0'::regclass
333 AND v.staattnum = '2'::int2) m;
336 SELECT dbms_stats.merge((
337 v.starelid::regclass, v.staattnum, v.stainherit,
338 v.stanullfrac, v.stawidth, v.stadistinct,
339 v.stakind1, v.stakind2, v.stakind3, v.stakind4,
340 v.staop1, v.staop2, v.staop3,
341 NULL, v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
342 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
344 s.starelid::regclass, s.staattnum, s.stainherit,
345 s.stanullfrac, s.stawidth, s.stadistinct,
346 s.stakind1, s.stakind2, s.stakind3, s.stakind4,
347 s.staop1, s.staop2, s.staop3,
348 NULL, s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
349 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
351 FROM dbms_stats.column_stats_locked v,
353 WHERE v.starelid = 'st0'::regclass
354 AND v.staattnum = '2'::int2
355 AND s.starelid = 'st0'::regclass
356 AND s.staattnum = '1'::int2;
359 SELECT (m.merge).starelid::regclass,
361 (m.merge).stainherit,
362 (m.merge).stanullfrac,
364 (m.merge).stadistinct,
373 (m.merge).stanumbers1,
374 (m.merge).stanumbers2,
375 (m.merge).stanumbers3,
376 (m.merge).stanumbers4,
377 (m.merge).stavalues1,
378 (m.merge).stavalues2,
379 (m.merge).stavalues3,
381 FROM (SELECT dbms_stats.merge((
382 v.starelid::regclass, v.staattnum, v.stainherit,
383 v.stanullfrac, v.stawidth, v.stadistinct,
384 v.stakind1, v.stakind2, v.stakind3, v.stakind4,
385 v.staop1, v.staop2, v.staop3, v.staop4,
386 NULL, v.stanumbers2, v.stanumbers3, v.stanumbers4,
387 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
389 s.starelid::regclass, s.staattnum, s.stainherit,
390 s.stanullfrac, s.stawidth, s.stadistinct,
391 s.stakind1, s.stakind2, s.stakind3, s.stakind4,
392 s.staop1, s.staop2, s.staop3, s.staop4,
393 NULL, s.stanumbers2, s.stanumbers3, s.stanumbers4,
394 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
396 FROM dbms_stats.column_stats_locked v,
398 WHERE v.starelid = 'st0'::regclass
399 AND v.staattnum = '2'::int2
400 AND s.starelid = 'st0'::regclass
401 AND s.staattnum = '1'::int2) m;
404 SELECT (m.merge).starelid::regclass,
406 (m.merge).stainherit,
407 (m.merge).stanullfrac,
409 (m.merge).stadistinct,
418 (m.merge).stanumbers1,
419 (m.merge).stanumbers2,
420 (m.merge).stanumbers3,
421 (m.merge).stanumbers4,
422 (m.merge).stavalues1,
423 (m.merge).stavalues2,
424 (m.merge).stavalues3,
426 FROM (SELECT dbms_stats.merge((
429 NULL, NULL, NULL, NULL,
430 NULL, NULL, NULL, NULL,
431 NULL, NULL, NULL, NULL,
432 NULL, NULL, NULL, NULL
435 WHERE s.starelid = 'st0'::regclass
436 AND s.staattnum = '1'::int2) m;
439 SELECT (m.merge).starelid::regclass,
441 (m.merge).stainherit,
442 (m.merge).stanullfrac,
444 (m.merge).stadistinct,
453 (m.merge).stanumbers1,
454 (m.merge).stanumbers2,
455 (m.merge).stanumbers3,
456 (m.merge).stanumbers4,
457 (m.merge).stavalues1,
458 (m.merge).stavalues2,
459 (m.merge).stavalues3,
461 FROM (SELECT dbms_stats.merge(v, (
464 NULL, NULL, NULL, NULL,
465 NULL, NULL, NULL, NULL,
466 NULL, NULL, NULL, NULL,
467 NULL, NULL, NULL, NULL))
468 FROM dbms_stats.column_stats_locked v
469 WHERE v.starelid = 'st0'::regclass
470 AND v.staattnum = '2'::int2) m;
473 SELECT (m.merge).starelid::regclass,
475 (m.merge).stainherit,
476 (m.merge).stanullfrac,
478 (m.merge).stadistinct,
487 (m.merge).stanumbers1,
488 (m.merge).stanumbers2,
489 (m.merge).stanumbers3,
490 (m.merge).stanumbers4,
491 (m.merge).stavalues1,
492 (m.merge).stavalues2,
493 (m.merge).stavalues3,
495 FROM (SELECT dbms_stats.merge(v, s)
496 FROM dbms_stats.column_stats_locked v,
498 WHERE v.starelid = 'st0'::regclass
499 AND v.staattnum = '2'::int2
500 AND s.starelid = 'st0'::regclass
501 AND s.staattnum = '1'::int2) m;
504 SELECT dbms_stats.merge((
505 v.starelid::regclass, v.staattnum, v.stainherit,
506 v.stanullfrac, v.stawidth, v.stadistinct,
507 NULL, NULL, NULL, NULL,
508 NULL, NULL, NULL, NULL,
509 NULL, NULL, NULL, NULL,
510 NULL, NULL, NULL, NULL
512 s.starelid::regclass, s.staattnum, s.stainherit,
513 s.stanullfrac, s.stawidth, s.stadistinct,
514 NULL, NULL, NULL, NULL,
515 NULL, NULL, NULL, NULL,
516 NULL, NULL, NULL, NULL,
517 NULL, NULL, NULL, NULL))
518 FROM dbms_stats.column_stats_locked v,
520 WHERE v.starelid = 'st0'::regclass
521 AND v.staattnum = '2'::int2
522 AND s.starelid = 'st0'::regclass
523 AND s.staattnum = '1'::int2;
526 SELECT (m.merge).starelid::regclass,
528 (m.merge).stainherit,
529 (m.merge).stanullfrac,
531 (m.merge).stadistinct,
540 (m.merge).stanumbers1,
541 (m.merge).stanumbers2,
542 (m.merge).stanumbers3,
543 (m.merge).stanumbers4,
544 (m.merge).stavalues1,
545 (m.merge).stavalues2,
546 (m.merge).stavalues3,
548 FROM (SELECT dbms_stats.merge((
549 v.starelid::regclass, v.staattnum, v.stainherit,
550 v.stanullfrac, v.stawidth, v.stadistinct,
551 v.stakind1, v.stakind2, v.stakind3, v.stakind4,
552 v.staop1, v.staop2, v.staop3,
553 NULL, v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
554 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
556 s.starelid::regclass, s.staattnum, s.stainherit,
557 s.stanullfrac, s.stawidth, s.stadistinct,
558 s.stakind1, s.stakind2, s.stakind3, s.stakind4,
559 s.staop1, s.staop2, s.staop3, s.staop4,
560 s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
561 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
563 FROM dbms_stats.column_stats_locked v,
565 WHERE v.starelid = 'st0'::regclass
566 AND v.staattnum = '1'::int2
567 AND s.starelid = 'st0'::regclass
568 AND s.staattnum = '1'::int2) m;
571 SELECT dbms_stats.merge((
572 v.starelid::regclass, v.staattnum, v.stainherit,
573 v.stanullfrac, v.stawidth, v.stadistinct,
574 v.stakind1, v.stakind2, v.stakind3, v.stakind4,
575 v.staop1, v.staop2, v.staop3,
576 NULL, v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
577 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
579 s.starelid::regclass, s.staattnum, s.stainherit,
580 s.stanullfrac, s.stawidth, s.stadistinct,
581 s.stakind1, s.stakind2, s.stakind3, s.stakind4,
582 s.staop1, s.staop2, s.staop3,
583 NULL, s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
584 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
586 FROM dbms_stats.column_stats_locked v,
588 WHERE v.starelid = 'st0'::regclass
589 AND v.staattnum = '1'::int2
590 AND s.starelid = 'st0'::regclass
591 AND s.staattnum = '1'::int2;
594 SELECT (m.merge).starelid::regclass,
596 (m.merge).stainherit,
597 (m.merge).stanullfrac,
599 (m.merge).stadistinct,
608 (m.merge).stanumbers1,
609 (m.merge).stanumbers2,
610 (m.merge).stanumbers3,
611 (m.merge).stanumbers4,
612 (m.merge).stavalues1,
613 (m.merge).stavalues2,
614 (m.merge).stavalues3,
616 FROM (SELECT dbms_stats.merge((
617 v.starelid::regclass, v.staattnum, v.stainherit,
618 v.stanullfrac, v.stawidth, v.stadistinct,
620 v.staop1, v.staop2, v.staop3, v.staop4,
621 v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
622 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
624 s.starelid::regclass, s.staattnum, s.stainherit,
625 s.stanullfrac, s.stawidth, s.stadistinct,
627 s.staop1, s.staop2, s.staop3, s.staop4,
628 s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
629 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
631 FROM dbms_stats.column_stats_locked v,
633 WHERE v.starelid = 'st0'::regclass
634 AND v.staattnum = '2'::int2
635 AND s.starelid = 'st0'::regclass
636 AND s.staattnum = '1'::int2) m;
639 SELECT (m.merge).starelid::regclass,
641 (m.merge).stainherit,
642 (m.merge).stanullfrac,
644 (m.merge).stadistinct,
653 (m.merge).stanumbers1,
654 (m.merge).stanumbers2,
655 (m.merge).stanumbers3,
656 (m.merge).stanumbers4,
657 (m.merge).stavalues1,
658 (m.merge).stavalues2,
659 (m.merge).stavalues3,
661 FROM (SELECT dbms_stats.merge((v.starelid::regclass, v.staattnum, v.stainherit,
662 v.stanullfrac, v.stawidth, v.stadistinct,
664 v.staop1, v.staop2, v.staop3, v.staop4,
665 v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
666 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
668 s.starelid::regclass, s.staattnum, s.stainherit,
669 s.stanullfrac, s.stawidth, s.stadistinct,
671 s.staop1, s.staop2, s.staop3, s.staop4,
672 s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
673 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
675 FROM dbms_stats.column_stats_locked v,
677 WHERE v.starelid = 'st0'::regclass
678 AND v.staattnum = '2'::int2
679 AND s.starelid = 'st0'::regclass
680 AND s.staattnum = '1'::int2) m;
683 SELECT dbms_stats.merge((v.starelid::regclass, '2', v.stainherit,
684 v.stanullfrac, v.stawidth, v.stadistinct,
686 v.staop1, v.staop2, v.staop3, v.staop4,
687 v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
688 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
690 s.starelid::regclass, s.staattnum, s.stainherit,
691 s.stanullfrac, s.stawidth, s.stadistinct,
693 s.staop1, s.staop2, s.staop3, s.staop4,
694 s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
695 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
697 FROM dbms_stats.column_stats_locked v,
699 WHERE v.starelid = 'st0'::regclass
700 AND v.staattnum = '1'::int2
701 AND s.starelid = 'st0'::regclass
702 AND s.staattnum = '1'::int2;
705 SELECT dbms_stats.merge((v.starelid::regclass, '2', v.stainherit,
706 v.stanullfrac, v.stawidth, v.stadistinct,
708 v.staop1, v.staop2, v.staop3, v.staop4,
709 v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
710 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
712 s.starelid::regclass, s.staattnum, s.stainherit,
713 s.stanullfrac, s.stawidth, s.stadistinct,
715 s.staop1, s.staop2, s.staop3, s.staop4,
716 s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
717 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
719 FROM dbms_stats.column_stats_locked v,
721 WHERE v.starelid = 'st0'::regclass
722 AND v.staattnum = '1'::int2
723 AND s.starelid = 'st0'::regclass
724 AND s.staattnum = '1'::int2;
725 RESET client_min_messages;
726 SELECT dbms_stats.unlock_database_stats();
729 * No.6-4 dbms_stats.is_target_relkind
732 SELECT dbms_stats.is_target_relkind(k::"char")
733 FROM (VALUES ('r'), ('i'), ('f'), ('m'),
734 ('S'), ('t'), ('v'), ('c')) t(k);
737 * No.7-1 dbms_stats.backup
739 DELETE FROM dbms_stats.backup_history;
740 INSERT INTO dbms_stats.backup_history(id, time, unit) values(1, '2012-01-01', 'd');
742 SELECT dbms_stats.backup(1, 's0.sft0'::regclass, NULL);
743 SELECT count(*) FROM dbms_stats.relation_stats_backup;
744 SELECT count(*) FROM dbms_stats.column_stats_backup;
747 -- #### 9.1 doesn't has materialized views
748 -- DELETE FROM dbms_stats.relation_stats_backup;
749 -- SELECT dbms_stats.backup(1, 's0.smv0'::regclass, NULL);
750 -- SELECT count(*) FROM dbms_stats.relation_stats_backup;
751 -- SELECT count(*) FROM dbms_stats.column_stats_backup;
754 DELETE FROM dbms_stats.relation_stats_backup;
755 SELECT dbms_stats.backup(1, NULL, 1::int2);
756 SELECT relid::regclass FROM dbms_stats.relation_stats_backup
759 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_backup
760 GROUP BY starelid, staattnum
761 ORDER BY starelid, staattnum;
764 DELETE FROM dbms_stats.relation_stats_backup;
765 SELECT dbms_stats.backup(1, NULL::regclass, NULL);
766 SELECT relid::regclass FROM dbms_stats.relation_stats_backup
769 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_backup
770 GROUP BY starelid, staattnum
771 ORDER BY starelid, staattnum;
774 DELETE FROM dbms_stats.relation_stats_backup;
775 SELECT dbms_stats.backup(NULL, 's0.st0'::regclass, NULL);
776 SELECT count(*) FROM dbms_stats.relation_stats_backup;
777 SELECT count(*) FROM dbms_stats.column_stats_backup;
780 * No.8-1 dbms_stats.backup
782 SELECT setval('dbms_stats.backup_history_id_seq',1, false);
784 * Stab function dbms_stats.backup
786 ALTER FUNCTION dbms_stats.backup(
790 RENAME TO truth_func_backup;
792 CREATE OR REPLACE FUNCTION dbms_stats.backup(
799 RAISE NOTICE 'arguments are %, %, %', $1, $2, $3;
806 DELETE FROM dbms_stats.backup_history;
807 SELECT dbms_stats.backup('s0.st0'::regclass, 'id', 'dummy comment');
808 SELECT id, unit, comment FROM dbms_stats.backup_history;
811 DELETE FROM dbms_stats.backup_history;
812 SELECT dbms_stats.backup('s0.st0'::regclass, NULL, 'dummy comment');
813 SELECT id, unit, comment FROM dbms_stats.backup_history;
816 DELETE FROM dbms_stats.backup_history;
817 SELECT dbms_stats.backup(NULL::regclass, 'id', 'dummy comment');
818 SELECT id, unit, comment FROM dbms_stats.backup_history;
821 DELETE FROM dbms_stats.backup_history;
822 SELECT dbms_stats.backup(NULL::regclass, NULL, 'dummy comment');
823 SELECT id, unit, comment FROM dbms_stats.backup_history;
826 DELETE FROM dbms_stats.backup_history;
827 SELECT dbms_stats.backup(0, NULL, 'dummy comment');
828 SELECT id, unit, comment FROM dbms_stats.backup_history;
831 DELETE FROM dbms_stats.backup_history;
832 SELECT dbms_stats.backup('s0.st0'::regclass, NULL, 'dummy comment');
833 SELECT id, unit, comment FROM dbms_stats.backup_history;
836 DELETE FROM dbms_stats.backup_history;
837 SELECT dbms_stats.backup(
838 'pg_toast.pg_toast_2618'::regclass,
841 SELECT id, unit, comment FROM dbms_stats.backup_history;
844 DELETE FROM dbms_stats.backup_history;
845 SELECT dbms_stats.backup('s0.st0_idx'::regclass, NULL, 'dummy comment');
846 SELECT id, unit, comment FROM dbms_stats.backup_history;
849 DELETE FROM dbms_stats.backup_history;
850 SELECT dbms_stats.backup('s0.ss0'::regclass, NULL, 'dummy comment');
851 SELECT id, unit, comment FROM dbms_stats.backup_history;
854 DELETE FROM dbms_stats.backup_history;
855 SELECT dbms_stats.backup('s0.sc0'::regclass, NULL, 'dummy comment');
856 SELECT id, unit, comment FROM dbms_stats.backup_history;
859 DELETE FROM dbms_stats.backup_history;
860 SELECT dbms_stats.backup('s0.sft0'::regclass, NULL, 'dummy comment');
861 SELECT id, unit, comment FROM dbms_stats.backup_history;
864 -- #### 9.1 doesn't has materialized views
865 -- DELETE FROM dbms_stats.backup_history;
866 -- SELECT dbms_stats.backup('s0.smv0'::regclass, NULL, 'dummy comment');
867 -- SELECT id, unit, comment FROM dbms_stats.backup_history;
870 DELETE FROM dbms_stats.backup_history;
871 SELECT dbms_stats.backup('pg_catalog.pg_class'::regclass, NULL, 'dummy comment');
872 SELECT id, unit, comment FROM dbms_stats.backup_history;
875 DELETE FROM dbms_stats.backup_history;
876 SELECT dbms_stats.backup('s0.st0'::regclass, 'dummy', 'dummy comment');
877 SELECT id, unit, comment FROM dbms_stats.backup_history;
880 DELETE FROM dbms_stats.backup_history;
881 DELETE FROM pg_statistic
882 WHERE starelid = 's0.st0'::regclass
883 AND staattnum = 1::int2;
884 SELECT count(*) FROM dbms_stats.column_stats_effective
885 WHERE starelid = 's0.st0'::regclass
886 AND staattnum = 1::int2;
887 SELECT dbms_stats.backup('s0.st0'::regclass, 'id', 'dummy comment');
888 SELECT id, unit, comment FROM dbms_stats.backup_history;
891 * Stab function dbms_stats.backup
893 ALTER FUNCTION dbms_stats.backup(
897 RENAME TO truth_func_backup;
898 CREATE OR REPLACE FUNCTION dbms_stats.backup(
899 relid regclass DEFAULT NULL,
900 attname text DEFAULT NULL,
901 comment text DEFAULT NULL)
905 IF $3 = '<NULL>' THEN
906 RAISE NOTICE 'third argument is not NULL but string "<NULL>"';
908 RAISE NOTICE 'arguments are %, %, %', $1, $2, $3;
915 * No.8-3 dbms_stats.backup_schema_stats
917 SELECT setval('dbms_stats.backup_history_id_seq',9, false);
919 SELECT dbms_stats.backup_schema_stats('s0', 'comment');
920 SELECT id, unit, comment FROM dbms_stats.backup_history
924 SELECT dbms_stats.backup_schema_stats('s00', 'comment');
925 SELECT id, unit, comment FROM dbms_stats.backup_history
929 SELECT dbms_stats.backup_schema_stats('pg_catalog', 'comment');
930 SELECT id, unit, comment FROM dbms_stats.backup_history
935 * Delete stab function dbms_stats.backup
937 DROP FUNCTION dbms_stats.backup(
941 ALTER FUNCTION dbms_stats.truth_func_backup(
946 DROP FUNCTION dbms_stats.backup(
950 ALTER FUNCTION dbms_stats.truth_func_backup(
958 * create backup statistics state A
960 DELETE FROM dbms_stats.backup_history;
962 INSERT INTO dbms_stats.backup_history(id, time, unit)
963 VALUES (1, '2012-02-29 23:59:56.999999', 'd');
965 SELECT setval('dbms_stats.backup_history_id_seq',1);
966 SELECT dbms_stats.backup();
967 UPDATE dbms_stats.backup_history
968 SET time = '2012-02-29 23:59:57'
970 SELECT dbms_stats.backup('s0.st0');
971 UPDATE dbms_stats.backup_history
972 SET time = '2012-02-29 23:59:57.000001'
974 SELECT dbms_stats.backup();
975 UPDATE dbms_stats.backup_history
976 SET time = '2012-02-29 23:59:58'
978 DELETE FROM dbms_stats.relation_stats_backup
980 SELECT dbms_stats.backup('s0.st0', 'id');
981 UPDATE dbms_stats.backup_history
982 SET time = '2012-03-01 00:00:00'
984 SELECT dbms_stats.backup('s0.st0');
985 UPDATE dbms_stats.backup_history
986 SET time = '2012-03-01 00:00:02'
988 SELECT dbms_stats.backup('public.st0');
989 UPDATE dbms_stats.backup_history
990 SET time = '2012-03-01 00:00:04'
992 INSERT INTO dbms_stats.backup_history(time, unit)
993 VALUES ('2012-03-01 00:00:06', 's');
994 SELECT dbms_stats.backup(8, c.oid, NULL)
995 FROM pg_catalog.pg_class c,
996 pg_catalog.pg_namespace n
997 WHERE n.nspname = 's0'
998 AND c.relnamespace = n.oid
999 AND c.relkind IN ('r', 'i');
1001 SELECT * FROM dbms_stats.backup_history
1003 SELECT * FROM relations_backup_v;
1004 SELECT * FROM columns_backup_v;
1009 * No.9-1 dbms_stats.restore
1012 DELETE FROM dbms_stats.relation_stats_locked;
1013 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
1015 SELECT * FROM internal_locks;
1016 SELECT dbms_stats.restore(2, 's0.st0', NULL);
1017 SELECT * FROM internal_locks;
1019 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1022 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1025 DELETE FROM dbms_stats.relation_stats_locked;
1026 SELECT dbms_stats.restore(2, 'st0', NULL);
1027 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1030 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1033 DELETE FROM dbms_stats.relation_stats_locked;
1034 SELECT dbms_stats.restore(2, 's00.s0', NULL);
1035 SELECT count(*) FROM dbms_stats.column_stats_locked;
1036 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1039 DELETE FROM dbms_stats.relation_stats_locked;
1040 SELECT dbms_stats.restore(NULL, 's0.st0', NULL);
1041 SELECT count(*) FROM dbms_stats.column_stats_locked;
1042 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1045 DELETE FROM dbms_stats.relation_stats_locked;
1046 SELECT dbms_stats.restore(2, 's0.st0', 'id');
1047 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1050 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1053 DELETE FROM dbms_stats.relation_stats_locked;
1054 SELECT dbms_stats.restore(2, NULL, 'id');
1055 SELECT count(*) FROM dbms_stats.column_stats_locked;
1056 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1059 DELETE FROM dbms_stats.relation_stats_locked;
1060 SELECT dbms_stats.restore(2, 's0.st0', NULL);
1061 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1064 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1067 DELETE FROM dbms_stats.relation_stats_locked;
1068 SELECT dbms_stats.restore(2, NULL, NULL);
1069 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1072 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1075 DELETE FROM dbms_stats.relation_stats_locked;
1076 SELECT dbms_stats.restore(0, 's0.st0', NULL);
1077 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1080 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1083 DELETE FROM dbms_stats.relation_stats_locked;
1084 SELECT dbms_stats.restore(2, 0, 'id');
1085 SELECT count(*) FROM dbms_stats.column_stats_locked;
1086 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1089 DELETE FROM dbms_stats.relation_stats_locked;
1090 SELECT dbms_stats.restore(1, 's0.st0', NULL);
1091 SELECT count(*) FROM dbms_stats.column_stats_locked;
1092 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1095 DELETE FROM dbms_stats.relation_stats_locked;
1096 SELECT dbms_stats.restore(2, 's0.st0', 'dummy');
1097 SELECT count(*) FROM dbms_stats.column_stats_locked;
1098 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1101 DELETE FROM dbms_stats.relation_stats_locked;
1102 SELECT dbms_stats.restore(1, 's0.st0', 'id');
1103 SELECT count(*) FROM dbms_stats.column_stats_locked;
1104 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1107 DELETE FROM dbms_stats.relation_stats_locked;
1108 ALTER TABLE s1.st0 DROP COLUMN id;
1109 SELECT dbms_stats.restore(2, 's1.st0', 'id');
1110 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1113 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1116 DELETE FROM dbms_stats.relation_stats_locked;
1117 \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';"`
1119 -- SELECT dbms_stats.restore(2, :s1_st0_oid, NULL);
1120 -- To avoid test unstability caused by relation id alloction, the test
1121 -- above is omitted.
1123 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1124 SELECT count(*) FROM dbms_stats.column_stats_locked;
1125 CREATE TABLE s1.st0(id integer, num integer);
1126 INSERT INTO s1.st0 VALUES (1, 15), (2, 25), (3, 35), (4, 45);
1129 DELETE FROM dbms_stats.relation_stats_locked;
1130 SELECT dbms_stats.restore(2, 's0.st0', NULL);
1131 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1134 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1137 DELETE FROM dbms_stats.relation_stats_locked;
1138 INSERT INTO dbms_stats.relation_stats_backup(
1139 id, relid, relname, relpages, reltuples,
1142 'pg_toast.pg_toast_2618'::regclass,
1143 'pg_toast.pg_toast_2618', 1, 1,
1145 SELECT * FROM relations_backup_v
1147 AND relname = 'pg_toast.pg_toast_2618';
1148 SELECT dbms_stats.restore(2, 'pg_toast.pg_toast_2618', NULL);
1149 SELECT count(*) FROM dbms_stats.column_stats_locked;
1150 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1151 DELETE FROM dbms_stats.relation_stats_backup
1153 AND relname = 'pg_toast.pg_toast_2618';
1156 DELETE FROM dbms_stats.relation_stats_locked;
1157 SELECT dbms_stats.restore(2, 's0.st0_idx', NULL);
1158 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1161 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1164 DELETE FROM dbms_stats.relation_stats_locked;
1165 INSERT INTO dbms_stats.relation_stats_backup(
1166 id, relid, relname, relpages, reltuples,
1168 VALUES (2, 's0.ss0'::regclass, 's0.ss0', 1, 1,
1170 SELECT * FROM relations_backup_v
1172 AND relname = 's0.ss0';
1173 SELECT dbms_stats.restore(2, 's0.ss0', NULL);
1174 SELECT count(*) FROM dbms_stats.column_stats_locked;
1175 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1176 DELETE FROM dbms_stats.relation_stats_backup
1178 AND relname = 's0.ss0';
1181 DELETE FROM dbms_stats.relation_stats_locked;
1182 INSERT INTO dbms_stats.relation_stats_backup(
1183 id, relid, relname, relpages, reltuples,
1185 VALUES (2, 's0.sc0'::regclass, 's0.sc0', 1, 1,
1187 SELECT * FROM relations_backup_v
1189 AND relname = 's0.sc0';
1190 SELECT dbms_stats.restore(2, 's0.sc0', NULL);
1191 SELECT count(*) FROM dbms_stats.column_stats_locked;
1192 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1193 DELETE FROM dbms_stats.relation_stats_backup
1195 AND relname = 's0.sc0';
1198 DELETE FROM dbms_stats.relation_stats_locked;
1199 INSERT INTO dbms_stats.relation_stats_backup(
1200 id, relid, relname, relpages, reltuples,
1202 VALUES (2, 's0.sft0'::regclass, 's0.sft0', 1, 1,
1204 SELECT * FROM relations_backup_v
1206 AND relname = 's0.sft0';
1207 SELECT dbms_stats.restore(2, 's0.sft0', NULL);
1208 SELECT count(*) FROM dbms_stats.column_stats_locked;
1209 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1210 DELETE FROM dbms_stats.relation_stats_backup
1212 AND relname = 's0.sft0';
1215 -- #### 9.1 doesn't has materialized views
1216 -- DELETE FROM dbms_stats.relation_stats_locked;
1217 -- INSERT INTO dbms_stats.relation_stats_backup(
1218 -- id, relid, relname, relpages, reltuples,
1221 -- VALUES (3, 's0.smv0'::regclass, 's0.smv0', 1, 1,
1224 -- SELECT * FROM relations_backup_v
1226 -- AND relname = 's0.smv0';
1227 -- SELECT dbms_stats.restore(2, 's0.smv0', NULL);
1228 -- SELECT count(*) FROM dbms_stats.column_stats_locked;
1229 -- SELECT count(*) FROM dbms_stats.relation_stats_locked;
1230 -- DELETE FROM dbms_stats.relation_stats_backup
1232 -- AND relname = 's0.smv0';
1235 DELETE FROM dbms_stats.relation_stats_locked;
1236 INSERT INTO dbms_stats.relation_stats_backup(
1237 id, relid, relname, relpages, reltuples,
1239 VALUES (2, 'pg_catalog.pg_class'::regclass, 'pg_catalog.pg_class', 1, 1,
1241 SELECT * FROM relations_backup_v
1243 AND relname = 'pg_catalog.pg_class';
1244 SELECT dbms_stats.restore(2, 'pg_catalog.pg_class', NULL);
1245 SELECT count(*) FROM dbms_stats.column_stats_locked;
1246 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1247 DELETE FROM dbms_stats.relation_stats_backup
1249 AND relname = 'pg_catalog.pg_class';
1252 DELETE FROM dbms_stats.relation_stats_locked;
1253 INSERT INTO dbms_stats.relation_stats_locked(relid, relname)
1254 VALUES ('s0.st0'::regclass, 's0.st0');
1255 INSERT INTO dbms_stats.column_stats_locked(starelid, staattnum, stainherit)
1256 SELECT starelid::regclass, staattnum, stainherit
1257 FROM dbms_stats.column_stats_effective
1258 WHERE starelid = 's0.st0'::regclass;
1259 SELECT id, unit, comment FROM dbms_stats.backup_history
1261 SELECT * FROM columns_locked_v;
1262 SELECT * FROM relations_locked_v;
1263 SELECT dbms_stats.restore(2, 's0.st0', NULL);
1264 SELECT * FROM relations_locked_v;
1265 SELECT * FROM columns_locked_v;
1268 DELETE FROM dbms_stats.relation_stats_locked;
1269 SELECT id, unit, comment FROM dbms_stats.backup_history
1271 SELECT dbms_stats.restore(2, 's0.st0', NULL);
1272 SELECT * FROM relations_locked_v;
1273 SELECT * FROM columns_locked_v;
1276 * Stab function dbms_stats.restore
1278 CREATE OR REPLACE FUNCTION dbms_stats.restore(
1280 relid regclass DEFAULT NULL,
1281 attname text DEFAULT NULL)
1282 RETURNS SETOF regclass AS
1285 RAISE NOTICE 'arguments are "%, %, %"', $1, $2, $3;
1287 SELECT c.oid::regclass
1288 FROM pg_class c, dbms_stats.relation_stats_backup b
1289 WHERE (c.oid = $2 OR $2 IS NULL)
1291 AND c.relkind IN ('r', 'i')
1292 AND (b.id <= $1 OR $1 IS NOT NULL)
1294 ORDER BY c.oid::regclass::text;
1300 * No.10-1 dbms_stats.restore_database_stats
1303 SELECT dbms_stats.restore_database_stats('2012-02-29 23:59:57');
1305 SELECT dbms_stats.restore_database_stats('2012-02-29 23:59:57.000002');
1307 SELECT dbms_stats.restore_database_stats('2012-01-01 00:00:00');
1308 --#No.10-1-4 is skipped after lock tests
1309 --#No.10-1-5 is skipped after lock tests
1311 SELECT dbms_stats.restore_database_stats('2012-02-29 23:59:57');
1314 * No.10-2 dbms_stats.restore_schema_stats
1317 SELECT dbms_stats.restore_schema_stats('s0', '2012-02-29 23:59:57');
1319 SELECT dbms_stats.restore_schema_stats('s0', '2012-02-29 23:59:57.000002');
1321 SELECT dbms_stats.restore_schema_stats('s0', '2012-01-01 00:00:00');
1322 --#No.10-2-4 is skipped after lock tests
1323 --#No.10-2-5 is skipped after lock tests
1325 SELECT dbms_stats.restore_schema_stats('s0', '2012-02-29 23:59:57');
1327 SELECT dbms_stats.restore_schema_stats('s0', '2012-02-29 23:59:57');
1328 --#No.10-2-8 is skipped after lock tests
1330 SELECT dbms_stats.restore_schema_stats('s00', '2012-02-29 23:59:57');
1332 SELECT dbms_stats.restore_schema_stats('pg_catalog', '2012-02-29 23:59:57');
1335 * No.10-7 dbms_stats.restore_stats
1338 DELETE FROM dbms_stats.relation_stats_locked;
1339 SELECT dbms_stats.restore_stats(NULL);
1342 DELETE FROM dbms_stats.relation_stats_locked;
1343 SELECT dbms_stats.restore_stats(0);
1346 DELETE FROM dbms_stats.relation_stats_locked;
1347 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
1349 SELECT * FROM internal_locks;
1350 SELECT dbms_stats.restore_stats(2);
1351 SELECT * FROM internal_locks;
1353 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1356 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1359 DELETE FROM dbms_stats.relation_stats_locked;
1360 INSERT INTO dbms_stats.relation_stats_locked(relid, relname)
1361 SELECT relid::regclass, relname
1362 FROM dbms_stats.relation_stats_effective;
1363 INSERT INTO dbms_stats.column_stats_locked(starelid, staattnum, stainherit)
1364 SELECT starelid::regclass, staattnum, stainherit
1365 FROM dbms_stats.column_stats_effective;
1366 SELECT id, unit, comment FROM dbms_stats.backup_history
1368 SELECT * FROM columns_locked_v;
1369 SELECT * FROM relations_locked_v;
1370 SELECT dbms_stats.restore_stats(8);
1371 SELECT * FROM relations_locked_v;
1372 SELECT * FROM columns_locked_v;
1375 DELETE FROM dbms_stats.relation_stats_locked;
1376 SELECT id, unit, comment FROM dbms_stats.backup_history
1378 SELECT dbms_stats.restore_stats(8);
1379 SELECT * FROM relations_locked_v;
1380 SELECT * FROM columns_locked_v;
1383 * No.11-1 dbms_stats.lock(relid, attname)
1386 DELETE FROM dbms_stats.relation_stats_locked;
1387 SELECT dbms_stats.lock(NULL, NULL);
1389 ALTER FUNCTION dbms_stats.lock(relid regclass)
1390 RENAME TO truth_lock;
1391 CREATE FUNCTION dbms_stats.lock(relid regclass)
1395 RAISE NOTICE 'arguments are %', $1;
1400 DELETE FROM dbms_stats.relation_stats_locked;
1401 SELECT dbms_stats.lock('s0.st0', NULL);
1402 DROP FUNCTION dbms_stats.lock(relid regclass);
1403 ALTER FUNCTION dbms_stats.truth_lock(relid regclass)
1406 DELETE FROM dbms_stats.relation_stats_locked;
1407 SELECT dbms_stats.lock(NULL, 'id');
1409 DELETE FROM dbms_stats.relation_stats_locked;
1410 SELECT dbms_stats.lock('s0.st0', 'id');
1411 SELECT * FROM relations_locked_v;
1412 SELECT * FROM columns_locked_v c;
1414 DELETE FROM dbms_stats.relation_stats_locked;
1415 SELECT dbms_stats.lock(0, 'id');
1417 DELETE FROM dbms_stats.relation_stats_locked;
1418 SELECT dbms_stats.lock('s0.st0', 'id');
1419 SELECT * FROM relations_locked_v;
1420 SELECT * FROM columns_locked_v c;
1422 DELETE FROM dbms_stats.relation_stats_locked;
1423 SELECT dbms_stats.lock('pg_toast.pg_toast_2618', 'id');
1425 DELETE FROM dbms_stats.relation_stats_locked;
1426 SELECT dbms_stats.lock('s0.st0_idx', 'id');
1428 DELETE FROM dbms_stats.relation_stats_locked;
1429 SELECT dbms_stats.lock('st1_exp', 'lower');
1430 SELECT * FROM relations_locked_v;
1431 SELECT * FROM columns_locked_v c;
1432 DELETE FROM dbms_stats.relation_stats_locked;
1435 DELETE FROM dbms_stats.relation_stats_locked;
1436 SELECT dbms_stats.lock('s0.ss0', 'id');
1438 DELETE FROM dbms_stats.relation_stats_locked;
1439 SELECT dbms_stats.lock('s0.sc0', 'id');
1441 DELETE FROM dbms_stats.relation_stats_locked;
1442 SELECT dbms_stats.lock('s0.sft0', 'id');
1443 SELECT * FROM relations_locked_v;
1444 SELECT * FROM columns_locked_v c;
1446 -- #### 9.1 doesn't has materialized views
1447 -- DELETE FROM dbms_stats.relation_stats_locked;
1448 -- SELECT dbms_stats.lock('s0.smv0', 'id');
1449 -- SELECT * FROM relations_locked_v;
1450 -- SELECT * FROM columns_locked_v c;
1452 DELETE FROM dbms_stats.relation_stats_locked;
1453 SELECT dbms_stats.lock('pg_catalog.pg_class', 'id');
1455 DELETE FROM dbms_stats.relation_stats_locked;
1456 SELECT dbms_stats.lock('s0.st0', 'dummy');
1458 DELETE FROM dbms_stats.relation_stats_locked;
1459 DELETE FROM pg_statistic
1460 WHERE starelid = 's0.st0'::regclass;
1461 SELECT dbms_stats.lock('s0.st0', 'id');
1464 DELETE FROM dbms_stats.relation_stats_locked;
1465 INSERT INTO dbms_stats.relation_stats_locked(
1466 relid, relname, relpages, reltuples,
1468 VALUES('s0.st0'::regclass, 's0.st0', 1, 1640,
1470 SELECT dbms_stats.lock_column_stats('s0.st0','id');
1471 UPDATE dbms_stats.column_stats_locked
1472 SET (stanullfrac, stawidth, stadistinct,
1473 stakind1, stakind2, stakind3, stakind4,
1474 staop1, staop2, staop3, staop4,
1475 stanumbers1, stanumbers2, stanumbers3, stanumbers4,
1476 stavalues1, stavalues2, stavalues3, stavalues4
1479 NULL, NULL, NULL, NULL,
1480 NULL, NULL, NULL, NULL,
1481 NULL, NULL, NULL, NULL,
1482 NULL, NULL, NULL, NULL)
1483 WHERE starelid = 's0.st0'::regclass;
1484 SELECT dbms_stats.lock('s0.st0', 'id');
1485 SELECT * FROM relations_locked_v;
1486 SELECT * FROM columns_locked_v c;
1488 DELETE FROM dbms_stats.relation_stats_locked;
1489 SELECT dbms_stats.lock('s0.st0', 'id');
1490 SELECT * FROM relations_locked_v
1491 WHERE relid = 's0.st0'::regclass;
1492 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1495 * No.11-2 dbms_stats.lock(relid)
1498 DELETE FROM dbms_stats.relation_stats_locked;
1499 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
1501 SELECT * FROM internal_locks;
1502 SELECT dbms_stats.lock('s0.st0');
1503 SELECT * FROM relations_locked_v;
1504 SELECT * FROM columns_locked_v c;
1505 SELECT * FROM internal_locks;
1509 DELETE FROM dbms_stats.relation_stats_locked;
1510 SELECT dbms_stats.lock(NULL);
1512 DELETE FROM dbms_stats.relation_stats_locked;
1513 SELECT dbms_stats.lock('0');
1515 DELETE FROM dbms_stats.relation_stats_locked;
1516 SELECT dbms_stats.lock('s0.st0');
1517 SELECT * FROM relations_locked_v;
1518 SELECT * FROM columns_locked_v c;
1520 DELETE FROM dbms_stats.relation_stats_locked;
1521 SELECT dbms_stats.lock('pg_toast.pg_toast_2618');
1523 DELETE FROM dbms_stats.relation_stats_locked;
1524 SELECT dbms_stats.lock('s0.st0_idx');
1525 SELECT * FROM relations_locked_v;
1526 SELECT * FROM columns_locked_v c;
1528 DELETE FROM dbms_stats.relation_stats_locked;
1529 SELECT dbms_stats.lock('s0.ss0');
1531 DELETE FROM dbms_stats.relation_stats_locked;
1532 SELECT dbms_stats.lock('s0.sc0');
1534 DELETE FROM dbms_stats.relation_stats_locked;
1535 SELECT dbms_stats.lock('s0.sft0');
1536 SELECT * FROM relations_locked_v;
1537 SELECT * FROM columns_locked_v c;
1539 -- #### 9.1 doesn't has materialized views
1540 -- DELETE FROM dbms_stats.relation_stats_locked;
1541 -- SELECT dbms_stats.lock('s0.smv0');
1542 -- SELECT * FROM relations_locked_v;
1543 -- SELECT * FROM columns_locked_v c;
1545 DELETE FROM dbms_stats.relation_stats_locked;
1546 SELECT dbms_stats.lock('pg_catalog.pg_class');
1548 DELETE FROM dbms_stats.relation_stats_locked;
1549 SELECT dbms_stats.lock_table_stats('s0.st0');
1550 UPDATE dbms_stats.relation_stats_locked
1551 SET (relpages, reltuples,
1555 WHERE relid = 's0.st0'::regclass;
1556 SELECT dbms_stats.lock('s0.st0');
1557 SELECT * FROM relations_locked_v;
1558 SELECT * FROM columns_locked_v c;
1560 DELETE FROM dbms_stats.relation_stats_locked;
1561 SELECT dbms_stats.lock('s0.st0');
1562 SELECT * FROM relations_locked_v;
1563 SELECT * FROM columns_locked_v c;
1566 * Stab function dbms_stats.lock
1568 ALTER FUNCTION dbms_stats.lock(relid regclass)
1569 RENAME TO truth_lock;
1570 CREATE FUNCTION dbms_stats.lock(relid regclass)
1574 RAISE NOTICE 'arguments are %', $1;
1580 ALTER FUNCTION dbms_stats.lock(relid regclass, attname text)
1581 RENAME TO truth_lock;
1582 CREATE FUNCTION dbms_stats.lock(
1588 RAISE NOTICE 'arguments are %, %', $1, $2;
1595 * No.12-1 dbms_stats.lock_database_stats
1598 SELECT dbms_stats.lock_database_stats();
1601 * No.12-2 dbms_stats.lock_schema_stats
1604 SELECT dbms_stats.lock_schema_stats('s0');
1606 SELECT dbms_stats.lock_schema_stats('s00');
1608 SELECT dbms_stats.lock_schema_stats('pg_catalog');
1611 * No.12-3 dbms_stats.lock_table_stats(regclass)
1614 SELECT dbms_stats.lock_table_stats('s0.st0');
1616 SELECT dbms_stats.lock_table_stats('st0');
1618 SELECT dbms_stats.lock_table_stats('s00.s0');
1621 * No.12-4 dbms_stats.lock_table_stats(schemaname, tablename)
1624 SELECT dbms_stats.lock_table_stats('s0', 'st0');
1627 * No.12-5 dbms_stats.lock_column_stats(regclass, attname)
1630 SELECT dbms_stats.lock_column_stats('s0.st0', 'id');
1632 SELECT dbms_stats.lock_column_stats('st0', 'id');
1634 SELECT dbms_stats.lock_column_stats('s00.s0', 'id');
1637 * No.12-6 dbms_stats.lock_column_stats(schemaname, tablename, int2)
1640 SELECT dbms_stats.lock_column_stats('s0', 'st0', 'id');
1643 * Delete Stab function lock
1645 DROP FUNCTION dbms_stats.lock(relid regclass);
1646 ALTER FUNCTION dbms_stats.truth_lock(relid regclass)
1648 DROP FUNCTION dbms_stats.lock(relid regclass, attname text);
1649 ALTER FUNCTION dbms_stats.truth_lock(relid regclass, attname text)
1653 * No.13-1 dbms_stats.unlock
1656 DELETE FROM dbms_stats.relation_stats_locked;
1657 SELECT dbms_stats.lock_database_stats();
1658 SELECT * FROM dbms_stats.backup_history
1660 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1661 SELECT count(*) FROM dbms_stats.column_stats_backup;
1662 SELECT dbms_stats.unlock();
1663 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1664 SELECT count(*) FROM dbms_stats.column_stats_locked;
1665 SELECT * FROM dbms_stats.backup_history
1667 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1668 SELECT count(*) FROM dbms_stats.column_stats_backup;
1671 DELETE FROM dbms_stats.relation_stats_locked;
1672 SELECT dbms_stats.lock_database_stats();
1673 SELECT dbms_stats.unlock();
1674 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1675 SELECT count(*) FROM dbms_stats.column_stats_locked;
1678 DELETE FROM dbms_stats.relation_stats_locked;
1679 SELECT dbms_stats.lock_database_stats();
1680 DELETE FROM dbms_stats.column_stats_locked;
1681 SELECT dbms_stats.unlock();
1682 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1683 SELECT count(*) FROM dbms_stats.column_stats_locked;
1686 DELETE FROM dbms_stats.relation_stats_locked;
1687 SELECT dbms_stats.unlock();
1688 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1689 SELECT count(*) FROM dbms_stats.column_stats_locked;
1692 DELETE FROM dbms_stats.relation_stats_locked;
1693 SELECT dbms_stats.lock_database_stats();
1694 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1697 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1700 SELECT dbms_stats.unlock('s0.st0');
1701 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1704 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1709 DELETE FROM dbms_stats.relation_stats_locked;
1710 SELECT dbms_stats.lock_database_stats();
1711 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1714 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1717 SELECT dbms_stats.unlock('st0');
1718 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1721 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1726 DELETE FROM dbms_stats.relation_stats_locked;
1727 SELECT dbms_stats.lock_database_stats();
1728 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1729 SELECT count(*) FROM dbms_stats.column_stats_locked;
1730 SELECT dbms_stats.unlock('s00.s0');
1731 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1732 SELECT count(*) FROM dbms_stats.column_stats_locked;
1735 DELETE FROM dbms_stats.relation_stats_locked;
1736 SELECT dbms_stats.lock_database_stats();
1737 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1740 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1741 SELECT dbms_stats.unlock('s0.st0', 'id');
1742 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1745 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1748 DELETE FROM dbms_stats.relation_stats_locked;
1749 SELECT dbms_stats.lock_database_stats();
1750 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1753 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1754 SELECT dbms_stats.unlock('s0.st0', 'dummy');
1755 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1758 SELECT starelid, attname, stainherit FROM columns_locked_v c;
1761 DELETE FROM dbms_stats.relation_stats_locked;
1762 SELECT dbms_stats.lock_database_stats();
1763 DELETE FROM dbms_stats.column_stats_locked;
1764 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1767 SELECT dbms_stats.unlock('s0.st0', 'id');
1768 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1771 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_locked
1772 GROUP BY starelid, staattnum
1773 ORDER BY starelid, staattnum;
1776 DELETE FROM dbms_stats.relation_stats_locked;
1777 SELECT dbms_stats.lock_database_stats();
1778 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1781 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_locked
1782 GROUP BY starelid, staattnum
1783 ORDER BY starelid, staattnum;
1784 SELECT dbms_stats.unlock(NULL, 'id');
1785 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1788 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_locked
1789 GROUP BY starelid, staattnum
1790 ORDER BY starelid, staattnum;
1793 DELETE FROM dbms_stats.relation_stats_locked;
1794 SELECT dbms_stats.lock_database_stats();
1795 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1798 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_locked
1799 GROUP BY starelid, staattnum
1800 ORDER BY starelid, staattnum;
1801 SELECT dbms_stats.unlock('s0.st0', NULL);
1802 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1805 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_locked
1806 GROUP BY starelid, staattnum
1807 ORDER BY starelid, staattnum;
1810 DELETE FROM dbms_stats.relation_stats_locked;
1811 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
1812 SELECT dbms_stats.lock_database_stats();
1813 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1814 SELECT count(*) FROM dbms_stats.column_stats_locked;
1816 SELECT * FROM internal_locks;
1817 SELECT dbms_stats.unlock();
1818 SELECT * FROM internal_locks;
1820 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1821 SELECT count(*) FROM dbms_stats.column_stats_locked;
1824 * No.14-1 dbms_stats.unlock_database_stats
1827 DELETE FROM dbms_stats.relation_stats_locked;
1828 SELECT dbms_stats.lock_database_stats();
1829 SELECT * FROM dbms_stats.backup_history
1831 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1832 SELECT count(*) FROM dbms_stats.column_stats_backup;
1833 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1834 SELECT count(*) FROM dbms_stats.column_stats_locked;
1835 SELECT dbms_stats.unlock_database_stats();
1836 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1837 SELECT count(*) FROM dbms_stats.column_stats_locked;
1838 SELECT * FROM dbms_stats.backup_history
1840 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1841 SELECT count(*) FROM dbms_stats.column_stats_backup;
1844 DELETE FROM dbms_stats.relation_stats_locked;
1845 SELECT dbms_stats.lock_database_stats();
1846 DELETE FROM dbms_stats.column_stats_locked;
1847 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1848 SELECT dbms_stats.unlock_database_stats();
1849 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1850 SELECT count(*) FROM dbms_stats.column_stats_locked;
1853 DELETE FROM dbms_stats.relation_stats_locked;
1854 SELECT dbms_stats.unlock_database_stats();
1855 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1856 SELECT count(*) FROM dbms_stats.column_stats_locked;
1859 DELETE FROM dbms_stats.relation_stats_locked;
1860 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
1861 SELECT dbms_stats.lock_database_stats();
1862 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1863 SELECT count(*) FROM dbms_stats.column_stats_locked;
1865 SELECT * FROM internal_locks;
1866 SELECT dbms_stats.unlock_database_stats();
1867 SELECT * FROM internal_locks;
1869 SELECT count(*) FROM dbms_stats.relation_stats_locked;
1870 SELECT count(*) FROM dbms_stats.column_stats_locked;
1873 * No.14-2 dbms_stats.unlock_schema_stats
1876 DELETE FROM dbms_stats.relation_stats_locked;
1877 SELECT dbms_stats.lock_database_stats();
1878 SELECT * FROM dbms_stats.backup_history
1880 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1881 SELECT count(*) FROM dbms_stats.column_stats_backup;
1882 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1885 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1888 SELECT dbms_stats.unlock_schema_stats('s0');
1889 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1892 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1895 SELECT * FROM dbms_stats.backup_history
1897 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1898 SELECT count(*) FROM dbms_stats.column_stats_backup;
1901 DELETE FROM dbms_stats.relation_stats_locked;
1902 SELECT dbms_stats.lock_database_stats();
1903 DELETE FROM dbms_stats.column_stats_locked;
1904 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1907 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1910 SELECT dbms_stats.unlock_schema_stats('s0');
1911 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1914 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1919 DELETE FROM dbms_stats.relation_stats_locked;
1920 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1923 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1926 SELECT dbms_stats.unlock_schema_stats('s0');
1927 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1930 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1935 DELETE FROM dbms_stats.relation_stats_locked;
1936 SELECT dbms_stats.lock_database_stats();
1937 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1940 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1943 SELECT dbms_stats.unlock_schema_stats('s0');
1944 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1947 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1952 DELETE FROM dbms_stats.relation_stats_locked;
1953 SELECT dbms_stats.lock_database_stats();
1954 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1957 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1960 SELECT dbms_stats.unlock_schema_stats('s00');
1961 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1964 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1969 DELETE FROM dbms_stats.relation_stats_locked;
1970 SELECT dbms_stats.lock_database_stats();
1971 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1974 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1977 SELECT dbms_stats.unlock_schema_stats('pg_catalog');
1978 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1981 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1986 DELETE FROM dbms_stats.relation_stats_locked;
1987 SELECT dbms_stats.lock_database_stats();
1988 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1991 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
1994 SELECT dbms_stats.unlock_schema_stats(NULL);
1995 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
1998 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2003 DELETE FROM dbms_stats.relation_stats_locked;
2004 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
2005 SELECT dbms_stats.lock_database_stats();
2006 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2009 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2013 SELECT * FROM internal_locks;
2014 SELECT dbms_stats.unlock_schema_stats('s0');
2015 SELECT * FROM internal_locks;
2017 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2020 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2025 * No.14-3 dbms_stats.unlock_table_stats(regclass)
2028 DELETE FROM dbms_stats.relation_stats_locked;
2029 SELECT dbms_stats.lock_database_stats();
2030 SELECT * FROM dbms_stats.backup_history
2032 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2033 SELECT count(*) FROM dbms_stats.column_stats_backup;
2034 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2037 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2040 SELECT dbms_stats.unlock_table_stats('s0.st0');
2041 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2044 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2047 SELECT * FROM dbms_stats.backup_history
2049 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2050 SELECT count(*) FROM dbms_stats.column_stats_backup;
2053 DELETE FROM dbms_stats.relation_stats_locked;
2054 SELECT dbms_stats.lock_database_stats();
2055 DELETE FROM dbms_stats.column_stats_locked;
2056 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2059 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2062 SELECT dbms_stats.unlock_table_stats('s0.st0');
2063 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2066 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2071 DELETE FROM dbms_stats.relation_stats_locked;
2072 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2075 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2078 SELECT dbms_stats.unlock_table_stats('s0.st0');
2079 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2082 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2087 DELETE FROM dbms_stats.relation_stats_locked;
2088 SELECT dbms_stats.lock_database_stats();
2089 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2092 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2095 SELECT dbms_stats.unlock_table_stats('s0.st0');
2096 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2099 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2104 DELETE FROM dbms_stats.relation_stats_locked;
2105 SELECT dbms_stats.lock_database_stats();
2106 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2109 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2112 SELECT dbms_stats.unlock_table_stats('st0');
2113 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2116 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2121 DELETE FROM dbms_stats.relation_stats_locked;
2122 SELECT dbms_stats.lock_database_stats();
2123 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2126 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2129 SELECT dbms_stats.unlock_table_stats('s00.s0');
2130 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2133 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2138 DELETE FROM dbms_stats.relation_stats_locked;
2139 SELECT dbms_stats.lock_database_stats();
2140 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2143 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2146 SELECT dbms_stats.unlock_table_stats(NULL);
2147 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2150 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2155 DELETE FROM dbms_stats.relation_stats_locked;
2156 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
2157 SELECT dbms_stats.lock_database_stats();
2158 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2161 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2165 SELECT * FROM internal_locks;
2166 SELECT dbms_stats.unlock_table_stats('s0.st0');
2167 SELECT * FROM internal_locks;
2169 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2172 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2177 * No.14-4 dbms_stats.unlock_table_stats(schemaname, tablename)
2180 DELETE FROM dbms_stats.relation_stats_locked;
2181 SELECT dbms_stats.lock_database_stats();
2182 SELECT * FROM dbms_stats.backup_history
2184 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2185 SELECT count(*) FROM dbms_stats.column_stats_backup;
2186 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2189 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2192 SELECT dbms_stats.unlock_table_stats('s0','st0');
2193 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2196 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2199 SELECT * FROM dbms_stats.backup_history
2201 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2202 SELECT count(*) FROM dbms_stats.column_stats_backup;
2205 DELETE FROM dbms_stats.relation_stats_locked;
2206 SELECT dbms_stats.lock_database_stats();
2207 DELETE FROM dbms_stats.column_stats_locked;
2208 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2211 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2214 SELECT dbms_stats.unlock_table_stats('s0', 'st0');
2215 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2218 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2223 DELETE FROM dbms_stats.relation_stats_locked;
2224 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2227 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2230 SELECT dbms_stats.unlock_table_stats('s0', 'st0');
2231 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2234 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2239 DELETE FROM dbms_stats.relation_stats_locked;
2240 SELECT dbms_stats.lock_database_stats();
2241 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2244 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2247 SELECT dbms_stats.unlock_table_stats('s0', 'st0');
2248 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2251 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2256 DELETE FROM dbms_stats.relation_stats_locked;
2257 SELECT dbms_stats.lock_database_stats();
2258 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2261 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2264 SELECT dbms_stats.unlock_table_stats('s00', 's0');
2265 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2268 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2273 DELETE FROM dbms_stats.relation_stats_locked;
2274 SELECT dbms_stats.lock_database_stats();
2275 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2278 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2281 SELECT dbms_stats.unlock_table_stats(NULL, 'st0');
2282 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2285 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2290 DELETE FROM dbms_stats.relation_stats_locked;
2291 SELECT dbms_stats.lock_database_stats();
2292 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2295 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2298 SELECT dbms_stats.unlock_table_stats('s0', NULL);
2299 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2302 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2307 DELETE FROM dbms_stats.relation_stats_locked;
2308 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
2309 SELECT dbms_stats.lock_database_stats();
2310 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2313 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2317 SELECT * FROM internal_locks;
2318 SELECT dbms_stats.unlock_table_stats('s0', 'st0');
2319 SELECT * FROM internal_locks;
2321 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2324 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
2329 * No.14-5 dbms_stats.unlock_column_stats(regclass, attname)
2332 DELETE FROM dbms_stats.relation_stats_locked;
2333 SELECT dbms_stats.lock_database_stats();
2334 SELECT * FROM dbms_stats.backup_history
2336 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2337 SELECT count(*) FROM dbms_stats.column_stats_backup;
2338 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2341 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2342 SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
2343 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2344 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2347 SELECT * FROM dbms_stats.backup_history
2349 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2350 SELECT count(*) FROM dbms_stats.column_stats_backup;
2353 DELETE FROM dbms_stats.relation_stats_locked;
2354 SELECT dbms_stats.lock_database_stats();
2355 DELETE FROM dbms_stats.column_stats_locked;
2356 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2359 SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
2360 SELECT count(*) FROM dbms_stats.column_stats_locked;
2361 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2366 DELETE FROM dbms_stats.relation_stats_locked;
2367 SELECT dbms_stats.lock_database_stats();
2368 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2371 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2372 SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
2373 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2374 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2379 DELETE FROM dbms_stats.relation_stats_locked;
2380 SELECT dbms_stats.lock_database_stats();
2381 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2384 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2385 SELECT dbms_stats.unlock_column_stats('st0', 'id');
2386 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2387 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2392 DELETE FROM dbms_stats.relation_stats_locked;
2393 SELECT dbms_stats.lock_database_stats();
2394 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2397 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2398 SELECT dbms_stats.unlock_column_stats('s0.st0', 'dummy');
2399 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2400 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2405 DELETE FROM dbms_stats.relation_stats_locked;
2406 SELECT dbms_stats.lock_database_stats();
2407 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2410 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2411 SELECT dbms_stats.unlock_column_stats('s00.s0', 'id');
2412 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2413 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2418 DELETE FROM dbms_stats.relation_stats_locked;
2419 SELECT dbms_stats.lock_database_stats();
2420 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2423 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2424 SELECT dbms_stats.unlock_column_stats(NULL, 'id');
2425 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2426 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2431 DELETE FROM dbms_stats.relation_stats_locked;
2432 SELECT dbms_stats.lock_database_stats();
2433 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2436 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2437 SELECT dbms_stats.unlock_column_stats('s0.st0', NULL);
2438 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2439 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2444 DELETE FROM dbms_stats.relation_stats_locked;
2445 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
2446 SELECT dbms_stats.lock_database_stats();
2447 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2450 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2452 SELECT * FROM internal_locks;
2453 SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
2454 SELECT * FROM internal_locks;
2456 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2457 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2462 * No.14-6 dbms_stats.unlock_column_stats(schemaname, tablename, attname)
2465 DELETE FROM dbms_stats.relation_stats_locked;
2466 SELECT dbms_stats.lock_database_stats();
2467 SELECT * FROM dbms_stats.backup_history
2469 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2472 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2473 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
2474 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2475 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2478 SELECT * FROM dbms_stats.backup_history
2480 SELECT count(*) FROM dbms_stats.relation_stats_backup;
2481 SELECT count(*) FROM dbms_stats.column_stats_backup;
2484 DELETE FROM dbms_stats.relation_stats_locked;
2485 SELECT dbms_stats.lock_database_stats();
2486 DELETE FROM dbms_stats.column_stats_locked;
2487 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2490 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
2491 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2492 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2497 DELETE FROM dbms_stats.relation_stats_locked;
2498 SELECT dbms_stats.lock_database_stats();
2499 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2502 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2503 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
2504 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2505 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2510 DELETE FROM dbms_stats.relation_stats_locked;
2511 SELECT dbms_stats.lock_database_stats();
2512 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2515 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2516 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'dummy');
2517 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2518 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2523 DELETE FROM dbms_stats.relation_stats_locked;
2524 SELECT dbms_stats.lock_database_stats();
2525 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2528 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2529 SELECT dbms_stats.unlock_column_stats(NULL, 'st0', 'id');
2530 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2531 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2536 DELETE FROM dbms_stats.relation_stats_locked;
2537 SELECT dbms_stats.lock_database_stats();
2538 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2541 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2542 SELECT dbms_stats.unlock_column_stats('s0', NULL, 'id');
2543 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2544 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2549 DELETE FROM dbms_stats.relation_stats_locked;
2550 SELECT dbms_stats.lock_database_stats();
2551 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2554 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2555 SELECT dbms_stats.unlock_column_stats('s0', 'st0', NULL);
2556 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2557 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2562 DELETE FROM dbms_stats.relation_stats_locked;
2563 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
2564 SELECT dbms_stats.lock_database_stats();
2565 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2568 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2570 SELECT * FROM internal_locks;
2571 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
2572 SELECT * FROM internal_locks;
2574 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2575 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2579 -- No.15 error description. -- abnormal case.
2580 RESET SESSION AUTHORIZATION;
2581 CREATE TABLE s0.st4 (a int, b text);
2582 CREATE VIEW s0.vst4 AS select * FROM s0.st4;
2583 GRANT SELECT ON s0.vst4 TO regular_user;
2585 ALTER TABLE dbms_stats.relation_stats_locked OWNER TO regular_user;
2586 /* reconnection needed to flush cache */
2589 EXPLAIN (COSTS OFF) SELECT * FROM s0.vst4 WHERE a = 1;
2592 ALTER TABLE dbms_stats.relation_stats_locked OWNER TO super_user;
2593 DROP TABLE s0.st4 CASCADE;