3 * No.2-1 table definitions.
6 \d dbms_stats.backup_history
7 Table "dbms_stats.backup_history"
8 Column | Type | Collation | Nullable | Default
9 ---------+--------------------------+-----------+----------+-------------------------------------------------------
10 id | bigint | | not null | nextval('dbms_stats.backup_history_id_seq'::regclass)
11 time | timestamp with time zone | | not null |
12 unit | character(1) | | not null |
15 "backup_history_pkey" PRIMARY KEY, btree (id)
17 TABLE "dbms_stats.column_stats_backup" CONSTRAINT "column_stats_backup_id_fkey" FOREIGN KEY (id) REFERENCES dbms_stats.backup_history(id) ON DELETE CASCADE
18 TABLE "dbms_stats.relation_stats_backup" CONSTRAINT "relation_stats_backup_id_fkey" FOREIGN KEY (id) REFERENCES dbms_stats.backup_history(id) ON DELETE CASCADE
21 \d dbms_stats.column_stats_backup
22 Table "dbms_stats.column_stats_backup"
23 Column | Type | Collation | Nullable | Default
24 -------------+---------------------+-----------+----------+---------
25 id | bigint | | not null |
26 statypid | oid | | not null |
27 starelid | oid | | not null |
28 staattnum | smallint | | not null |
29 stainherit | boolean | | not null |
30 stanullfrac | real | | not null |
31 stawidth | integer | | not null |
32 stadistinct | real | | not null |
33 stakind1 | smallint | | not null |
34 stakind2 | smallint | | not null |
35 stakind3 | smallint | | not null |
36 stakind4 | smallint | | not null |
37 stakind5 | smallint | | not null |
38 staop1 | oid | | not null |
39 staop2 | oid | | not null |
40 staop3 | oid | | not null |
41 staop4 | oid | | not null |
42 staop5 | oid | | not null |
43 stanumbers1 | real[] | | |
44 stanumbers2 | real[] | | |
45 stanumbers3 | real[] | | |
46 stanumbers4 | real[] | | |
47 stanumbers5 | real[] | | |
48 stavalues1 | dbms_stats.anyarray | | |
49 stavalues2 | dbms_stats.anyarray | | |
50 stavalues3 | dbms_stats.anyarray | | |
51 stavalues4 | dbms_stats.anyarray | | |
52 stavalues5 | dbms_stats.anyarray | | |
54 "column_stats_backup_pkey" PRIMARY KEY, btree (id, starelid, staattnum, stainherit)
55 Foreign-key constraints:
56 "column_stats_backup_id_fkey" FOREIGN KEY (id) REFERENCES dbms_stats.backup_history(id) ON DELETE CASCADE
57 "column_stats_backup_id_fkey1" FOREIGN KEY (id, starelid) REFERENCES dbms_stats.relation_stats_backup(id, relid) ON DELETE CASCADE
60 \d dbms_stats.column_stats_locked
61 Table "dbms_stats.column_stats_locked"
62 Column | Type | Collation | Nullable | Default
63 -------------+---------------------+-----------+----------+---------
64 starelid | oid | | not null |
65 staattnum | smallint | | not null |
66 stainherit | boolean | | not null |
67 stanullfrac | real | | |
68 stawidth | integer | | |
69 stadistinct | real | | |
70 stakind1 | smallint | | |
71 stakind2 | smallint | | |
72 stakind3 | smallint | | |
73 stakind4 | smallint | | |
74 stakind5 | smallint | | |
80 stanumbers1 | real[] | | |
81 stanumbers2 | real[] | | |
82 stanumbers3 | real[] | | |
83 stanumbers4 | real[] | | |
84 stanumbers5 | real[] | | |
85 stavalues1 | dbms_stats.anyarray | | |
86 stavalues2 | dbms_stats.anyarray | | |
87 stavalues3 | dbms_stats.anyarray | | |
88 stavalues4 | dbms_stats.anyarray | | |
89 stavalues5 | dbms_stats.anyarray | | |
91 "column_stats_locked_pkey" PRIMARY KEY, btree (starelid, staattnum, stainherit)
92 Foreign-key constraints:
93 "column_stats_locked_starelid_fkey" FOREIGN KEY (starelid) REFERENCES dbms_stats.relation_stats_locked(relid) ON DELETE CASCADE
95 invalidate_column_cache BEFORE INSERT OR DELETE OR UPDATE ON dbms_stats.column_stats_locked FOR EACH ROW EXECUTE PROCEDURE dbms_stats.invalidate_column_cache()
98 \d dbms_stats.relation_stats_backup
99 Table "dbms_stats.relation_stats_backup"
100 Column | Type | Collation | Nullable | Default
101 ------------------+--------------------------+-----------+----------+---------
102 id | bigint | | not null |
103 relid | oid | | not null |
104 relname | text | | not null |
105 relpages | integer | | not null |
106 reltuples | real | | not null |
107 relallvisible | integer | | not null |
108 curpages | integer | | not null |
109 last_analyze | timestamp with time zone | | |
110 last_autoanalyze | timestamp with time zone | | |
112 "relation_stats_backup_pkey" PRIMARY KEY, btree (id, relid)
113 Foreign-key constraints:
114 "relation_stats_backup_id_fkey" FOREIGN KEY (id) REFERENCES dbms_stats.backup_history(id) ON DELETE CASCADE
116 TABLE "dbms_stats.column_stats_backup" CONSTRAINT "column_stats_backup_id_fkey1" FOREIGN KEY (id, starelid) REFERENCES dbms_stats.relation_stats_backup(id, relid) ON DELETE CASCADE
119 \d dbms_stats.relation_stats_locked
120 Table "dbms_stats.relation_stats_locked"
121 Column | Type | Collation | Nullable | Default
122 ------------------+--------------------------+-----------+----------+---------
123 relid | oid | | not null |
124 relname | text | | not null |
125 relpages | integer | | |
126 reltuples | real | | |
127 relallvisible | integer | | |
128 curpages | integer | | |
129 last_analyze | timestamp with time zone | | |
130 last_autoanalyze | timestamp with time zone | | |
132 "relation_stats_locked_pkey" PRIMARY KEY, btree (relid)
134 TABLE "dbms_stats.column_stats_locked" CONSTRAINT "column_stats_locked_starelid_fkey" FOREIGN KEY (starelid) REFERENCES dbms_stats.relation_stats_locked(relid) ON DELETE CASCADE
136 invalidate_relation_cache BEFORE INSERT OR DELETE OR UPDATE ON dbms_stats.relation_stats_locked FOR EACH ROW EXECUTE PROCEDURE dbms_stats.invalidate_relation_cache()
139 * No.2-2 view definitions.
142 \dS+ dbms_stats.column_stats_effective
143 View "dbms_stats.column_stats_effective"
144 Column | Type | Collation | Nullable | Default | Storage | Description
145 -------------+---------------------+-----------+----------+---------+----------+-------------
146 starelid | oid | | | | plain |
147 staattnum | smallint | | | | plain |
148 stainherit | boolean | | | | plain |
149 stanullfrac | real | | | | plain |
150 stawidth | integer | | | | plain |
151 stadistinct | real | | | | plain |
152 stakind1 | smallint | | | | plain |
153 stakind2 | smallint | | | | plain |
154 stakind3 | smallint | | | | plain |
155 stakind4 | smallint | | | | plain |
156 stakind5 | smallint | | | | plain |
157 staop1 | oid | | | | plain |
158 staop2 | oid | | | | plain |
159 staop3 | oid | | | | plain |
160 staop4 | oid | | | | plain |
161 staop5 | oid | | | | plain |
162 stanumbers1 | real[] | | | | extended |
163 stanumbers2 | real[] | | | | extended |
164 stanumbers3 | real[] | | | | extended |
165 stanumbers4 | real[] | | | | extended |
166 stanumbers5 | real[] | | | | extended |
167 stavalues1 | dbms_stats.anyarray | | | | extended |
168 stavalues2 | dbms_stats.anyarray | | | | extended |
169 stavalues3 | dbms_stats.anyarray | | | | extended |
170 stavalues4 | dbms_stats.anyarray | | | | extended |
171 stavalues5 | dbms_stats.anyarray | | | | extended |
199 FROM ( SELECT (dbms_stats.merge(v.*, s.*)).starelid AS starelid,
200 (dbms_stats.merge(v.*, s.*)).staattnum AS staattnum,
201 (dbms_stats.merge(v.*, s.*)).stainherit AS stainherit,
202 (dbms_stats.merge(v.*, s.*)).stanullfrac AS stanullfrac,
203 (dbms_stats.merge(v.*, s.*)).stawidth AS stawidth,
204 (dbms_stats.merge(v.*, s.*)).stadistinct AS stadistinct,
205 (dbms_stats.merge(v.*, s.*)).stakind1 AS stakind1,
206 (dbms_stats.merge(v.*, s.*)).stakind2 AS stakind2,
207 (dbms_stats.merge(v.*, s.*)).stakind3 AS stakind3,
208 (dbms_stats.merge(v.*, s.*)).stakind4 AS stakind4,
209 (dbms_stats.merge(v.*, s.*)).stakind5 AS stakind5,
210 (dbms_stats.merge(v.*, s.*)).staop1 AS staop1,
211 (dbms_stats.merge(v.*, s.*)).staop2 AS staop2,
212 (dbms_stats.merge(v.*, s.*)).staop3 AS staop3,
213 (dbms_stats.merge(v.*, s.*)).staop4 AS staop4,
214 (dbms_stats.merge(v.*, s.*)).staop5 AS staop5,
215 (dbms_stats.merge(v.*, s.*)).stanumbers1 AS stanumbers1,
216 (dbms_stats.merge(v.*, s.*)).stanumbers2 AS stanumbers2,
217 (dbms_stats.merge(v.*, s.*)).stanumbers3 AS stanumbers3,
218 (dbms_stats.merge(v.*, s.*)).stanumbers4 AS stanumbers4,
219 (dbms_stats.merge(v.*, s.*)).stanumbers5 AS stanumbers5,
220 (dbms_stats.merge(v.*, s.*)).stavalues1 AS stavalues1,
221 (dbms_stats.merge(v.*, s.*)).stavalues2 AS stavalues2,
222 (dbms_stats.merge(v.*, s.*)).stavalues3 AS stavalues3,
223 (dbms_stats.merge(v.*, s.*)).stavalues4 AS stavalues4,
224 (dbms_stats.merge(v.*, s.*)).stavalues5 AS stavalues5
226 FULL JOIN dbms_stats.column_stats_locked v USING (starelid, staattnum, stainherit)
227 WHERE NOT dbms_stats.is_system_catalog(starelid::regclass) AND (EXISTS ( SELECT NULL::text
229 WHERE a.attrelid = starelid AND a.attnum = staattnum AND a.attisdropped = false))) m
230 WHERE m.starelid IS NOT NULL;
233 \dS+ dbms_stats.relation_stats_effective
234 View "dbms_stats.relation_stats_effective"
235 Column | Type | Collation | Nullable | Default | Storage | Description
236 ------------------+--------------------------+-----------+----------+---------+----------+-------------
237 relid | oid | | | | plain |
238 relname | text | | | | extended |
239 relpages | integer | | | | plain |
240 reltuples | real | | | | plain |
241 relallvisible | integer | | | | plain |
242 curpages | integer | | | | plain |
243 last_analyze | timestamp with time zone | | | | plain |
244 last_autoanalyze | timestamp with time zone | | | | plain |
246 SELECT c.oid AS relid,
247 dbms_stats.relname(n.nspname::text, c.relname::text) AS relname,
248 COALESCE(v.relpages, c.relpages) AS relpages,
249 COALESCE(v.reltuples, c.reltuples) AS reltuples,
250 COALESCE(v.relallvisible, c.relallvisible) AS relallvisible,
251 COALESCE(v.curpages, (pg_relation_size(c.oid::regclass) / current_setting('block_size'::text)::integer)::integer) AS curpages,
252 COALESCE(v.last_analyze, pg_stat_get_last_analyze_time(c.oid)) AS last_analyze,
253 COALESCE(v.last_autoanalyze, pg_stat_get_last_autoanalyze_time(c.oid)) AS last_autoanalyze
255 JOIN pg_namespace n ON c.relnamespace = n.oid
256 LEFT JOIN dbms_stats.relation_stats_locked v ON v.relid = c.oid
257 WHERE dbms_stats.is_target_relkind(c.relkind) AND NOT dbms_stats.is_system_schema(n.nspname::text);
260 \dS+ dbms_stats.stats
261 View "dbms_stats.stats"
262 Column | Type | Collation | Nullable | Default | Storage | Description
263 ------------------------+---------------------+-----------+----------+---------+----------+-------------
264 schemaname | name | | | | plain |
265 tablename | name | | | | plain |
266 attname | name | | | | plain |
267 inherited | boolean | | | | plain |
268 null_frac | real | | | | plain |
269 avg_width | integer | | | | plain |
270 n_distinct | real | | | | plain |
271 most_common_vals | dbms_stats.anyarray | | | | extended |
272 most_common_freqs | real[] | | | | extended |
273 histogram_bounds | dbms_stats.anyarray | | | | extended |
274 correlation | real | | | | plain |
275 most_common_elems | dbms_stats.anyarray | | | | extended |
276 most_common_elem_freqs | real[] | | | | extended |
277 elem_count_histogram | real[] | | | | extended |
279 SELECT n.nspname AS schemaname,
280 c.relname AS tablename,
282 s.stainherit AS inherited,
283 s.stanullfrac AS null_frac,
284 s.stawidth AS avg_width,
285 s.stadistinct AS n_distinct,
287 WHEN s.stakind1 = 1 THEN s.stavalues1
288 WHEN s.stakind2 = 1 THEN s.stavalues2
289 WHEN s.stakind3 = 1 THEN s.stavalues3
290 WHEN s.stakind4 = 1 THEN s.stavalues4
291 WHEN s.stakind5 = 1 THEN s.stavalues5
292 ELSE NULL::dbms_stats.anyarray
293 END AS most_common_vals,
295 WHEN s.stakind1 = 1 THEN s.stanumbers1
296 WHEN s.stakind2 = 1 THEN s.stanumbers2
297 WHEN s.stakind3 = 1 THEN s.stanumbers3
298 WHEN s.stakind4 = 1 THEN s.stanumbers4
299 WHEN s.stakind5 = 1 THEN s.stanumbers5
301 END AS most_common_freqs,
303 WHEN s.stakind1 = 2 THEN s.stavalues1
304 WHEN s.stakind2 = 2 THEN s.stavalues2
305 WHEN s.stakind3 = 2 THEN s.stavalues3
306 WHEN s.stakind4 = 2 THEN s.stavalues4
307 WHEN s.stakind5 = 2 THEN s.stavalues5
308 ELSE NULL::dbms_stats.anyarray
309 END AS histogram_bounds,
311 WHEN s.stakind1 = 3 THEN s.stanumbers1[1]
312 WHEN s.stakind2 = 3 THEN s.stanumbers2[1]
313 WHEN s.stakind3 = 3 THEN s.stanumbers3[1]
314 WHEN s.stakind4 = 3 THEN s.stanumbers4[1]
315 WHEN s.stakind5 = 3 THEN s.stanumbers5[1]
319 WHEN s.stakind1 = 4 THEN s.stavalues1
320 WHEN s.stakind2 = 4 THEN s.stavalues2
321 WHEN s.stakind3 = 4 THEN s.stavalues3
322 WHEN s.stakind4 = 4 THEN s.stavalues4
323 WHEN s.stakind5 = 4 THEN s.stavalues5
324 ELSE NULL::dbms_stats.anyarray
325 END AS most_common_elems,
327 WHEN s.stakind1 = 4 THEN s.stanumbers1
328 WHEN s.stakind2 = 4 THEN s.stanumbers2
329 WHEN s.stakind3 = 4 THEN s.stanumbers3
330 WHEN s.stakind4 = 4 THEN s.stanumbers4
331 WHEN s.stakind5 = 4 THEN s.stanumbers5
333 END AS most_common_elem_freqs,
335 WHEN s.stakind1 = 5 THEN s.stanumbers1
336 WHEN s.stakind2 = 5 THEN s.stanumbers2
337 WHEN s.stakind3 = 5 THEN s.stanumbers3
338 WHEN s.stakind4 = 5 THEN s.stanumbers4
339 WHEN s.stakind5 = 5 THEN s.stanumbers5
341 END AS elem_count_histogram
342 FROM dbms_stats.column_stats_effective s
343 JOIN pg_class c ON c.oid = s.starelid
344 JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum
345 LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
346 WHERE NOT a.attisdropped AND has_column_privilege(c.oid, a.attnum, 'select'::text) AND (c.relrowsecurity = false OR NOT row_security_active(c.oid));
347 Options: security_barrier=true
350 \dS+ dbms_stats.column_stats_locked
351 Table "dbms_stats.column_stats_locked"
352 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
353 -------------+---------------------+-----------+----------+---------+----------+--------------+-------------
354 starelid | oid | | not null | | plain | |
355 staattnum | smallint | | not null | | plain | |
356 stainherit | boolean | | not null | | plain | |
357 stanullfrac | real | | | | plain | |
358 stawidth | integer | | | | plain | |
359 stadistinct | real | | | | plain | |
360 stakind1 | smallint | | | | plain | |
361 stakind2 | smallint | | | | plain | |
362 stakind3 | smallint | | | | plain | |
363 stakind4 | smallint | | | | plain | |
364 stakind5 | smallint | | | | plain | |
365 staop1 | oid | | | | plain | |
366 staop2 | oid | | | | plain | |
367 staop3 | oid | | | | plain | |
368 staop4 | oid | | | | plain | |
369 staop5 | oid | | | | plain | |
370 stanumbers1 | real[] | | | | extended | |
371 stanumbers2 | real[] | | | | extended | |
372 stanumbers3 | real[] | | | | extended | |
373 stanumbers4 | real[] | | | | extended | |
374 stanumbers5 | real[] | | | | extended | |
375 stavalues1 | dbms_stats.anyarray | | | | extended | |
376 stavalues2 | dbms_stats.anyarray | | | | extended | |
377 stavalues3 | dbms_stats.anyarray | | | | extended | |
378 stavalues4 | dbms_stats.anyarray | | | | extended | |
379 stavalues5 | dbms_stats.anyarray | | | | extended | |
381 "column_stats_locked_pkey" PRIMARY KEY, btree (starelid, staattnum, stainherit)
382 Foreign-key constraints:
383 "column_stats_locked_starelid_fkey" FOREIGN KEY (starelid) REFERENCES dbms_stats.relation_stats_locked(relid) ON DELETE CASCADE
385 invalidate_column_cache BEFORE INSERT OR DELETE OR UPDATE ON dbms_stats.column_stats_locked FOR EACH ROW EXECUTE PROCEDURE dbms_stats.invalidate_column_cache()
388 \dS+ dbms_stats.relation_stats_locked
389 Table "dbms_stats.relation_stats_locked"
390 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
391 ------------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
392 relid | oid | | not null | | plain | |
393 relname | text | | not null | | extended | |
394 relpages | integer | | | | plain | |
395 reltuples | real | | | | plain | |
396 relallvisible | integer | | | | plain | |
397 curpages | integer | | | | plain | |
398 last_analyze | timestamp with time zone | | | | plain | |
399 last_autoanalyze | timestamp with time zone | | | | plain | |
401 "relation_stats_locked_pkey" PRIMARY KEY, btree (relid)
403 TABLE "dbms_stats.column_stats_locked" CONSTRAINT "column_stats_locked_starelid_fkey" FOREIGN KEY (starelid) REFERENCES dbms_stats.relation_stats_locked(relid) ON DELETE CASCADE
405 invalidate_relation_cache BEFORE INSERT OR DELETE OR UPDATE ON dbms_stats.relation_stats_locked FOR EACH ROW EXECUTE PROCEDURE dbms_stats.invalidate_relation_cache()
408 * No.2-4 dbms_stats.anyarray
411 SELECT n.nspname, t.typname, t.typlen, t.typbyval, t.typtype,
412 t.typcategory, t.typispreferred, t.typisdefined, t.typdelim,
413 t.typrelid, t.typelem, t.typinput, t.typoutput, t.typreceive,
414 t.typsend, t.typmodin, t.typmodout, t.typanalyze, t.typalign,
415 t.typstorage, t.typnotnull, t.typbasetype, t.typtypmod, t.typndims,
416 t.typcollation, t.typdefaultbin, t.typdefault, t.typacl
417 FROM pg_type t, pg_namespace n
418 WHERE t.typnamespace = n.oid
419 AND n.nspname = 'dbms_stats'
420 AND t.typname = 'anyarray';
421 nspname | typname | typlen | typbyval | typtype | typcategory | typispreferred | typisdefined | typdelim | typrelid | typelem | typinput | typoutput | typreceive | typsend | typmodin | typmodout | typanalyze | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typcollation | typdefaultbin | typdefault | typacl
422 ------------+----------+--------+----------+---------+-------------+----------------+--------------+----------+----------+---------+------------------------+-------------------------+--------------------------+--------------------------+----------+-----------+------------+----------+------------+------------+-------------+-----------+----------+--------------+---------------+------------+--------
423 dbms_stats | anyarray | -1 | f | b | P | f | t | , | 0 | 0 | dbms_stats.anyarray_in | dbms_stats.anyarray_out | dbms_stats.anyarray_recv | dbms_stats.anyarray_send | - | - | - | d | x | f | 0 | -1 | 0 | 0 | (null) | (null) | (null)
427 * No.5-1 dbms_stats.merge
429 UPDATE pg_statistic SET
430 stanullfrac = staattnum,
431 stawidth = staattnum,
432 stadistinct = staattnum,
443 stanumbers1 = ARRAY[staattnum,4],
444 stanumbers2 = ARRAY[staattnum,1],
445 stanumbers3 = ARRAY[staattnum,2],
446 stanumbers4 = ARRAY[staattnum,3],
447 stanumbers5 = ARRAY[staattnum,5],
448 stavalues2 = array_cat(stavalues1,stavalues1),
449 stavalues3 = array_cat(array_cat(stavalues1,stavalues1),stavalues1),
450 stavalues4 = array_cat(array_cat(array_cat(stavalues1,stavalues1),stavalues1),stavalues1)
451 ,stavalues5 = array_cat(array_cat(array_cat(array_cat(stavalues1,stavalues1),stavalues1),stavalues1),stavalues1)
452 WHERE starelid = 'st0'::regclass;
453 SELECT dbms_stats.lock_table_stats('st0');
459 UPDATE dbms_stats.column_stats_locked SET
461 stanullfrac = -staattnum,
462 stawidth = -staattnum,
463 stadistinct = -staattnum,
474 stanumbers1 = ARRAY[-staattnum,22],
475 stanumbers2 = ARRAY[-staattnum,23],
476 stanumbers3 = ARRAY[-staattnum,24],
477 stanumbers4 = ARRAY[-staattnum,21],
478 stanumbers5 = ARRAY[-staattnum,25],
479 stavalues1 = stavalues3,
480 stavalues2 = stavalues2,
481 stavalues3 = stavalues1,
482 stavalues4 = stavalues4
483 ,stavalues5 = stavalues5
486 * Driver function dbms_stats.merge1
488 CREATE FUNCTION dbms_stats.merge1(
489 lhs dbms_stats.column_stats_locked,
490 rhs pg_catalog.pg_statistic
492 '$libdir/pg_dbms_stats', 'dbms_stats_merge'
494 SELECT * FROM columns_locked_v
495 WHERE starelid = 'st0'::regclass;
496 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
497 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+---------------------------+-------------------+------------+-----------------------------------+-------------------------------------------
498 st0 | 1 | id | t | -1 | -1 | -1 | 2 | 3 | 4 | 1 | 5 | 22 | 23 | 24 | 21 | 25 | {-1,22} | {-1,23} | {-1,24} | {-1,21} | {-1,25} | {1,2,1,2,1,2} | {1,2,1,2} | {1,2} | {1,2,1,2,1,2,1,2} | {1,2,1,2,1,2,1,2,1,2}
499 st0 | 2 | name | t | -2 | -2 | -2 | 2 | 3 | 4 | 1 | 5 | 22 | 23 | 24 | 21 | 25 | {-2,22} | {-2,23} | {-2,24} | {-2,21} | {-2,25} | {"test ","test ","test "} | {"test ","test "} | {"test "} | {"test ","test ","test ","test "} | {"test ","test ","test ","test ","test "}
502 SELECT * FROM plain_columns_statistic_v
503 WHERE starelid = 'st0'::regclass;
504 starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
505 ----------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------+-------------------+---------------------------+-----------------------------------+-------------------------------------------
506 st0 | 1 | f | 1 | 1 | 1 | 4 | 1 | 2 | 3 | 5 | 14 | 11 | 12 | 13 | 15 | {1,4} | {1,1} | {1,2} | {1,3} | {1,5} | {1,2} | {1,2,1,2} | {1,2,1,2,1,2} | {1,2,1,2,1,2,1,2} | {1,2,1,2,1,2,1,2,1,2}
507 st0 | 2 | f | 2 | 2 | 2 | 4 | 1 | 2 | 3 | 5 | 14 | 11 | 12 | 13 | 15 | {2,4} | {2,1} | {2,2} | {2,3} | {2,5} | {"test "} | {"test ","test "} | {"test ","test ","test "} | {"test ","test ","test ","test "} | {"test ","test ","test ","test ","test "}
510 SET client_min_messages TO LOG;
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(NULL, s)
540 WHERE starelid = 'st0'::regclass
541 AND staattnum = '1'::int2) m;
542 starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
543 ----------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------+------------+---------------+-------------------+-----------------------
544 st0 | 1 | f | 1 | 1 | 1 | 4 | 1 | 2 | 3 | 5 | 14 | 11 | 12 | 13 | 15 | {1,4} | {1,1} | {1,2} | {1,3} | {1,5} | {1,2} | {1,2,1,2} | {1,2,1,2,1,2} | {1,2,1,2,1,2,1,2} | {1,2,1,2,1,2,1,2,1,2}
548 SELECT (m.merge).starelid::regclass,
550 (m.merge).stainherit,
551 (m.merge).stanullfrac,
553 (m.merge).stadistinct,
564 (m.merge).stanumbers1,
565 (m.merge).stanumbers2,
566 (m.merge).stanumbers3,
567 (m.merge).stanumbers4,
568 (m.merge).stanumbers5,
569 (m.merge).stavalues1,
570 (m.merge).stavalues2,
571 (m.merge).stavalues3,
573 ,(m.merge).stavalues5
574 FROM (SELECT dbms_stats.merge(v, NULL)
575 FROM dbms_stats.column_stats_locked v
576 WHERE starelid = 'st0'::regclass
577 AND staattnum = '2'::int2) m;
578 starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
579 ----------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+---------------------------+-------------------+------------+-----------------------------------+-------------------------------------------
580 st0 | 2 | t | -2 | -2 | -2 | 2 | 3 | 4 | 1 | 5 | 22 | 23 | 24 | 21 | 25 | {-2,22} | {-2,23} | {-2,24} | {-2,21} | {-2,25} | {"test ","test ","test "} | {"test ","test "} | {"test "} | {"test ","test ","test ","test "} | {"test ","test ","test ","test ","test "}
584 SELECT dbms_stats.merge(NULL, NULL);
591 SELECT (m.merge).starelid::regclass,
593 (m.merge).stainherit,
594 (m.merge).stanullfrac,
596 (m.merge).stadistinct,
607 (m.merge).stanumbers1,
608 (m.merge).stanumbers2,
609 (m.merge).stanumbers3,
610 (m.merge).stanumbers4,
611 (m.merge).stanumbers5,
612 (m.merge).stavalues1,
613 (m.merge).stavalues2,
614 (m.merge).stavalues3,
616 ,(m.merge).stavalues5
617 FROM (SELECT dbms_stats.merge(v, s)
618 FROM dbms_stats.column_stats_locked v,
620 WHERE v.starelid = 'st0'::regclass
621 AND v.staattnum = '2'::int2
622 AND s.starelid = 'st0'::regclass
623 AND s.staattnum = '1'::int2) m;
624 starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
625 ----------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+---------------------------+-------------------+------------+-----------------------------------+-------------------------------------------
626 st0 | 2 | t | -2 | -2 | -2 | 2 | 3 | 4 | 1 | 5 | 22 | 23 | 24 | 21 | 25 | {-2,22} | {-2,23} | {-2,24} | {-2,21} | {-2,25} | {"test ","test ","test "} | {"test ","test "} | {"test "} | {"test ","test ","test ","test "} | {"test ","test ","test ","test ","test "}
630 SELECT (m.merge).starelid::regclass,
632 (m.merge).stainherit,
633 (m.merge).stanullfrac,
635 (m.merge).stadistinct,
646 (m.merge).stanumbers1,
647 (m.merge).stanumbers2,
648 (m.merge).stanumbers3,
649 (m.merge).stanumbers4,
650 (m.merge).stanumbers5,
651 (m.merge).stavalues1,
652 (m.merge).stavalues2,
653 (m.merge).stavalues3,
655 ,(m.merge).stavalues5
656 FROM (SELECT dbms_stats.merge(v, s)
657 FROM dbms_stats.column_stats_locked v,
659 WHERE v.starelid = 'st0'::regclass
660 AND v.staattnum = '2'::int2
661 AND s.starelid = 'st0'::regclass
662 AND s.staattnum = '1'::int2) m;
663 starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
664 ----------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+---------------------------+-------------------+------------+-----------------------------------+-------------------------------------------
665 st0 | 2 | t | -2 | -2 | -2 | 2 | 3 | 4 | 1 | 5 | 22 | 23 | 24 | 21 | 25 | {-2,22} | {-2,23} | {-2,24} | {-2,21} | {-2,25} | {"test ","test ","test "} | {"test ","test "} | {"test "} | {"test ","test ","test ","test "} | {"test ","test ","test ","test ","test "}
669 SELECT dbms_stats.merge1(v, s)
670 FROM dbms_stats.column_stats_locked v,
672 WHERE v.starelid = 'st0'::regclass
673 AND v.staattnum = '2'::int2
674 AND s.starelid = 'st0'::regclass
675 AND s.staattnum = '1'::int2;
676 ERROR: return type must be a row type
678 SELECT dbms_stats.merge(NULL, (
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
691 WHERE s.starelid = 'st0'::regclass
692 AND s.staattnum = '1'::int2;
699 SELECT (m.merge).starelid::regclass,
701 (m.merge).stainherit,
702 (m.merge).stanullfrac,
704 (m.merge).stadistinct,
715 (m.merge).stanumbers1,
716 (m.merge).stanumbers2,
717 (m.merge).stanumbers3,
718 (m.merge).stanumbers4,
719 (m.merge).stanumbers5,
720 (m.merge).stavalues1,
721 (m.merge).stavalues2,
722 (m.merge).stavalues3,
724 ,(m.merge).stavalues5
725 FROM (SELECT dbms_stats.merge(NULL, (
726 s.starelid::regclass, s.staattnum, s.stainherit,
727 s.stanullfrac, s.stawidth, s.stadistinct,
728 s.stakind1, s.stakind2, s.stakind3, s.stakind4,
730 s.staop1, s.staop2, s.staop3, s.staop4,
732 NULL, s.stanumbers2, s.stanumbers3, s.stanumbers4,
734 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
738 WHERE s.starelid = 'st0'::regclass
739 AND s.staattnum = '1'::int2) m;
740 starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
741 ----------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------+------------+---------------+-------------------+-----------------------
742 st0 | 1 | f | 1 | 1 | 1 | 4 | 1 | 2 | 3 | 5 | 14 | 11 | 12 | 13 | 15 | (null) | {1,1} | {1,2} | {1,3} | {1,5} | {1,2} | {1,2,1,2} | {1,2,1,2,1,2} | {1,2,1,2,1,2,1,2} | {1,2,1,2,1,2,1,2,1,2}
746 SELECT dbms_stats.merge((
747 v.starelid::regclass, v.staattnum, v.stainherit,
748 v.stanullfrac, v.stawidth, v.stadistinct,
749 v.stakind1, v.stakind2, v.stakind3, v.stakind4,
751 v.staop1, v.staop2, v.staop3,
753 NULL, v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
755 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
758 FROM dbms_stats.column_stats_locked v
759 WHERE v.starelid = 'st0'::regclass
760 AND v.staattnum = '2'::int2;
767 SELECT (m.merge).starelid::regclass,
769 (m.merge).stainherit,
770 (m.merge).stanullfrac,
772 (m.merge).stadistinct,
783 (m.merge).stanumbers1,
784 (m.merge).stanumbers2,
785 (m.merge).stanumbers3,
786 (m.merge).stanumbers4,
787 (m.merge).stanumbers5,
788 (m.merge).stavalues1,
789 (m.merge).stavalues2,
790 (m.merge).stavalues3,
792 ,(m.merge).stavalues5
793 FROM (SELECT dbms_stats.merge((
794 v.starelid::regclass, v.staattnum, v.stainherit,
795 v.stanullfrac, v.stawidth, v.stadistinct,
796 v.stakind1, v.stakind2, v.stakind3, v.stakind4,
798 v.staop1, v.staop2, v.staop3, v.staop4,
800 NULL, v.stanumbers2, v.stanumbers3, v.stanumbers4,
802 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
805 FROM dbms_stats.column_stats_locked v
806 WHERE v.starelid = 'st0'::regclass
807 AND v.staattnum = '2'::int2) m;
808 starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
809 ----------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+---------------------------+-------------------+------------+-----------------------------------+-------------------------------------------
810 st0 | 2 | t | -2 | -2 | -2 | 2 | 3 | 4 | 1 | 5 | 22 | 23 | 24 | 21 | 25 | (null) | {-2,23} | {-2,24} | {-2,21} | {-2,25} | {"test ","test ","test "} | {"test ","test "} | {"test "} | {"test ","test ","test ","test "} | {"test ","test ","test ","test ","test "}
814 SELECT dbms_stats.merge((
815 v.starelid::regclass, v.staattnum, v.stainherit,
816 v.stanullfrac, v.stawidth, v.stadistinct,
817 v.stakind1, v.stakind2, v.stakind3, v.stakind4,
819 v.staop1, v.staop2, v.staop3,
821 NULL, v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
823 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
826 s.starelid::regclass, s.staattnum, s.stainherit,
827 s.stanullfrac, s.stawidth, s.stadistinct,
828 s.stakind1, s.stakind2, s.stakind3, s.stakind4,
830 s.staop1, s.staop2, s.staop3,
832 NULL, s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
834 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
837 FROM dbms_stats.column_stats_locked v,
839 WHERE v.starelid = 'st0'::regclass
840 AND v.staattnum = '2'::int2
841 AND s.starelid = 'st0'::regclass
842 AND s.staattnum = '1'::int2;
843 LOG: pg_dbms_stats: bad statistics
844 DETAIL: column "staop5" should not be null
851 SELECT (m.merge).starelid::regclass,
853 (m.merge).stainherit,
854 (m.merge).stanullfrac,
856 (m.merge).stadistinct,
867 (m.merge).stanumbers1,
868 (m.merge).stanumbers2,
869 (m.merge).stanumbers3,
870 (m.merge).stanumbers4,
871 (m.merge).stanumbers5,
872 (m.merge).stavalues1,
873 (m.merge).stavalues2,
874 (m.merge).stavalues3,
876 ,(m.merge).stavalues5
877 FROM (SELECT dbms_stats.merge((
878 v.starelid::regclass, v.staattnum, v.stainherit,
879 v.stanullfrac, v.stawidth, v.stadistinct,
880 v.stakind1, v.stakind2, v.stakind3, v.stakind4,
882 v.staop1, v.staop2, v.staop3, v.staop4,
884 NULL, v.stanumbers2, v.stanumbers3, v.stanumbers4,
886 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
889 s.starelid::regclass, s.staattnum, s.stainherit,
890 s.stanullfrac, s.stawidth, s.stadistinct,
891 s.stakind1, s.stakind2, s.stakind3, s.stakind4,
893 s.staop1, s.staop2, s.staop3, s.staop4,
895 NULL, s.stanumbers2, s.stanumbers3, s.stanumbers4,
897 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
900 FROM dbms_stats.column_stats_locked v,
902 WHERE v.starelid = 'st0'::regclass
903 AND v.staattnum = '2'::int2
904 AND s.starelid = 'st0'::regclass
905 AND s.staattnum = '1'::int2) m;
906 starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
907 ----------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+---------------------------+-------------------+------------+-----------------------------------+-------------------------------------------
908 st0 | 2 | t | -2 | -2 | -2 | 2 | 3 | 4 | 1 | 5 | 22 | 23 | 24 | 21 | 25 | (null) | {-2,23} | {-2,24} | {-2,21} | {-2,25} | {"test ","test ","test "} | {"test ","test "} | {"test "} | {"test ","test ","test ","test "} | {"test ","test ","test ","test ","test "}
912 SELECT (m.merge).starelid::regclass,
914 (m.merge).stainherit,
915 (m.merge).stanullfrac,
917 (m.merge).stadistinct,
928 (m.merge).stanumbers1,
929 (m.merge).stanumbers2,
930 (m.merge).stanumbers3,
931 (m.merge).stanumbers4,
932 (m.merge).stanumbers5,
933 (m.merge).stavalues1,
934 (m.merge).stavalues2,
935 (m.merge).stavalues3,
937 ,(m.merge).stavalues5
938 FROM (SELECT dbms_stats.merge((
941 NULL, NULL, NULL, NULL,
942 NULL, NULL, NULL, NULL,
943 NULL, NULL, NULL, NULL,
944 NULL, NULL, NULL, NULL
945 ,NULL, NULL, NULL, NULL
948 WHERE s.starelid = 'st0'::regclass
949 AND s.staattnum = '1'::int2) m;
950 starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
951 ----------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------+------------+---------------+-------------------+-----------------------
952 st0 | 1 | f | 1 | 1 | 1 | 4 | 1 | 2 | 3 | 5 | 14 | 11 | 12 | 13 | 15 | {1,4} | {1,1} | {1,2} | {1,3} | {1,5} | {1,2} | {1,2,1,2} | {1,2,1,2,1,2} | {1,2,1,2,1,2,1,2} | {1,2,1,2,1,2,1,2,1,2}
956 SELECT (m.merge).starelid::regclass,
958 (m.merge).stainherit,
959 (m.merge).stanullfrac,
961 (m.merge).stadistinct,
972 (m.merge).stanumbers1,
973 (m.merge).stanumbers2,
974 (m.merge).stanumbers3,
975 (m.merge).stanumbers4,
976 (m.merge).stanumbers5,
977 (m.merge).stavalues1,
978 (m.merge).stavalues2,
979 (m.merge).stavalues3,
981 ,(m.merge).stavalues5
982 FROM (SELECT dbms_stats.merge(v, (
985 NULL, NULL, NULL, NULL,
986 NULL, NULL, NULL, NULL,
987 NULL, NULL, NULL, NULL,
988 NULL, NULL, NULL, NULL,
989 NULL, NULL, NULL, NULL))
990 FROM dbms_stats.column_stats_locked v
991 WHERE v.starelid = 'st0'::regclass
992 AND v.staattnum = '2'::int2) m;
993 starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
994 ----------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+---------------------------+-------------------+------------+-----------------------------------+-------------------------------------------
995 st0 | 2 | t | -2 | -2 | -2 | 2 | 3 | 4 | 1 | 5 | 22 | 23 | 24 | 21 | 25 | {-2,22} | {-2,23} | {-2,24} | {-2,21} | {-2,25} | {"test ","test ","test "} | {"test ","test "} | {"test "} | {"test ","test ","test ","test "} | {"test ","test ","test ","test ","test "}
999 SELECT (m.merge).starelid::regclass,
1000 (m.merge).staattnum,
1001 (m.merge).stainherit,
1002 (m.merge).stanullfrac,
1004 (m.merge).stadistinct,
1015 (m.merge).stanumbers1,
1016 (m.merge).stanumbers2,
1017 (m.merge).stanumbers3,
1018 (m.merge).stanumbers4,
1019 (m.merge).stanumbers5,
1020 (m.merge).stavalues1,
1021 (m.merge).stavalues2,
1022 (m.merge).stavalues3,
1023 (m.merge).stavalues4
1024 ,(m.merge).stavalues5
1025 FROM (SELECT dbms_stats.merge(v, s)
1026 FROM dbms_stats.column_stats_locked v,
1028 WHERE v.starelid = 'st0'::regclass
1029 AND v.staattnum = '2'::int2
1030 AND s.starelid = 'st0'::regclass
1031 AND s.staattnum = '1'::int2) m;
1032 starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
1033 ----------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+---------------------------+-------------------+------------+-----------------------------------+-------------------------------------------
1034 st0 | 2 | t | -2 | -2 | -2 | 2 | 3 | 4 | 1 | 5 | 22 | 23 | 24 | 21 | 25 | {-2,22} | {-2,23} | {-2,24} | {-2,21} | {-2,25} | {"test ","test ","test "} | {"test ","test "} | {"test "} | {"test ","test ","test ","test "} | {"test ","test ","test ","test ","test "}
1038 SELECT dbms_stats.merge((
1039 v.starelid::regclass, v.staattnum, v.stainherit,
1040 v.stanullfrac, v.stawidth, v.stadistinct,
1041 NULL, NULL, NULL, NULL,
1042 NULL, NULL, NULL, NULL,
1043 NULL, NULL, NULL, NULL,
1044 NULL, NULL, NULL, NULL
1045 ,NULL, NULL, NULL, NULL
1047 s.starelid::regclass, s.staattnum, s.stainherit,
1048 s.stanullfrac, s.stawidth, s.stadistinct,
1049 NULL, NULL, NULL, NULL,
1050 NULL, NULL, NULL, NULL,
1051 NULL, NULL, NULL, NULL,
1052 NULL, NULL, NULL, NULL,
1053 NULL, NULL, NULL, NULL))
1054 FROM dbms_stats.column_stats_locked v,
1056 WHERE v.starelid = 'st0'::regclass
1057 AND v.staattnum = '2'::int2
1058 AND s.starelid = 'st0'::regclass
1059 AND s.staattnum = '1'::int2;
1060 LOG: pg_dbms_stats: bad statistics
1061 DETAIL: column "stakind1" should not be null
1068 SELECT (m.merge).starelid::regclass,
1069 (m.merge).staattnum,
1070 (m.merge).stainherit,
1071 (m.merge).stanullfrac,
1073 (m.merge).stadistinct,
1084 (m.merge).stanumbers1,
1085 (m.merge).stanumbers2,
1086 (m.merge).stanumbers3,
1087 (m.merge).stanumbers4,
1088 (m.merge).stanumbers5,
1089 (m.merge).stavalues1,
1090 (m.merge).stavalues2,
1091 (m.merge).stavalues3,
1092 (m.merge).stavalues4
1093 ,(m.merge).stavalues5
1094 FROM (SELECT dbms_stats.merge((
1095 v.starelid::regclass, v.staattnum, v.stainherit,
1096 v.stanullfrac, v.stawidth, v.stadistinct,
1097 v.stakind1, v.stakind2, v.stakind3, v.stakind4,
1099 v.staop1, v.staop2, v.staop3,
1101 NULL, v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
1103 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
1106 s.starelid::regclass, s.staattnum, s.stainherit,
1107 s.stanullfrac, s.stawidth, s.stadistinct,
1108 s.stakind1, s.stakind2, s.stakind3, s.stakind4,
1110 s.staop1, s.staop2, s.staop3, s.staop4,
1112 s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
1114 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
1117 FROM dbms_stats.column_stats_locked v,
1119 WHERE v.starelid = 'st0'::regclass
1120 AND v.staattnum = '1'::int2
1121 AND s.starelid = 'st0'::regclass
1122 AND s.staattnum = '1'::int2) m;
1123 starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
1124 ----------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------+------------+---------------+-------------------+-----------------------
1125 st0 | 1 | t | -1 | -1 | -1 | 4 | 1 | 2 | 3 | 5 | 14 | 11 | 12 | 13 | 15 | {1,4} | {1,1} | {1,2} | {1,3} | {1,5} | {1,2} | {1,2,1,2} | {1,2,1,2,1,2} | {1,2,1,2,1,2,1,2} | {1,2,1,2,1,2,1,2,1,2}
1129 SELECT dbms_stats.merge((
1130 v.starelid::regclass, v.staattnum, v.stainherit,
1131 v.stanullfrac, v.stawidth, v.stadistinct,
1132 v.stakind1, v.stakind2, v.stakind3, v.stakind4,
1134 v.staop1, v.staop2, v.staop3,
1136 NULL, v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
1138 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
1141 s.starelid::regclass, s.staattnum, s.stainherit,
1142 s.stanullfrac, s.stawidth, s.stadistinct,
1143 s.stakind1, s.stakind2, s.stakind3, s.stakind4,
1145 s.staop1, s.staop2, s.staop3,
1147 NULL, s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
1149 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
1152 FROM dbms_stats.column_stats_locked v,
1154 WHERE v.starelid = 'st0'::regclass
1155 AND v.staattnum = '1'::int2
1156 AND s.starelid = 'st0'::regclass
1157 AND s.staattnum = '1'::int2;
1158 LOG: pg_dbms_stats: bad statistics
1159 DETAIL: column "staop5" should not be null
1166 SELECT (m.merge).starelid::regclass,
1167 (m.merge).staattnum,
1168 (m.merge).stainherit,
1169 (m.merge).stanullfrac,
1171 (m.merge).stadistinct,
1182 (m.merge).stanumbers1,
1183 (m.merge).stanumbers2,
1184 (m.merge).stanumbers3,
1185 (m.merge).stanumbers4,
1186 (m.merge).stanumbers5,
1187 (m.merge).stavalues1,
1188 (m.merge).stavalues2,
1189 (m.merge).stavalues3,
1190 (m.merge).stavalues4
1191 ,(m.merge).stavalues5
1192 FROM (SELECT dbms_stats.merge((
1193 v.starelid::regclass, v.staattnum, v.stainherit,
1194 v.stanullfrac, v.stawidth, v.stadistinct,
1197 v.staop1, v.staop2, v.staop3, v.staop4,
1199 v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
1201 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
1204 s.starelid::regclass, s.staattnum, s.stainherit,
1205 s.stanullfrac, s.stawidth, s.stadistinct,
1208 s.staop1, s.staop2, s.staop3, s.staop4,
1210 s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
1212 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
1215 FROM dbms_stats.column_stats_locked v,
1217 WHERE v.starelid = 'st0'::regclass
1218 AND v.staattnum = '2'::int2
1219 AND s.starelid = 'st0'::regclass
1220 AND s.staattnum = '1'::int2) m;
1221 starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
1222 ----------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+---------------------------+-------------------+------------+-----------------------------------+-------------------------------------------
1223 st0 | 2 | t | -2 | -2 | -2 | 1 | 1 | 1 | 1 | 1 | 22 | 23 | 24 | 21 | 25 | {-2,22} | {-2,23} | {-2,24} | {-2,21} | {-2,25} | {"test ","test ","test "} | {"test ","test "} | {"test "} | {"test ","test ","test ","test "} | {"test ","test ","test ","test ","test "}
1227 SELECT (m.merge).starelid::regclass,
1228 (m.merge).staattnum,
1229 (m.merge).stainherit,
1230 (m.merge).stanullfrac,
1232 (m.merge).stadistinct,
1243 (m.merge).stanumbers1,
1244 (m.merge).stanumbers2,
1245 (m.merge).stanumbers3,
1246 (m.merge).stanumbers4,
1247 (m.merge).stanumbers5,
1248 (m.merge).stavalues1,
1249 (m.merge).stavalues2,
1250 (m.merge).stavalues3,
1251 (m.merge).stavalues4
1252 ,(m.merge).stavalues5
1253 FROM (SELECT dbms_stats.merge((v.starelid::regclass, v.staattnum, v.stainherit,
1254 v.stanullfrac, v.stawidth, v.stadistinct,
1257 v.staop1, v.staop2, v.staop3, v.staop4,
1259 v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
1261 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
1264 s.starelid::regclass, s.staattnum, s.stainherit,
1265 s.stanullfrac, s.stawidth, s.stadistinct,
1268 s.staop1, s.staop2, s.staop3, s.staop4,
1270 s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
1272 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
1275 FROM dbms_stats.column_stats_locked v,
1277 WHERE v.starelid = 'st0'::regclass
1278 AND v.staattnum = '2'::int2
1279 AND s.starelid = 'st0'::regclass
1280 AND s.staattnum = '1'::int2) m;
1281 starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
1282 ----------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+---------------------------+-------------------+------------+-----------------------------------+-------------------------------------------
1283 st0 | 2 | t | -2 | -2 | -2 | 2 | 2 | 2 | 2 | 2 | 22 | 23 | 24 | 21 | 25 | {-2,22} | {-2,23} | {-2,24} | {-2,21} | {-2,25} | {"test ","test ","test "} | {"test ","test "} | {"test "} | {"test ","test ","test ","test "} | {"test ","test ","test ","test ","test "}
1287 SELECT dbms_stats.merge((v.starelid::regclass, '2', v.stainherit,
1288 v.stanullfrac, v.stawidth, v.stadistinct,
1291 v.staop1, v.staop2, v.staop3, v.staop4,
1293 v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
1295 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
1298 s.starelid::regclass, s.staattnum, s.stainherit,
1299 s.stanullfrac, s.stawidth, s.stadistinct,
1302 s.staop1, s.staop2, s.staop3, s.staop4,
1304 s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
1306 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
1309 FROM dbms_stats.column_stats_locked v,
1311 WHERE v.starelid = 'st0'::regclass
1312 AND v.staattnum = '1'::int2
1313 AND s.starelid = 'st0'::regclass
1314 AND s.staattnum = '1'::int2;
1315 LOG: pg_dbms_stats: bad column type
1316 DETAIL: type of column "name" has been changed
1317 HINT: need to execute dbms_stats.unlock('st0', 'name')
1324 SELECT dbms_stats.merge((v.starelid::regclass, '2', v.stainherit,
1325 v.stanullfrac, v.stawidth, v.stadistinct,
1328 v.staop1, v.staop2, v.staop3, v.staop4,
1330 v.stanumbers1, v.stanumbers2, v.stanumbers3, v.stanumbers4,
1332 v.stavalues1, v.stavalues2, v.stavalues3, v.stavalues4
1335 s.starelid::regclass, s.staattnum, s.stainherit,
1336 s.stanullfrac, s.stawidth, s.stadistinct,
1339 s.staop1, s.staop2, s.staop3, s.staop4,
1341 s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4,
1343 s.stavalues1, s.stavalues2, s.stavalues3, s.stavalues4
1346 FROM dbms_stats.column_stats_locked v,
1348 WHERE v.starelid = 'st0'::regclass
1349 AND v.staattnum = '1'::int2
1350 AND s.starelid = 'st0'::regclass
1351 AND s.staattnum = '1'::int2;
1352 LOG: pg_dbms_stats: bad column type
1353 DETAIL: type of column "name" has been changed
1354 HINT: need to execute dbms_stats.unlock('st0', 'name')
1360 RESET client_min_messages;
1361 SELECT dbms_stats.unlock_database_stats();
1362 unlock_database_stats
1363 -----------------------
1368 * No.6-4 dbms_stats.is_target_relkind
1371 SELECT dbms_stats.is_target_relkind(k::"char")
1372 FROM (VALUES ('r'), ('i'), ('f'), ('m'),
1373 ('S'), ('t'), ('v'), ('c')) t(k);
1387 * No.7-1 dbms_stats.backup
1389 DELETE FROM dbms_stats.backup_history;
1390 INSERT INTO dbms_stats.backup_history(id, time, unit) values(1, '2012-01-01', 'd');
1392 SELECT dbms_stats.backup(1, 's0.sft0'::regclass, NULL);
1398 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1404 SELECT count(*) FROM dbms_stats.column_stats_backup;
1411 DELETE FROM dbms_stats.relation_stats_backup;
1412 SELECT dbms_stats.backup(1, 's0.smv0'::regclass, NULL);
1418 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1424 SELECT count(*) FROM dbms_stats.column_stats_backup;
1431 DELETE FROM dbms_stats.relation_stats_backup;
1432 SELECT dbms_stats.backup(1, NULL, 1::int2);
1438 SELECT relid::regclass FROM dbms_stats.relation_stats_backup
1461 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_backup
1462 GROUP BY starelid, staattnum
1463 ORDER BY starelid, staattnum;
1464 starelid | staattnum
1465 ----------+-----------
1478 DELETE FROM dbms_stats.relation_stats_backup;
1479 SELECT dbms_stats.backup(1, NULL::regclass, NULL);
1485 SELECT relid::regclass FROM dbms_stats.relation_stats_backup
1508 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_backup
1509 GROUP BY starelid, staattnum
1510 ORDER BY starelid, staattnum;
1511 starelid | staattnum
1512 ----------+-----------
1533 DELETE FROM dbms_stats.relation_stats_backup;
1534 \! psql contrib_regression -c "SELECT dbms_stats.backup(NULL, 's0.st0'::regclass, NULL)" > results/ut_no2_1_17.out 2>&1
1535 SELECT count(*) FROM dbms_stats.relation_stats_backup;
1541 SELECT count(*) FROM dbms_stats.column_stats_backup;
1548 * No.8-1 dbms_stats.backup
1550 SELECT setval('dbms_stats.backup_history_id_seq',1, false);
1557 * Stab function dbms_stats.backup
1559 ALTER FUNCTION dbms_stats.backup(
1563 RENAME TO truth_func_backup;
1564 CREATE OR REPLACE FUNCTION dbms_stats.backup(
1571 RAISE NOTICE 'arguments are %, %, %', $1, $2, $3;
1577 DELETE FROM dbms_stats.backup_history;
1578 SELECT dbms_stats.backup('s0.st0'::regclass, 'id', 'dummy comment');
1579 NOTICE: arguments are 1, s0.st0, 1
1585 SELECT id, unit, comment FROM dbms_stats.backup_history;
1587 ----+------+---------------
1588 1 | c | dummy comment
1592 DELETE FROM dbms_stats.backup_history;
1593 SELECT dbms_stats.backup('s0.st0'::regclass, NULL, 'dummy comment');
1594 NOTICE: arguments are 2, s0.st0, <NULL>
1600 SELECT id, unit, comment FROM dbms_stats.backup_history;
1602 ----+------+---------------
1603 2 | t | dummy comment
1607 DELETE FROM dbms_stats.backup_history;
1608 SELECT dbms_stats.backup(NULL::regclass, 'id', 'dummy comment');
1609 ERROR: relation required
1610 CONTEXT: PL/pgSQL function dbms_stats.backup(regclass,text,text) line 9 at RAISE
1611 SELECT id, unit, comment FROM dbms_stats.backup_history;
1613 ----+------+---------
1617 DELETE FROM dbms_stats.backup_history;
1618 SELECT dbms_stats.backup(NULL::regclass, NULL, 'dummy comment');
1619 NOTICE: arguments are 3, <NULL>, <NULL>
1625 SELECT id, unit, comment FROM dbms_stats.backup_history;
1627 ----+------+---------------
1628 3 | d | dummy comment
1632 DELETE FROM dbms_stats.backup_history;
1633 SELECT dbms_stats.backup(0, NULL, 'dummy comment');
1634 ERROR: relation "-" not found
1635 CONTEXT: PL/pgSQL function dbms_stats.backup(regclass,text,text) line 15 at RAISE
1636 SELECT id, unit, comment FROM dbms_stats.backup_history;
1638 ----+------+---------
1642 DELETE FROM dbms_stats.backup_history;
1643 SELECT dbms_stats.backup('s0.st0'::regclass, NULL, 'dummy comment');
1644 NOTICE: arguments are 4, s0.st0, <NULL>
1650 SELECT id, unit, comment FROM dbms_stats.backup_history;
1652 ----+------+---------------
1653 4 | t | dummy comment
1657 DELETE FROM dbms_stats.backup_history;
1658 SELECT dbms_stats.backup(
1659 'pg_toast.pg_toast_2618'::regclass,
1662 ERROR: relation of relkind "t" cannot have statistics to backup: "pg_toast.pg_toast_2618"
1663 HINT: Only tables(r), materialized views(m), foreign tables(f) and indexes(i) are allowed.
1664 CONTEXT: PL/pgSQL function dbms_stats.backup(regclass,text,text) line 18 at RAISE
1665 SELECT id, unit, comment FROM dbms_stats.backup_history;
1667 ----+------+---------
1671 DELETE FROM dbms_stats.backup_history;
1672 SELECT dbms_stats.backup('s0.st0_idx'::regclass, NULL, 'dummy comment');
1673 NOTICE: arguments are 5, s0.st0_idx, <NULL>
1679 SELECT id, unit, comment FROM dbms_stats.backup_history;
1681 ----+------+---------------
1682 5 | t | dummy comment
1686 DELETE FROM dbms_stats.backup_history;
1687 SELECT dbms_stats.backup('s0.ss0'::regclass, NULL, 'dummy comment');
1688 ERROR: relation of relkind "S" cannot have statistics to backup: "s0.ss0"
1689 HINT: Only tables(r), materialized views(m), foreign tables(f) and indexes(i) are allowed.
1690 CONTEXT: PL/pgSQL function dbms_stats.backup(regclass,text,text) line 18 at RAISE
1691 SELECT id, unit, comment FROM dbms_stats.backup_history;
1693 ----+------+---------
1697 DELETE FROM dbms_stats.backup_history;
1698 SELECT dbms_stats.backup('s0.sc0'::regclass, NULL, 'dummy comment');
1699 ERROR: relation of relkind "c" cannot have statistics to backup: "s0.sc0"
1700 HINT: Only tables(r), materialized views(m), foreign tables(f) and indexes(i) are allowed.
1701 CONTEXT: PL/pgSQL function dbms_stats.backup(regclass,text,text) line 18 at RAISE
1702 SELECT id, unit, comment FROM dbms_stats.backup_history;
1704 ----+------+---------
1708 DELETE FROM dbms_stats.backup_history;
1709 SELECT dbms_stats.backup('s0.sft0'::regclass, NULL, 'dummy comment');
1710 NOTICE: arguments are 6, s0.sft0, <NULL>
1716 SELECT id, unit, comment FROM dbms_stats.backup_history;
1718 ----+------+---------------
1719 6 | t | dummy comment
1723 DELETE FROM dbms_stats.backup_history;
1724 SELECT dbms_stats.backup('s0.smv0'::regclass, NULL, 'dummy comment');
1725 NOTICE: arguments are 7, s0.smv0, <NULL>
1731 SELECT id, unit, comment FROM dbms_stats.backup_history;
1733 ----+------+---------------
1734 7 | t | dummy comment
1738 DELETE FROM dbms_stats.backup_history;
1739 SELECT dbms_stats.backup('pg_catalog.pg_class'::regclass, NULL, 'dummy comment');
1740 ERROR: backing up statistics is inhibited for system catalogs: "pg_class"
1741 CONTEXT: PL/pgSQL function dbms_stats.backup(regclass,text,text) line 23 at RAISE
1742 SELECT id, unit, comment FROM dbms_stats.backup_history;
1744 ----+------+---------
1748 DELETE FROM dbms_stats.backup_history;
1749 SELECT dbms_stats.backup('s0.st0'::regclass, 'dummy', 'dummy comment');
1750 ERROR: column "dummy" not found in relation "s0.st0"
1751 CONTEXT: PL/pgSQL function dbms_stats.backup(regclass,text,text) line 29 at RAISE
1752 SELECT id, unit, comment FROM dbms_stats.backup_history;
1754 ----+------+---------
1758 DELETE FROM dbms_stats.backup_history;
1759 DELETE FROM pg_statistic
1760 WHERE starelid = 's0.st0'::regclass
1761 AND staattnum = 1::int2;
1762 SELECT count(*) FROM dbms_stats.column_stats_effective
1763 WHERE starelid = 's0.st0'::regclass
1764 AND staattnum = 1::int2;
1770 SELECT dbms_stats.backup('s0.st0'::regclass, 'id', 'dummy comment');
1771 ERROR: no statistics available for column "id" of relation "s0.st0"
1772 CONTEXT: PL/pgSQL function dbms_stats.backup(regclass,text,text) line 32 at RAISE
1773 SELECT id, unit, comment FROM dbms_stats.backup_history;
1775 ----+------+---------
1779 * Stab function dbms_stats.backup
1781 ALTER FUNCTION dbms_stats.backup(
1785 RENAME TO truth_func_backup;
1786 CREATE OR REPLACE FUNCTION dbms_stats.backup(
1787 relid regclass DEFAULT NULL,
1788 attname text DEFAULT NULL,
1789 comment text DEFAULT NULL)
1793 IF $3 = '<NULL>' THEN
1794 RAISE NOTICE 'third argument is not NULL but string "<NULL>"';
1796 RAISE NOTICE 'arguments are %, %, %', $1, $2, $3;
1802 * No.8-3 dbms_stats.backup_schema_stats
1804 SELECT setval('dbms_stats.backup_history_id_seq',9, false);
1811 SELECT dbms_stats.backup_schema_stats('s0', 'comment');
1812 NOTICE: arguments are 9, s0.st0, <NULL>
1813 NOTICE: arguments are 9, s0.st0_idx, <NULL>
1814 NOTICE: arguments are 9, s0.st1, <NULL>
1815 NOTICE: arguments are 9, s0.st1_idx, <NULL>
1816 NOTICE: arguments are 9, s0.st2, <NULL>
1817 NOTICE: arguments are 9, s0.st2_idx, <NULL>
1818 NOTICE: arguments are 9, s0.sft0, <NULL>
1819 NOTICE: arguments are 9, s0.smv0, <NULL>
1821 ---------------------
1825 SELECT id, unit, comment FROM dbms_stats.backup_history
1829 ----+------+---------
1834 SELECT dbms_stats.backup_schema_stats('s00', 'comment');
1835 ERROR: schema "s00" not found
1836 CONTEXT: PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 7 at RAISE
1837 SELECT id, unit, comment FROM dbms_stats.backup_history
1841 ----+------+---------
1846 SELECT dbms_stats.backup_schema_stats('pg_catalog', 'comment');
1847 ERROR: backing up statistics is inhibited for system schemas: "pg_catalog"
1848 CONTEXT: PL/pgSQL function dbms_stats.backup_schema_stats(text,text) line 10 at RAISE
1849 SELECT id, unit, comment FROM dbms_stats.backup_history
1853 ----+------+---------
1858 * Delete stab function dbms_stats.backup
1860 DROP FUNCTION dbms_stats.backup(
1864 ALTER FUNCTION dbms_stats.truth_func_backup(
1869 DROP FUNCTION dbms_stats.backup(
1873 ALTER FUNCTION dbms_stats.truth_func_backup(
1880 * create backup statistics state A
1882 DELETE FROM dbms_stats.backup_history;
1883 INSERT INTO dbms_stats.backup_history(id, time, unit)
1884 VALUES (1, '2012-02-29 23:59:56.999999', 'd');
1885 SELECT setval('dbms_stats.backup_history_id_seq',1);
1891 SELECT dbms_stats.backup();
1897 UPDATE dbms_stats.backup_history
1898 SET time = '2012-02-29 23:59:57'
1900 SELECT dbms_stats.backup('s0.st0');
1906 UPDATE dbms_stats.backup_history
1907 SET time = '2012-02-29 23:59:57.000001'
1909 SELECT dbms_stats.backup();
1915 UPDATE dbms_stats.backup_history
1916 SET time = '2012-02-29 23:59:58'
1918 DELETE FROM dbms_stats.relation_stats_backup
1920 SELECT dbms_stats.backup('s0.st0', 'id');
1926 UPDATE dbms_stats.backup_history
1927 SET time = '2012-03-01 00:00:00'
1929 SELECT dbms_stats.backup('s0.st0');
1935 UPDATE dbms_stats.backup_history
1936 SET time = '2012-03-01 00:00:02'
1938 SELECT dbms_stats.backup('public.st0');
1944 UPDATE dbms_stats.backup_history
1945 SET time = '2012-03-01 00:00:04'
1947 INSERT INTO dbms_stats.backup_history(time, unit)
1948 VALUES ('2012-03-01 00:00:06', 's');
1949 SELECT dbms_stats.backup(8, c.oid, NULL)
1950 FROM pg_catalog.pg_class c,
1951 pg_catalog.pg_namespace n
1952 WHERE n.nspname = 's0'
1953 AND c.relnamespace = n.oid
1954 AND c.relkind IN ('r', 'i');
1965 SELECT * FROM dbms_stats.backup_history
1967 id | time | unit | comment
1968 ----+-------------------------------------+------+---------
1969 1 | Wed Feb 29 23:59:56.999999 2012 PST | d | (null)
1970 2 | Wed Feb 29 23:59:57 2012 PST | d | (null)
1971 3 | Wed Feb 29 23:59:57.000001 2012 PST | t | (null)
1972 4 | Wed Feb 29 23:59:58 2012 PST | d | (null)
1973 5 | Thu Mar 01 00:00:00 2012 PST | c | (null)
1974 6 | Thu Mar 01 00:00:02 2012 PST | t | (null)
1975 7 | Thu Mar 01 00:00:04 2012 PST | t | (null)
1976 8 | Thu Mar 01 00:00:06 2012 PST | s | (null)
1979 SELECT * FROM relations_backup_v;
1980 id | relid | relname | relpages | reltuples | relallvisible | curpages
1981 ----+------------+----------------+----------+-----------+---------------+----------
1982 2 | pt0 | public.pt0 | 0 | 0 | 0 | 0
1983 2 | pt0_idx | public.pt0_idx | 2 | 0 | 0 | 2
1984 2 | st0 | public.st0 | 1 | 2 | 1 | 1
1985 2 | st0_idx | public.st0_idx | 2 | 2 | 0 | 2
1986 2 | st1 | public.st1 | 45 | 10000 | 45 | 45
1987 2 | s0.st0 | s0.st0 | 1 | 2 | 1 | 1
1988 2 | s0.st0_idx | s0.st0_idx | 2 | 2 | 0 | 2
1989 2 | s0.st1 | s0.st1 | 1 | 3 | 1 | 1
1990 2 | s0.st1_idx | s0.st1_idx | 2 | 3 | 0 | 2
1991 2 | s0.st2 | s0.st2 | 1 | 3 | 1 | 1
1992 2 | s0.st2_idx | s0.st2_idx | 2 | 3 | 0 | 2
1993 2 | s1.st0 | s1.st0 | 1 | 4 | 1 | 1
1994 2 | st1_idx | public.st1_idx | 30 | 10000 | 0 | 30
1995 2 | st1_exp | public.st1_exp | 30 | 10000 | 0 | 30
1996 2 | s0.sft0 | s0.sft0 | 1 | 10 | 0 | 0
1997 2 | s0.smv0 | s0.smv0 | 1 | 2 | 1 | 1
1998 3 | s0.st0 | s0.st0 | 1 | 2 | 1 | 1
1999 5 | s0.st0 | s0.st0 | 1 | 2 | 1 | 1
2000 6 | s0.st0 | s0.st0 | 1 | 2 | 1 | 1
2001 7 | st0 | public.st0 | 1 | 2 | 1 | 1
2002 8 | s0.st0 | s0.st0 | 1 | 2 | 1 | 1
2003 8 | s0.st0_idx | s0.st0_idx | 2 | 2 | 0 | 2
2004 8 | s0.st1 | s0.st1 | 1 | 3 | 1 | 1
2005 8 | s0.st1_idx | s0.st1_idx | 2 | 3 | 0 | 2
2006 8 | s0.st2 | s0.st2 | 1 | 3 | 1 | 1
2007 8 | s0.st2_idx | s0.st2_idx | 2 | 3 | 0 | 2
2010 SELECT * FROM columns_backup_v;
2011 id | statypid | starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
2012 ----+----------+----------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+------------------------+-------------+-------------+-------------+-------------+------------------------+------------+------------+------------+------------
2013 2 | 23 | st0 | 1 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
2014 2 | 1042 | st0 | 2 | f | 0 | 6 | -0.5 | 1 | 3 | 0 | 0 | 0 | 1054 | 1058 | 0 | 0 | 0 | {1} | {1} | (null) | (null) | (null) | {"test "} | (null) | (null) | (null) | (null)
2015 2 | 23 | st1 | 1 | f | 0 | 4 | 3 | 1 | 3 | 0 | 0 | 0 | 96 | 97 | 0 | 0 | 0 | {0.3334,0.3333,0.3333} | {0.3332} | (null) | (null) | (null) | {1,0,2} | (null) | (null) | (null) | (null)
2016 2 | 25 | st1 | 2 | f | 0 | 2 | 3 | 1 | 3 | 0 | 0 | 0 | 98 | 664 | 0 | 0 | 0 | {0.3334,0.3333,0.3333} | {0.3332} | (null) | (null) | (null) | {1,0,2} | (null) | (null) | (null) | (null)
2017 2 | 23 | s0.st0 | 1 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
2018 2 | 23 | s0.st0 | 1 | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,4,5,6} | (null) | (null) | (null) | (null)
2019 2 | 23 | s0.st0 | 2 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20} | (null) | (null) | (null) | (null)
2020 2 | 23 | s0.st0 | 2 | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20,40,50,60} | (null) | (null) | (null) | (null)
2021 2 | 23 | s0.st1 | 1 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {4,5,6} | (null) | (null) | (null) | (null)
2022 2 | 23 | s0.st1 | 2 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {40,50,60} | (null) | (null) | (null) | (null)
2023 2 | 23 | s0.st2 | 1 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,3} | (null) | (null) | (null) | (null)
2024 2 | 25 | s0.st2 | 2 | f | 0 | 5 | -1 | 2 | 3 | 0 | 0 | 0 | 664 | 664 | 0 | 0 | 0 | (null) | {0.5} | (null) | (null) | (null) | {1,comment,test} | (null) | (null) | (null) | (null)
2025 2 | 23 | s1.st0 | 1 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,3,4} | (null) | (null) | (null) | (null)
2026 2 | 23 | s1.st0 | 2 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {15,25,35,45} | (null) | (null) | (null) | (null)
2027 2 | 25 | st1_exp | 1 | f | 0 | 5 | 3 | 1 | 3 | 0 | 0 | 0 | 98 | 664 | 0 | 0 | 0 | {0.3334,0.3333,0.3333} | {0.3332} | (null) | (null) | (null) | {1,0,2} | (null) | (null) | (null) | (null)
2028 2 | 23 | s0.sft0 | 1 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,3,4,5,6,7,8,9,10} | (null) | (null) | (null) | (null)
2029 2 | 23 | s0.smv0 | 1 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
2030 2 | 23 | s0.smv0 | 2 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20} | (null) | (null) | (null) | (null)
2031 2 | 25 | s0.smv0 | 3 | f | 0 | 3 | -1 | 2 | 3 | 0 | 0 | 0 | 664 | 664 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,test} | (null) | (null) | (null) | (null)
2032 3 | 23 | s0.st0 | 1 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
2033 3 | 23 | s0.st0 | 1 | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,4,5,6} | (null) | (null) | (null) | (null)
2034 3 | 23 | s0.st0 | 2 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20} | (null) | (null) | (null) | (null)
2035 3 | 23 | s0.st0 | 2 | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20,40,50,60} | (null) | (null) | (null) | (null)
2036 5 | 23 | s0.st0 | 1 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
2037 5 | 23 | s0.st0 | 1 | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,4,5,6} | (null) | (null) | (null) | (null)
2038 6 | 23 | s0.st0 | 1 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
2039 6 | 23 | s0.st0 | 1 | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,4,5,6} | (null) | (null) | (null) | (null)
2040 6 | 23 | s0.st0 | 2 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20} | (null) | (null) | (null) | (null)
2041 6 | 23 | s0.st0 | 2 | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20,40,50,60} | (null) | (null) | (null) | (null)
2042 7 | 23 | st0 | 1 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
2043 7 | 1042 | st0 | 2 | f | 0 | 6 | -0.5 | 1 | 3 | 0 | 0 | 0 | 1054 | 1058 | 0 | 0 | 0 | {1} | {1} | (null) | (null) | (null) | {"test "} | (null) | (null) | (null) | (null)
2044 8 | 23 | s0.st0 | 1 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
2045 8 | 23 | s0.st0 | 1 | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,4,5,6} | (null) | (null) | (null) | (null)
2046 8 | 23 | s0.st0 | 2 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20} | (null) | (null) | (null) | (null)
2047 8 | 23 | s0.st0 | 2 | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20,40,50,60} | (null) | (null) | (null) | (null)
2048 8 | 23 | s0.st1 | 1 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {4,5,6} | (null) | (null) | (null) | (null)
2049 8 | 23 | s0.st1 | 2 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {40,50,60} | (null) | (null) | (null) | (null)
2050 8 | 23 | s0.st2 | 1 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,3} | (null) | (null) | (null) | (null)
2051 8 | 25 | s0.st2 | 2 | f | 0 | 5 | -1 | 2 | 3 | 0 | 0 | 0 | 664 | 664 | 0 | 0 | 0 | (null) | {0.5} | (null) | (null) | (null) | {1,comment,test} | (null) | (null) | (null) | (null)
2056 * No.9-1 dbms_stats.restore
2059 DELETE FROM dbms_stats.relation_stats_locked;
2060 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
2062 SELECT * FROM internal_locks;
2067 SELECT dbms_stats.restore(2, 's0.st0', NULL);
2073 SELECT * FROM internal_locks;
2075 ----------------------------------+------------------
2076 dbms_stats.backup_history | RowShareLock
2077 dbms_stats.column_stats_backup | AccessShareLock
2078 dbms_stats.column_stats_locked | RowExclusiveLock
2079 dbms_stats.relation_stats_backup | AccessShareLock
2080 dbms_stats.relation_stats_backup | RowShareLock
2081 dbms_stats.relation_stats_locked | RowExclusiveLock
2082 dbms_stats.relation_stats_locked | RowShareLock
2086 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2094 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2095 starelid | attname | stainherit
2096 ----------+---------+------------
2104 DELETE FROM dbms_stats.relation_stats_locked;
2105 SELECT dbms_stats.restore(2, 'st0', NULL);
2111 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2119 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2120 starelid | attname | stainherit
2121 ----------+---------+------------
2127 DELETE FROM dbms_stats.relation_stats_locked;
2128 SELECT dbms_stats.restore(2, 's00.s0', NULL);
2129 ERROR: schema "s00" does not exist
2130 LINE 1: SELECT dbms_stats.restore(2, 's00.s0', NULL);
2132 SELECT count(*) FROM dbms_stats.column_stats_locked;
2138 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2145 DELETE FROM dbms_stats.relation_stats_locked;
2146 SELECT dbms_stats.restore(NULL, 's0.st0', NULL);
2147 ERROR: backup id required
2148 CONTEXT: PL/pgSQL function dbms_stats.restore(bigint,regclass,text) line 12 at RAISE
2149 SELECT count(*) FROM dbms_stats.column_stats_locked;
2155 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2162 DELETE FROM dbms_stats.relation_stats_locked;
2163 SELECT dbms_stats.restore(2, 's0.st0', 'id');
2169 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2177 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2178 starelid | attname | stainherit
2179 ----------+---------+------------
2185 DELETE FROM dbms_stats.relation_stats_locked;
2186 SELECT dbms_stats.restore(2, NULL, 'id');
2187 ERROR: relation required
2188 CONTEXT: PL/pgSQL function dbms_stats.restore(bigint,regclass,text) line 15 at RAISE
2189 SELECT count(*) FROM dbms_stats.column_stats_locked;
2195 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2202 DELETE FROM dbms_stats.relation_stats_locked;
2203 SELECT dbms_stats.restore(2, 's0.st0', NULL);
2209 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2217 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2218 starelid | attname | stainherit
2219 ----------+---------+------------
2227 DELETE FROM dbms_stats.relation_stats_locked;
2228 SELECT dbms_stats.restore(2, NULL, NULL);
2249 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2272 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2273 starelid | attname | stainherit
2274 ----------+---------+------------
2297 DELETE FROM dbms_stats.relation_stats_locked;
2298 SELECT dbms_stats.restore(0, 's0.st0', NULL);
2299 ERROR: backup id 0 not found
2300 CONTEXT: PL/pgSQL function dbms_stats.restore(bigint,regclass,text) line 19 at RAISE
2301 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2308 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2309 starelid | attname | stainherit
2310 ----------+---------+------------
2314 DELETE FROM dbms_stats.relation_stats_locked;
2315 SELECT dbms_stats.restore(2, 0, 'id');
2316 ERROR: relation "-" not found
2317 CONTEXT: PL/pgSQL function dbms_stats.restore(bigint,regclass,text) line 24 at RAISE
2318 SELECT count(*) FROM dbms_stats.column_stats_locked;
2324 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2331 DELETE FROM dbms_stats.relation_stats_locked;
2332 SELECT dbms_stats.restore(1, 's0.st0', NULL);
2333 ERROR: statistics of relation "s0.st0" not found in any backups before backup id = 1
2334 CONTEXT: PL/pgSQL function dbms_stats.restore(bigint,regclass,text) line 29 at RAISE
2335 SELECT count(*) FROM dbms_stats.column_stats_locked;
2341 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2348 DELETE FROM dbms_stats.relation_stats_locked;
2349 SELECT dbms_stats.restore(2, 's0.st0', 'dummy');
2350 ERROR: column "dummy" not found in relation s0.st0
2351 CONTEXT: PL/pgSQL function dbms_stats.restore(bigint,regclass,text) line 35 at RAISE
2352 SELECT count(*) FROM dbms_stats.column_stats_locked;
2358 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2365 DELETE FROM dbms_stats.relation_stats_locked;
2366 SELECT dbms_stats.restore(1, 's0.st0', 'id');
2367 ERROR: statistics of relation "s0.st0" not found in any backups before backup id = 1
2368 CONTEXT: PL/pgSQL function dbms_stats.restore(bigint,regclass,text) line 29 at RAISE
2369 SELECT count(*) FROM dbms_stats.column_stats_locked;
2375 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2382 DELETE FROM dbms_stats.relation_stats_locked;
2383 ALTER TABLE s1.st0 DROP COLUMN id;
2384 SELECT dbms_stats.restore(2, 's1.st0', 'id');
2385 ERROR: column "id" not found in relation s1.st0
2386 CONTEXT: PL/pgSQL function dbms_stats.restore(bigint,regclass,text) line 35 at RAISE
2387 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2394 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2395 starelid | attname | stainherit
2396 ----------+---------+------------
2400 DELETE FROM dbms_stats.relation_stats_locked;
2401 \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';"`
2403 -- SELECT dbms_stats.restore(2, :s1_st0_oid, NULL);
2404 -- To avoid test unstability caused by relation id alloction, the test
2405 -- above is omitted.
2406 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2412 SELECT count(*) FROM dbms_stats.column_stats_locked;
2418 CREATE TABLE s1.st0(id integer, num integer) WITH (autovacuum_enabled = 'false');
2419 INSERT INTO s1.st0 VALUES (1, 15), (2, 25), (3, 35), (4, 45);
2422 DELETE FROM dbms_stats.relation_stats_locked;
2423 SELECT dbms_stats.restore(2, 's0.st0', NULL);
2429 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2437 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2438 starelid | attname | stainherit
2439 ----------+---------+------------
2447 DELETE FROM dbms_stats.relation_stats_locked;
2448 INSERT INTO dbms_stats.relation_stats_backup(
2449 id, relid, relname, relpages, reltuples,
2453 'pg_toast.pg_toast_2618'::regclass,
2454 'pg_toast.pg_toast_2618', 1, 1,
2457 SELECT * FROM relations_backup_v
2459 AND relname = 'pg_toast.pg_toast_2618';
2460 id | relid | relname | relpages | reltuples | relallvisible | curpages
2461 ----+------------------------+------------------------+----------+-----------+---------------+----------
2462 2 | pg_toast.pg_toast_2618 | pg_toast.pg_toast_2618 | 1 | 1 | 1 | 1
2465 SELECT dbms_stats.restore(2, 'pg_toast.pg_toast_2618', NULL);
2470 SELECT count(*) FROM dbms_stats.column_stats_locked;
2476 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2482 DELETE FROM dbms_stats.relation_stats_backup
2484 AND relname = 'pg_toast.pg_toast_2618';
2486 DELETE FROM dbms_stats.relation_stats_locked;
2487 SELECT dbms_stats.restore(2, 's0.st0_idx', NULL);
2493 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
2501 SELECT starelid, attname, stainherit FROM columns_locked_v c;
2502 starelid | attname | stainherit
2503 ----------+---------+------------
2507 DELETE FROM dbms_stats.relation_stats_locked;
2508 INSERT INTO dbms_stats.relation_stats_backup(
2509 id, relid, relname, relpages, reltuples,
2512 VALUES (2, 's0.ss0'::regclass, 's0.ss0', 1, 1,
2515 SELECT * FROM relations_backup_v
2517 AND relname = 's0.ss0';
2518 id | relid | relname | relpages | reltuples | relallvisible | curpages
2519 ----+--------+---------+----------+-----------+---------------+----------
2520 2 | s0.ss0 | s0.ss0 | 1 | 1 | 1 | 1
2523 SELECT dbms_stats.restore(2, 's0.ss0', NULL);
2528 SELECT count(*) FROM dbms_stats.column_stats_locked;
2534 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2540 DELETE FROM dbms_stats.relation_stats_backup
2542 AND relname = 's0.ss0';
2544 DELETE FROM dbms_stats.relation_stats_locked;
2545 INSERT INTO dbms_stats.relation_stats_backup(
2546 id, relid, relname, relpages, reltuples,
2549 VALUES (2, 's0.sc0'::regclass, 's0.sc0', 1, 1,
2552 SELECT * FROM relations_backup_v
2554 AND relname = 's0.sc0';
2555 id | relid | relname | relpages | reltuples | relallvisible | curpages
2556 ----+--------+---------+----------+-----------+---------------+----------
2557 2 | s0.sc0 | s0.sc0 | 1 | 1 | 1 | 1
2560 SELECT dbms_stats.restore(2, 's0.sc0', NULL);
2565 SELECT count(*) FROM dbms_stats.column_stats_locked;
2571 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2577 DELETE FROM dbms_stats.relation_stats_backup
2579 AND relname = 's0.sc0';
2581 DELETE FROM dbms_stats.relation_stats_locked;
2582 INSERT INTO dbms_stats.relation_stats_backup(
2583 id, relid, relname, relpages, reltuples,
2586 VALUES (3, 's0.sft0'::regclass, 's0.sft0', 1, 1,
2589 SELECT * FROM relations_backup_v
2591 AND relname = 's0.sft0';
2592 id | relid | relname | relpages | reltuples | relallvisible | curpages
2593 ----+---------+---------+----------+-----------+---------------+----------
2594 3 | s0.sft0 | s0.sft0 | 1 | 1 | 1 | 1
2597 SELECT dbms_stats.restore(2, 's0.sft0', NULL);
2603 SELECT count(*) FROM dbms_stats.column_stats_locked;
2609 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2615 DELETE FROM dbms_stats.relation_stats_backup
2617 AND relname = 's0.sft0';
2619 DELETE FROM dbms_stats.relation_stats_locked;
2620 INSERT INTO dbms_stats.relation_stats_backup(
2621 id, relid, relname, relpages, reltuples,
2624 VALUES (3, 's0.smv0'::regclass, 's0.smv0', 1, 1,
2627 SELECT * FROM relations_backup_v
2629 AND relname = 's0.smv0';
2630 id | relid | relname | relpages | reltuples | relallvisible | curpages
2631 ----+---------+---------+----------+-----------+---------------+----------
2632 3 | s0.smv0 | s0.smv0 | 1 | 1 | 1 | 1
2635 SELECT dbms_stats.restore(2, 's0.smv0', NULL);
2641 SELECT count(*) FROM dbms_stats.column_stats_locked;
2647 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2653 DELETE FROM dbms_stats.relation_stats_backup
2655 AND relname = 's0.smv0';
2657 DELETE FROM dbms_stats.relation_stats_locked;
2658 INSERT INTO dbms_stats.relation_stats_backup(
2659 id, relid, relname, relpages, reltuples,
2662 VALUES (2, 'pg_catalog.pg_class'::regclass, 'pg_catalog.pg_class', 1, 1,
2665 SELECT * FROM relations_backup_v
2667 AND relname = 'pg_catalog.pg_class';
2668 id | relid | relname | relpages | reltuples | relallvisible | curpages
2669 ----+----------+---------------------+----------+-----------+---------------+----------
2670 2 | pg_class | pg_catalog.pg_class | 1 | 1 | 1 | 1
2673 SELECT dbms_stats.restore(2, 'pg_catalog.pg_class', NULL);
2678 SELECT count(*) FROM dbms_stats.column_stats_locked;
2684 SELECT count(*) FROM dbms_stats.relation_stats_locked;
2690 DELETE FROM dbms_stats.relation_stats_backup
2692 AND relname = 'pg_catalog.pg_class';
2694 DELETE FROM dbms_stats.relation_stats_locked;
2695 INSERT INTO dbms_stats.relation_stats_locked(relid, relname)
2696 VALUES ('s0.st0'::regclass, 's0.st0');
2697 INSERT INTO dbms_stats.column_stats_locked(starelid, staattnum, stainherit)
2698 SELECT starelid::regclass, staattnum, stainherit
2699 FROM dbms_stats.column_stats_effective
2700 WHERE starelid = 's0.st0'::regclass;
2701 SELECT id, unit, comment FROM dbms_stats.backup_history
2704 ----+------+---------
2708 SELECT * FROM columns_locked_v;
2709 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
2710 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------+------------+------------+------------+------------
2711 s0.st0 | 1 | id | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
2712 s0.st0 | 1 | id | t | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
2713 s0.st0 | 2 | num | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
2714 s0.st0 | 2 | num | t | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
2717 SELECT * FROM relations_locked_v;
2718 relid | relname | relpages | reltuples | relallvisible | curpages
2719 --------+---------+----------+-----------+---------------+----------
2720 s0.st0 | s0.st0 | (null) | (null) | (null) | (null)
2723 SELECT dbms_stats.restore(2, 's0.st0', NULL);
2729 SELECT * FROM relations_locked_v;
2730 relid | relname | relpages | reltuples | relallvisible | curpages
2731 --------+---------+----------+-----------+---------------+----------
2732 s0.st0 | s0.st0 | 1 | 2 | 1 | 1
2735 SELECT * FROM columns_locked_v;
2736 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
2737 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------------+------------+------------+------------+------------
2738 s0.st0 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
2739 s0.st0 | 1 | id | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,4,5,6} | (null) | (null) | (null) | (null)
2740 s0.st0 | 2 | num | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20} | (null) | (null) | (null) | (null)
2741 s0.st0 | 2 | num | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20,40,50,60} | (null) | (null) | (null) | (null)
2745 DELETE FROM dbms_stats.relation_stats_locked;
2746 SELECT id, unit, comment FROM dbms_stats.backup_history
2749 ----+------+---------
2753 SELECT dbms_stats.restore(2, 's0.st0', NULL);
2759 SELECT * FROM relations_locked_v;
2760 relid | relname | relpages | reltuples | relallvisible | curpages
2761 --------+---------+----------+-----------+---------------+----------
2762 s0.st0 | s0.st0 | 1 | 2 | 1 | 1
2765 SELECT * FROM columns_locked_v;
2766 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
2767 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------------+------------+------------+------------+------------
2768 s0.st0 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
2769 s0.st0 | 1 | id | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,4,5,6} | (null) | (null) | (null) | (null)
2770 s0.st0 | 2 | num | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20} | (null) | (null) | (null) | (null)
2771 s0.st0 | 2 | num | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20,40,50,60} | (null) | (null) | (null) | (null)
2775 * Stab function dbms_stats.restore
2777 CREATE OR REPLACE FUNCTION dbms_stats.restore(
2779 relid regclass DEFAULT NULL,
2780 attname text DEFAULT NULL)
2781 RETURNS SETOF regclass AS
2784 RAISE NOTICE 'arguments are "%, %, %"', $1, $2, $3;
2786 SELECT c.oid::regclass
2787 FROM pg_class c, dbms_stats.relation_stats_backup b
2788 WHERE (c.oid = $2 OR $2 IS NULL)
2790 AND c.relkind IN ('r', 'i')
2791 AND (b.id <= $1 OR $1 IS NOT NULL)
2793 ORDER BY c.oid::regclass::text;
2798 * No.10-1 dbms_stats.restore_database_stats
2801 SELECT dbms_stats.restore_database_stats('2012-02-29 23:59:57');
2802 NOTICE: arguments are "2, pt0, <NULL>"
2803 NOTICE: arguments are "2, pt0_idx, <NULL>"
2804 NOTICE: arguments are "2, st0, <NULL>"
2805 NOTICE: arguments are "2, st0_idx, <NULL>"
2806 NOTICE: arguments are "2, st1, <NULL>"
2807 NOTICE: arguments are "2, s0.st0, <NULL>"
2808 NOTICE: arguments are "2, s0.st0_idx, <NULL>"
2809 NOTICE: arguments are "2, s0.st1, <NULL>"
2810 NOTICE: arguments are "2, s0.st1_idx, <NULL>"
2811 NOTICE: arguments are "2, s0.st2, <NULL>"
2812 NOTICE: arguments are "2, s0.st2_idx, <NULL>"
2813 NOTICE: arguments are "2, st1_idx, <NULL>"
2814 NOTICE: arguments are "2, st1_exp, <NULL>"
2815 NOTICE: arguments are "2, s0.sft0, <NULL>"
2816 NOTICE: arguments are "2, s0.smv0, <NULL>"
2817 restore_database_stats
2818 ------------------------
2835 SELECT dbms_stats.restore_database_stats('2012-02-29 23:59:57.000002');
2836 NOTICE: arguments are "2, pt0, <NULL>"
2837 NOTICE: arguments are "2, pt0_idx, <NULL>"
2838 NOTICE: arguments are "2, st0, <NULL>"
2839 NOTICE: arguments are "2, st0_idx, <NULL>"
2840 NOTICE: arguments are "2, st1, <NULL>"
2841 NOTICE: arguments are "3, s0.st0, <NULL>"
2842 NOTICE: arguments are "2, s0.st0_idx, <NULL>"
2843 NOTICE: arguments are "2, s0.st1, <NULL>"
2844 NOTICE: arguments are "2, s0.st1_idx, <NULL>"
2845 NOTICE: arguments are "2, s0.st2, <NULL>"
2846 NOTICE: arguments are "2, s0.st2_idx, <NULL>"
2847 NOTICE: arguments are "2, st1_idx, <NULL>"
2848 NOTICE: arguments are "2, st1_exp, <NULL>"
2849 NOTICE: arguments are "2, s0.sft0, <NULL>"
2850 NOTICE: arguments are "2, s0.smv0, <NULL>"
2851 restore_database_stats
2852 ------------------------
2869 SELECT dbms_stats.restore_database_stats('2012-01-01 00:00:00');
2870 restore_database_stats
2871 ------------------------
2874 --#No.10-1-4 is skipped after lock tests
2875 --#No.10-1-5 is skipped after lock tests
2877 SELECT dbms_stats.restore_database_stats('2012-02-29 23:59:57');
2878 NOTICE: arguments are "2, pt0, <NULL>"
2879 NOTICE: arguments are "2, pt0_idx, <NULL>"
2880 NOTICE: arguments are "2, st0, <NULL>"
2881 NOTICE: arguments are "2, st0_idx, <NULL>"
2882 NOTICE: arguments are "2, st1, <NULL>"
2883 NOTICE: arguments are "2, s0.st0, <NULL>"
2884 NOTICE: arguments are "2, s0.st0_idx, <NULL>"
2885 NOTICE: arguments are "2, s0.st1, <NULL>"
2886 NOTICE: arguments are "2, s0.st1_idx, <NULL>"
2887 NOTICE: arguments are "2, s0.st2, <NULL>"
2888 NOTICE: arguments are "2, s0.st2_idx, <NULL>"
2889 NOTICE: arguments are "2, st1_idx, <NULL>"
2890 NOTICE: arguments are "2, st1_exp, <NULL>"
2891 NOTICE: arguments are "2, s0.sft0, <NULL>"
2892 NOTICE: arguments are "2, s0.smv0, <NULL>"
2893 restore_database_stats
2894 ------------------------
2911 * No.10-2 dbms_stats.restore_schema_stats
2914 SELECT dbms_stats.restore_schema_stats('s0', '2012-02-29 23:59:57');
2915 NOTICE: arguments are "2, s0.st0, <NULL>"
2916 NOTICE: arguments are "2, s0.st0_idx, <NULL>"
2917 NOTICE: arguments are "2, s0.st1, <NULL>"
2918 NOTICE: arguments are "2, s0.st1_idx, <NULL>"
2919 NOTICE: arguments are "2, s0.st2, <NULL>"
2920 NOTICE: arguments are "2, s0.st2_idx, <NULL>"
2921 NOTICE: arguments are "2, s0.sft0, <NULL>"
2922 NOTICE: arguments are "2, s0.smv0, <NULL>"
2923 restore_schema_stats
2924 ----------------------
2934 SELECT dbms_stats.restore_schema_stats('s0', '2012-02-29 23:59:57.000002');
2935 NOTICE: arguments are "3, s0.st0, <NULL>"
2936 NOTICE: arguments are "2, s0.st0_idx, <NULL>"
2937 NOTICE: arguments are "2, s0.st1, <NULL>"
2938 NOTICE: arguments are "2, s0.st1_idx, <NULL>"
2939 NOTICE: arguments are "2, s0.st2, <NULL>"
2940 NOTICE: arguments are "2, s0.st2_idx, <NULL>"
2941 NOTICE: arguments are "2, s0.sft0, <NULL>"
2942 NOTICE: arguments are "2, s0.smv0, <NULL>"
2943 restore_schema_stats
2944 ----------------------
2954 SELECT dbms_stats.restore_schema_stats('s0', '2012-01-01 00:00:00');
2955 restore_schema_stats
2956 ----------------------
2959 --#No.10-2-4 is skipped after lock tests
2960 --#No.10-2-5 is skipped after lock tests
2962 SELECT dbms_stats.restore_schema_stats('s0', '2012-02-29 23:59:57');
2963 NOTICE: arguments are "2, s0.st0, <NULL>"
2964 NOTICE: arguments are "2, s0.st0_idx, <NULL>"
2965 NOTICE: arguments are "2, s0.st1, <NULL>"
2966 NOTICE: arguments are "2, s0.st1_idx, <NULL>"
2967 NOTICE: arguments are "2, s0.st2, <NULL>"
2968 NOTICE: arguments are "2, s0.st2_idx, <NULL>"
2969 NOTICE: arguments are "2, s0.sft0, <NULL>"
2970 NOTICE: arguments are "2, s0.smv0, <NULL>"
2971 restore_schema_stats
2972 ----------------------
2982 SELECT dbms_stats.restore_schema_stats('s0', '2012-02-29 23:59:57');
2983 NOTICE: arguments are "2, s0.st0, <NULL>"
2984 NOTICE: arguments are "2, s0.st0_idx, <NULL>"
2985 NOTICE: arguments are "2, s0.st1, <NULL>"
2986 NOTICE: arguments are "2, s0.st1_idx, <NULL>"
2987 NOTICE: arguments are "2, s0.st2, <NULL>"
2988 NOTICE: arguments are "2, s0.st2_idx, <NULL>"
2989 NOTICE: arguments are "2, s0.sft0, <NULL>"
2990 NOTICE: arguments are "2, s0.smv0, <NULL>"
2991 restore_schema_stats
2992 ----------------------
3001 --#No.10-2-8 is skipped after lock tests
3003 SELECT dbms_stats.restore_schema_stats('s00', '2012-02-29 23:59:57');
3004 ERROR: schema "s00" not found
3005 CONTEXT: PL/pgSQL function dbms_stats.restore_schema_stats(text,timestamp with time zone) line 4 at RAISE
3007 SELECT dbms_stats.restore_schema_stats('pg_catalog', '2012-02-29 23:59:57');
3008 ERROR: restoring statistics is inhibited for system schemas: "pg_catalog"
3009 CONTEXT: PL/pgSQL function dbms_stats.restore_schema_stats(text,timestamp with time zone) line 7 at RAISE
3011 * No.10-7 dbms_stats.restore_stats
3014 DELETE FROM dbms_stats.relation_stats_locked;
3015 SELECT dbms_stats.restore_stats(NULL);
3021 DELETE FROM dbms_stats.relation_stats_locked;
3022 SELECT dbms_stats.restore_stats(0);
3023 ERROR: backup id 0 not found
3024 CONTEXT: PL/pgSQL function dbms_stats.restore_stats(bigint) line 10 at RAISE
3026 DELETE FROM dbms_stats.relation_stats_locked;
3027 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
3029 SELECT * FROM internal_locks;
3034 SELECT dbms_stats.restore_stats(2);
3054 SELECT * FROM internal_locks;
3056 ----------------------------------+------------------
3057 dbms_stats.backup_history | AccessShareLock
3058 dbms_stats.column_stats_backup | AccessShareLock
3059 dbms_stats.column_stats_locked | RowExclusiveLock
3060 dbms_stats.relation_stats_backup | AccessShareLock
3061 dbms_stats.relation_stats_backup | RowShareLock
3062 dbms_stats.relation_stats_locked | ExclusiveLock
3063 dbms_stats.relation_stats_locked | RowExclusiveLock
3064 dbms_stats.relation_stats_locked | RowShareLock
3068 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
3090 SELECT starelid, attname, stainherit FROM columns_locked_v c;
3091 starelid | attname | stainherit
3092 ----------+---------+------------
3113 DELETE FROM dbms_stats.relation_stats_locked;
3114 INSERT INTO dbms_stats.relation_stats_locked(relid, relname)
3115 SELECT relid::regclass, relname
3116 FROM dbms_stats.relation_stats_effective;
3117 INSERT INTO dbms_stats.column_stats_locked(starelid, staattnum, stainherit)
3118 SELECT starelid::regclass, staattnum, stainherit
3119 FROM dbms_stats.column_stats_effective;
3120 SELECT id, unit, comment FROM dbms_stats.backup_history
3123 ----+------+---------
3127 SELECT * FROM columns_locked_v;
3128 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
3129 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------+------------+------------+------------+------------
3130 st0 | 1 | id | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3131 st0 | 2 | name | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3132 st1 | 1 | val | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3133 st1 | 2 | str | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3134 s0.st0 | 1 | id | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3135 s0.st0 | 1 | id | t | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3136 s0.st0 | 2 | num | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3137 s0.st0 | 2 | num | t | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3138 s0.st1 | 1 | id | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3139 s0.st1 | 2 | num | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3140 s0.st2 | 1 | id | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3141 s0.st2 | 2 | txt | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3142 st1_exp | 1 | lower | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3143 s0.sft0 | 1 | id | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3144 s0.smv0 | 1 | id | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3145 s0.smv0 | 2 | num | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3146 s0.smv0 | 3 | txt | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3147 s1.st0 | 1 | id | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3148 s1.st0 | 2 | num | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3151 SELECT * FROM relations_locked_v;
3152 relid | relname | relpages | reltuples | relallvisible | curpages
3153 ------------+----------------+----------+-----------+---------------+----------
3154 pt0 | public.pt0 | (null) | (null) | (null) | (null)
3155 pt0_idx | public.pt0_idx | (null) | (null) | (null) | (null)
3156 st0 | public.st0 | (null) | (null) | (null) | (null)
3157 st0_idx | public.st0_idx | (null) | (null) | (null) | (null)
3158 st1 | public.st1 | (null) | (null) | (null) | (null)
3159 s0.st0 | s0.st0 | (null) | (null) | (null) | (null)
3160 s0.st0_idx | s0.st0_idx | (null) | (null) | (null) | (null)
3161 s0.st1 | s0.st1 | (null) | (null) | (null) | (null)
3162 s0.st1_idx | s0.st1_idx | (null) | (null) | (null) | (null)
3163 s0.st2 | s0.st2 | (null) | (null) | (null) | (null)
3164 s0.st2_idx | s0.st2_idx | (null) | (null) | (null) | (null)
3165 st1_idx | public.st1_idx | (null) | (null) | (null) | (null)
3166 st1_exp | public.st1_exp | (null) | (null) | (null) | (null)
3167 s0.sft0 | s0.sft0 | (null) | (null) | (null) | (null)
3168 s0.smv0 | s0.smv0 | (null) | (null) | (null) | (null)
3169 s1.st0 | s1.st0 | (null) | (null) | (null) | (null)
3172 SELECT dbms_stats.restore_stats(8);
3183 SELECT * FROM relations_locked_v;
3184 relid | relname | relpages | reltuples | relallvisible | curpages
3185 ------------+----------------+----------+-----------+---------------+----------
3186 pt0 | public.pt0 | (null) | (null) | (null) | (null)
3187 pt0_idx | public.pt0_idx | (null) | (null) | (null) | (null)
3188 st0 | public.st0 | (null) | (null) | (null) | (null)
3189 st0_idx | public.st0_idx | (null) | (null) | (null) | (null)
3190 st1 | public.st1 | (null) | (null) | (null) | (null)
3191 s0.st0 | s0.st0 | 1 | 2 | 1 | 1
3192 s0.st0_idx | s0.st0_idx | 2 | 2 | 0 | 2
3193 s0.st1 | s0.st1 | 1 | 3 | 1 | 1
3194 s0.st1_idx | s0.st1_idx | 2 | 3 | 0 | 2
3195 s0.st2 | s0.st2 | 1 | 3 | 1 | 1
3196 s0.st2_idx | s0.st2_idx | 2 | 3 | 0 | 2
3197 st1_idx | public.st1_idx | (null) | (null) | (null) | (null)
3198 st1_exp | public.st1_exp | (null) | (null) | (null) | (null)
3199 s0.sft0 | s0.sft0 | (null) | (null) | (null) | (null)
3200 s0.smv0 | s0.smv0 | (null) | (null) | (null) | (null)
3201 s1.st0 | s1.st0 | (null) | (null) | (null) | (null)
3204 SELECT * FROM columns_locked_v;
3205 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
3206 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------------+------------+------------+------------+------------
3207 st0 | 1 | id | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3208 st0 | 2 | name | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3209 st1 | 1 | val | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3210 st1 | 2 | str | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3211 s0.st0 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
3212 s0.st0 | 1 | id | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,4,5,6} | (null) | (null) | (null) | (null)
3213 s0.st0 | 2 | num | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20} | (null) | (null) | (null) | (null)
3214 s0.st0 | 2 | num | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20,40,50,60} | (null) | (null) | (null) | (null)
3215 s0.st1 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {4,5,6} | (null) | (null) | (null) | (null)
3216 s0.st1 | 2 | num | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {40,50,60} | (null) | (null) | (null) | (null)
3217 s0.st2 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,3} | (null) | (null) | (null) | (null)
3218 s0.st2 | 2 | txt | f | 0 | 5 | -1 | 2 | 3 | 0 | 0 | 0 | 664 | 664 | 0 | 0 | 0 | (null) | {0.5} | (null) | (null) | (null) | {1,comment,test} | (null) | (null) | (null) | (null)
3219 st1_exp | 1 | lower | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3220 s0.sft0 | 1 | id | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3221 s0.smv0 | 1 | id | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3222 s0.smv0 | 2 | num | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3223 s0.smv0 | 3 | txt | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3224 s1.st0 | 1 | id | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3225 s1.st0 | 2 | num | f | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
3229 DELETE FROM dbms_stats.relation_stats_locked;
3230 SELECT id, unit, comment FROM dbms_stats.backup_history
3233 ----+------+---------
3237 SELECT dbms_stats.restore_stats(8);
3248 SELECT * FROM relations_locked_v;
3249 relid | relname | relpages | reltuples | relallvisible | curpages
3250 ------------+------------+----------+-----------+---------------+----------
3251 s0.st0 | s0.st0 | 1 | 2 | 1 | 1
3252 s0.st0_idx | s0.st0_idx | 2 | 2 | 0 | 2
3253 s0.st1 | s0.st1 | 1 | 3 | 1 | 1
3254 s0.st1_idx | s0.st1_idx | 2 | 3 | 0 | 2
3255 s0.st2 | s0.st2 | 1 | 3 | 1 | 1
3256 s0.st2_idx | s0.st2_idx | 2 | 3 | 0 | 2
3259 SELECT * FROM columns_locked_v;
3260 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
3261 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------------+------------+------------+------------+------------
3262 s0.st0 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
3263 s0.st0 | 1 | id | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,4,5,6} | (null) | (null) | (null) | (null)
3264 s0.st0 | 2 | num | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20} | (null) | (null) | (null) | (null)
3265 s0.st0 | 2 | num | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20,40,50,60} | (null) | (null) | (null) | (null)
3266 s0.st1 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {4,5,6} | (null) | (null) | (null) | (null)
3267 s0.st1 | 2 | num | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {40,50,60} | (null) | (null) | (null) | (null)
3268 s0.st2 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,3} | (null) | (null) | (null) | (null)
3269 s0.st2 | 2 | txt | f | 0 | 5 | -1 | 2 | 3 | 0 | 0 | 0 | 664 | 664 | 0 | 0 | 0 | (null) | {0.5} | (null) | (null) | (null) | {1,comment,test} | (null) | (null) | (null) | (null)
3273 * No.11-1 dbms_stats.lock(relid, attname)
3276 DELETE FROM dbms_stats.relation_stats_locked;
3277 SELECT dbms_stats.lock(NULL, NULL);
3278 ERROR: relation required
3279 CONTEXT: PL/pgSQL function dbms_stats.lock(regclass,text) line 8 at RAISE
3281 ALTER FUNCTION dbms_stats.lock(relid regclass)
3282 RENAME TO truth_lock;
3283 CREATE FUNCTION dbms_stats.lock(relid regclass)
3287 RAISE NOTICE 'arguments are %', $1;
3292 DELETE FROM dbms_stats.relation_stats_locked;
3293 SELECT dbms_stats.lock('s0.st0', NULL);
3294 NOTICE: arguments are s0.st0
3300 DROP FUNCTION dbms_stats.lock(relid regclass);
3301 ALTER FUNCTION dbms_stats.truth_lock(relid regclass)
3304 DELETE FROM dbms_stats.relation_stats_locked;
3305 SELECT dbms_stats.lock(NULL, 'id');
3306 ERROR: relation required
3307 CONTEXT: PL/pgSQL function dbms_stats.lock(regclass,text) line 8 at RAISE
3309 DELETE FROM dbms_stats.relation_stats_locked;
3310 SELECT dbms_stats.lock('s0.st0', 'id');
3316 SELECT * FROM relations_locked_v;
3317 relid | relname | relpages | reltuples | relallvisible | curpages
3318 --------+---------+----------+-----------+---------------+----------
3319 s0.st0 | s0.st0 | (null) | (null) | (null) | (null)
3322 SELECT * FROM columns_locked_v c;
3323 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
3324 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+-------------+------------+------------+------------+------------
3325 s0.st0 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
3326 s0.st0 | 1 | id | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,4,5,6} | (null) | (null) | (null) | (null)
3330 DELETE FROM dbms_stats.relation_stats_locked;
3331 SELECT dbms_stats.lock(0, 'id');
3332 ERROR: relation "-" not found
3333 CONTEXT: PL/pgSQL function dbms_stats.lock(regclass,text) line 15 at RAISE
3335 DELETE FROM dbms_stats.relation_stats_locked;
3336 SELECT dbms_stats.lock('s0.st0', 'id');
3342 SELECT * FROM relations_locked_v;
3343 relid | relname | relpages | reltuples | relallvisible | curpages
3344 --------+---------+----------+-----------+---------------+----------
3345 s0.st0 | s0.st0 | (null) | (null) | (null) | (null)
3348 SELECT * FROM columns_locked_v c;
3349 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
3350 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+-------------+------------+------------+------------+------------
3351 s0.st0 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
3352 s0.st0 | 1 | id | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,4,5,6} | (null) | (null) | (null) | (null)
3356 DELETE FROM dbms_stats.relation_stats_locked;
3357 SELECT dbms_stats.lock('pg_toast.pg_toast_2618', 'id');
3358 ERROR: "pg_toast.pg_toast_2618" must be a table or an index
3359 CONTEXT: PL/pgSQL function dbms_stats.lock(regclass,text) line 18 at RAISE
3361 DELETE FROM dbms_stats.relation_stats_locked;
3362 SELECT dbms_stats.lock('s0.st0_idx', 'id');
3363 ERROR: "s0.st0_idx" must be an expression index
3364 CONTEXT: PL/pgSQL function dbms_stats.lock(regclass,text) line 21 at RAISE
3366 DELETE FROM dbms_stats.relation_stats_locked;
3367 SELECT dbms_stats.lock('st1_exp', 'lower');
3373 SELECT * FROM relations_locked_v;
3374 relid | relname | relpages | reltuples | relallvisible | curpages
3375 ---------+----------------+----------+-----------+---------------+----------
3376 st1_exp | public.st1_exp | (null) | (null) | (null) | (null)
3379 SELECT * FROM columns_locked_v c;
3380 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
3381 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+------------------------+-------------+-------------+-------------+-------------+------------+------------+------------+------------+------------
3382 st1_exp | 1 | lower | f | 0 | 5 | 3 | 1 | 3 | 0 | 0 | 0 | 98 | 664 | 0 | 0 | 0 | {0.3334,0.3333,0.3333} | {0.3332} | (null) | (null) | (null) | {1,0,2} | (null) | (null) | (null) | (null)
3385 DELETE FROM dbms_stats.relation_stats_locked;
3387 DELETE FROM dbms_stats.relation_stats_locked;
3388 SELECT dbms_stats.lock('s0.ss0', 'id');
3389 ERROR: "s0.ss0" must be a table or an index
3390 CONTEXT: PL/pgSQL function dbms_stats.lock(regclass,text) line 18 at RAISE
3392 DELETE FROM dbms_stats.relation_stats_locked;
3393 SELECT dbms_stats.lock('s0.sc0', 'id');
3394 ERROR: "s0.sc0" must be a table or an index
3395 CONTEXT: PL/pgSQL function dbms_stats.lock(regclass,text) line 18 at RAISE
3397 DELETE FROM dbms_stats.relation_stats_locked;
3398 SELECT dbms_stats.lock('s0.sft0', 'id');
3404 SELECT * FROM relations_locked_v;
3405 relid | relname | relpages | reltuples | relallvisible | curpages
3406 ---------+---------+----------+-----------+---------------+----------
3407 s0.sft0 | s0.sft0 | (null) | (null) | (null) | (null)
3410 SELECT * FROM columns_locked_v c;
3411 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
3412 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------------------+------------+------------+------------+------------
3413 s0.sft0 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,3,4,5,6,7,8,9,10} | (null) | (null) | (null) | (null)
3417 DELETE FROM dbms_stats.relation_stats_locked;
3418 SELECT dbms_stats.lock('s0.smv0', 'id');
3424 SELECT * FROM relations_locked_v;
3425 relid | relname | relpages | reltuples | relallvisible | curpages
3426 ---------+---------+----------+-----------+---------------+----------
3427 s0.smv0 | s0.smv0 | (null) | (null) | (null) | (null)
3430 SELECT * FROM columns_locked_v c;
3431 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
3432 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------+------------+------------+------------+------------
3433 s0.smv0 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
3437 DELETE FROM dbms_stats.relation_stats_locked;
3438 SELECT dbms_stats.lock('pg_catalog.pg_class', 'id');
3439 ERROR: locking statistics is inhibited for system catalogs: "pg_class"
3440 CONTEXT: PL/pgSQL function dbms_stats.lock(regclass,text) line 24 at RAISE
3442 DELETE FROM dbms_stats.relation_stats_locked;
3443 SELECT dbms_stats.lock('s0.st0', 'dummy');
3444 ERROR: column "dummy" not found in relation "s0.st0"
3445 CONTEXT: PL/pgSQL function dbms_stats.lock(regclass,text) line 29 at RAISE
3447 DELETE FROM dbms_stats.relation_stats_locked;
3448 DELETE FROM pg_statistic
3449 WHERE starelid = 's0.st0'::regclass;
3450 SELECT dbms_stats.lock('s0.st0', 'id');
3451 ERROR: no statistics available for column "id" of relation "s0.st0"
3452 CONTEXT: PL/pgSQL function dbms_stats.lock(regclass,text) line 120 at RAISE
3455 DELETE FROM dbms_stats.relation_stats_locked;
3456 INSERT INTO dbms_stats.relation_stats_locked(
3457 relid, relname, relpages, reltuples,
3460 VALUES('s0.st0'::regclass, 's0.st0', 1, 1640,
3463 SELECT dbms_stats.lock_column_stats('s0.st0','id');
3469 UPDATE dbms_stats.column_stats_locked
3470 SET (stanullfrac, stawidth, stadistinct,
3471 stakind1, stakind2, stakind3, stakind4,
3473 staop1, staop2, staop3, staop4,
3475 stanumbers1, stanumbers2, stanumbers3, stanumbers4,
3477 stavalues1, stavalues2, stavalues3, stavalues4
3481 NULL, NULL, NULL, NULL,
3482 NULL, NULL, NULL, NULL,
3483 NULL, NULL, NULL, NULL,
3484 NULL, NULL, NULL, NULL,
3485 NULL, NULL, NULL, NULL)
3486 WHERE starelid = 's0.st0'::regclass;
3487 SELECT dbms_stats.lock('s0.st0', 'id');
3493 SELECT * FROM relations_locked_v;
3494 relid | relname | relpages | reltuples | relallvisible | curpages
3495 --------+---------+----------+-----------+---------------+----------
3496 s0.st0 | s0.st0 | 1 | 1640 | 1 | 1
3499 SELECT * FROM columns_locked_v c;
3500 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
3501 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+-------------+------------+------------+------------+------------
3502 s0.st0 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
3503 s0.st0 | 1 | id | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,4,5,6} | (null) | (null) | (null) | (null)
3507 DELETE FROM dbms_stats.relation_stats_locked;
3508 SELECT dbms_stats.lock('s0.st0', 'id');
3514 SELECT * FROM relations_locked_v
3515 WHERE relid = 's0.st0'::regclass;
3516 relid | relname | relpages | reltuples | relallvisible | curpages
3517 --------+---------+----------+-----------+---------------+----------
3518 s0.st0 | s0.st0 | (null) | (null) | (null) | (null)
3521 SELECT starelid, attname, stainherit FROM columns_locked_v c;
3522 starelid | attname | stainherit
3523 ----------+---------+------------
3529 * No.11-2 dbms_stats.lock(relid)
3532 DELETE FROM dbms_stats.relation_stats_locked;
3533 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
3535 SELECT * FROM internal_locks;
3540 SELECT dbms_stats.lock('s0.st0');
3546 SELECT * FROM relations_locked_v;
3547 relid | relname | relpages | reltuples | relallvisible | curpages
3548 --------+---------+----------+-----------+---------------+----------
3549 s0.st0 | s0.st0 | 1 | 2 | 1 | 1
3552 SELECT * FROM columns_locked_v c;
3553 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
3554 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------------+------------+------------+------------+------------
3555 s0.st0 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
3556 s0.st0 | 1 | id | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,4,5,6} | (null) | (null) | (null) | (null)
3557 s0.st0 | 2 | num | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20} | (null) | (null) | (null) | (null)
3558 s0.st0 | 2 | num | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20,40,50,60} | (null) | (null) | (null) | (null)
3561 SELECT * FROM internal_locks;
3563 ----------------------------------+------------------
3564 dbms_stats.column_stats_locked | AccessShareLock
3565 dbms_stats.column_stats_locked | RowExclusiveLock
3566 dbms_stats.relation_stats_locked | AccessShareLock
3567 dbms_stats.relation_stats_locked | RowExclusiveLock
3568 dbms_stats.relation_stats_locked | RowShareLock
3573 DELETE FROM dbms_stats.relation_stats_locked;
3574 SELECT dbms_stats.lock(NULL);
3575 ERROR: relation required
3576 CONTEXT: PL/pgSQL function dbms_stats.lock(regclass) line 7 at RAISE
3578 DELETE FROM dbms_stats.relation_stats_locked;
3579 SELECT dbms_stats.lock('0');
3580 ERROR: relation "-" not found
3581 CONTEXT: PL/pgSQL function dbms_stats.lock(regclass) line 11 at RAISE
3583 DELETE FROM dbms_stats.relation_stats_locked;
3584 SELECT dbms_stats.lock('s0.st0');
3590 SELECT * FROM relations_locked_v;
3591 relid | relname | relpages | reltuples | relallvisible | curpages
3592 --------+---------+----------+-----------+---------------+----------
3593 s0.st0 | s0.st0 | 1 | 2 | 1 | 1
3596 SELECT * FROM columns_locked_v c;
3597 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
3598 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------------+------------+------------+------------+------------
3599 s0.st0 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
3600 s0.st0 | 1 | id | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,4,5,6} | (null) | (null) | (null) | (null)
3601 s0.st0 | 2 | num | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20} | (null) | (null) | (null) | (null)
3602 s0.st0 | 2 | num | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20,40,50,60} | (null) | (null) | (null) | (null)
3606 DELETE FROM dbms_stats.relation_stats_locked;
3607 SELECT dbms_stats.lock('pg_toast.pg_toast_2618');
3608 ERROR: locking statistics is not allowed for relations with relkind "t": "pg_toast.pg_toast_2618"
3609 HINT: Only tables(r, m, f) and indexes(i) are lockable.
3610 CONTEXT: PL/pgSQL function dbms_stats.lock(regclass) line 14 at RAISE
3612 DELETE FROM dbms_stats.relation_stats_locked;
3613 SELECT dbms_stats.lock('s0.st0_idx');
3619 SELECT * FROM relations_locked_v;
3620 relid | relname | relpages | reltuples | relallvisible | curpages
3621 ------------+------------+----------+-----------+---------------+----------
3622 s0.st0_idx | s0.st0_idx | 2 | 2 | 0 | 2
3625 SELECT * FROM columns_locked_v c;
3626 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
3627 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------+------------+------------+------------+------------
3631 DELETE FROM dbms_stats.relation_stats_locked;
3632 SELECT dbms_stats.lock('s0.ss0');
3633 ERROR: locking statistics is not allowed for relations with relkind "S": "s0.ss0"
3634 HINT: Only tables(r, m, f) and indexes(i) are lockable.
3635 CONTEXT: PL/pgSQL function dbms_stats.lock(regclass) line 14 at RAISE
3637 DELETE FROM dbms_stats.relation_stats_locked;
3638 SELECT dbms_stats.lock('s0.sc0');
3639 ERROR: locking statistics is not allowed for relations with relkind "c": "s0.sc0"
3640 HINT: Only tables(r, m, f) and indexes(i) are lockable.
3641 CONTEXT: PL/pgSQL function dbms_stats.lock(regclass) line 14 at RAISE
3643 DELETE FROM dbms_stats.relation_stats_locked;
3644 SELECT dbms_stats.lock('s0.sft0');
3650 SELECT * FROM relations_locked_v;
3651 relid | relname | relpages | reltuples | relallvisible | curpages
3652 ---------+---------+----------+-----------+---------------+----------
3653 s0.sft0 | s0.sft0 | 1 | 10 | 0 | 0
3656 SELECT * FROM columns_locked_v c;
3657 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
3658 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------------------+------------+------------+------------+------------
3659 s0.sft0 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,3,4,5,6,7,8,9,10} | (null) | (null) | (null) | (null)
3663 DELETE FROM dbms_stats.relation_stats_locked;
3664 SELECT dbms_stats.lock('s0.smv0');
3670 SELECT * FROM relations_locked_v;
3671 relid | relname | relpages | reltuples | relallvisible | curpages
3672 ---------+---------+----------+-----------+---------------+----------
3673 s0.smv0 | s0.smv0 | 1 | 2 | 1 | 1
3676 SELECT * FROM columns_locked_v c;
3677 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
3678 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------+------------+------------+------------+------------
3679 s0.smv0 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
3680 s0.smv0 | 2 | num | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20} | (null) | (null) | (null) | (null)
3681 s0.smv0 | 3 | txt | f | 0 | 3 | -1 | 2 | 3 | 0 | 0 | 0 | 664 | 664 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,test} | (null) | (null) | (null) | (null)
3685 DELETE FROM dbms_stats.relation_stats_locked;
3686 SELECT dbms_stats.lock('pg_catalog.pg_class');
3687 ERROR: locking statistics is not allowed for system catalogs: "pg_class"
3688 CONTEXT: PL/pgSQL function dbms_stats.lock(regclass) line 18 at RAISE
3690 DELETE FROM dbms_stats.relation_stats_locked;
3691 SELECT dbms_stats.lock_table_stats('s0.st0');
3697 UPDATE dbms_stats.relation_stats_locked
3698 SET (relpages, reltuples,
3704 WHERE relid = 's0.st0'::regclass;
3705 SELECT dbms_stats.lock('s0.st0');
3711 SELECT * FROM relations_locked_v;
3712 relid | relname | relpages | reltuples | relallvisible | curpages
3713 --------+---------+----------+-----------+---------------+----------
3714 s0.st0 | s0.st0 | 1 | 2 | 1 | 1
3717 SELECT * FROM columns_locked_v c;
3718 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
3719 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------------+------------+------------+------------+------------
3720 s0.st0 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
3721 s0.st0 | 1 | id | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,4,5,6} | (null) | (null) | (null) | (null)
3722 s0.st0 | 2 | num | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20} | (null) | (null) | (null) | (null)
3723 s0.st0 | 2 | num | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20,40,50,60} | (null) | (null) | (null) | (null)
3727 DELETE FROM dbms_stats.relation_stats_locked;
3728 SELECT dbms_stats.lock('s0.st0');
3734 SELECT * FROM relations_locked_v;
3735 relid | relname | relpages | reltuples | relallvisible | curpages
3736 --------+---------+----------+-----------+---------------+----------
3737 s0.st0 | s0.st0 | 1 | 2 | 1 | 1
3740 SELECT * FROM columns_locked_v c;
3741 starelid | staattnum | attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | stavalues4 | stavalues5
3742 ----------+-----------+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------------+------------+------------+------------+------------
3743 s0.st0 | 1 | id | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2} | (null) | (null) | (null) | (null)
3744 s0.st0 | 1 | id | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {1,2,4,5,6} | (null) | (null) | (null) | (null)
3745 s0.st0 | 2 | num | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20} | (null) | (null) | (null) | (null)
3746 s0.st0 | 2 | num | t | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | (null) | {1} | (null) | (null) | (null) | {10,20,40,50,60} | (null) | (null) | (null) | (null)
3750 * Stab function dbms_stats.lock
3752 ALTER FUNCTION dbms_stats.lock(relid regclass)
3753 RENAME TO truth_lock;
3754 CREATE FUNCTION dbms_stats.lock(relid regclass)
3758 RAISE NOTICE 'arguments are %', $1;
3763 ALTER FUNCTION dbms_stats.lock(relid regclass, attname text)
3764 RENAME TO truth_lock;
3765 CREATE FUNCTION dbms_stats.lock(
3771 RAISE NOTICE 'arguments are %, %', $1, $2;
3777 * No.12-1 dbms_stats.lock_database_stats
3780 SELECT dbms_stats.lock_database_stats();
3781 NOTICE: arguments are pt0
3782 NOTICE: arguments are pt0_idx
3783 NOTICE: arguments are st0
3784 NOTICE: arguments are st0_idx
3785 NOTICE: arguments are st1
3786 NOTICE: arguments are s0.st0
3787 NOTICE: arguments are s0.st0_idx
3788 NOTICE: arguments are s0.st1
3789 NOTICE: arguments are s0.st1_idx
3790 NOTICE: arguments are s0.st2
3791 NOTICE: arguments are s0.st2_idx
3792 NOTICE: arguments are st1_idx
3793 NOTICE: arguments are st1_exp
3794 NOTICE: arguments are s0.sft0
3795 NOTICE: arguments are s0.smv0
3796 NOTICE: arguments are s1.st0
3798 ---------------------
3818 * No.12-2 dbms_stats.lock_schema_stats
3821 SELECT dbms_stats.lock_schema_stats('s0');
3822 NOTICE: arguments are s0.st0
3823 NOTICE: arguments are s0.st0_idx
3824 NOTICE: arguments are s0.st1
3825 NOTICE: arguments are s0.st1_idx
3826 NOTICE: arguments are s0.st2
3827 NOTICE: arguments are s0.st2_idx
3828 NOTICE: arguments are s0.sft0
3829 NOTICE: arguments are s0.smv0
3843 SELECT dbms_stats.lock_schema_stats('s00');
3844 ERROR: schema "s00" not found
3845 CONTEXT: PL/pgSQL function dbms_stats.lock_schema_stats(text) line 4 at RAISE
3847 SELECT dbms_stats.lock_schema_stats('pg_catalog');
3848 ERROR: locking statistics is not allowed for system schemas: "pg_catalog"
3849 CONTEXT: PL/pgSQL function dbms_stats.lock_schema_stats(text) line 7 at RAISE
3851 * No.12-3 dbms_stats.lock_table_stats(regclass)
3854 SELECT dbms_stats.lock_table_stats('s0.st0');
3855 NOTICE: arguments are s0.st0
3862 SELECT dbms_stats.lock_table_stats('st0');
3863 NOTICE: arguments are st0
3870 SELECT dbms_stats.lock_table_stats('s00.s0');
3871 ERROR: schema "s00" does not exist
3872 LINE 1: SELECT dbms_stats.lock_table_stats('s00.s0');
3875 * No.12-4 dbms_stats.lock_table_stats(schemaname, tablename)
3878 SELECT dbms_stats.lock_table_stats('s0', 'st0');
3879 NOTICE: arguments are s0.st0
3886 * No.12-5 dbms_stats.lock_column_stats(regclass, attname)
3889 SELECT dbms_stats.lock_column_stats('s0.st0', 'id');
3890 NOTICE: arguments are s0.st0, id
3897 SELECT dbms_stats.lock_column_stats('st0', 'id');
3898 NOTICE: arguments are st0, id
3905 SELECT dbms_stats.lock_column_stats('s00.s0', 'id');
3906 ERROR: schema "s00" does not exist
3907 LINE 1: SELECT dbms_stats.lock_column_stats('s00.s0', 'id');
3910 * No.12-6 dbms_stats.lock_column_stats(schemaname, tablename, int2)
3913 SELECT dbms_stats.lock_column_stats('s0', 'st0', 'id');
3914 NOTICE: arguments are s0.st0, id
3921 * Delete Stab function lock
3923 DROP FUNCTION dbms_stats.lock(relid regclass);
3924 ALTER FUNCTION dbms_stats.truth_lock(relid regclass)
3926 DROP FUNCTION dbms_stats.lock(relid regclass, attname text);
3927 ALTER FUNCTION dbms_stats.truth_lock(relid regclass, attname text)
3930 * No.13-1 dbms_stats.unlock
3933 DELETE FROM dbms_stats.relation_stats_locked;
3934 SELECT dbms_stats.lock_database_stats();
3936 ---------------------
3955 SELECT * FROM dbms_stats.backup_history
3957 id | time | unit | comment
3958 ----+-------------------------------------+------+---------
3959 1 | Wed Feb 29 23:59:56.999999 2012 PST | d | (null)
3960 2 | Wed Feb 29 23:59:57 2012 PST | d | (null)
3961 3 | Wed Feb 29 23:59:57.000001 2012 PST | t | (null)
3962 4 | Wed Feb 29 23:59:58 2012 PST | d | (null)
3963 5 | Thu Mar 01 00:00:00 2012 PST | c | (null)
3964 6 | Thu Mar 01 00:00:02 2012 PST | t | (null)
3965 7 | Thu Mar 01 00:00:04 2012 PST | t | (null)
3966 8 | Thu Mar 01 00:00:06 2012 PST | s | (null)
3969 SELECT count(*) FROM dbms_stats.relation_stats_backup;
3975 SELECT count(*) FROM dbms_stats.column_stats_backup;
3981 SELECT dbms_stats.unlock();
4002 SELECT count(*) FROM dbms_stats.relation_stats_locked;
4008 SELECT count(*) FROM dbms_stats.column_stats_locked;
4014 SELECT * FROM dbms_stats.backup_history
4016 id | time | unit | comment
4017 ----+-------------------------------------+------+---------
4018 1 | Wed Feb 29 23:59:56.999999 2012 PST | d | (null)
4019 2 | Wed Feb 29 23:59:57 2012 PST | d | (null)
4020 3 | Wed Feb 29 23:59:57.000001 2012 PST | t | (null)
4021 4 | Wed Feb 29 23:59:58 2012 PST | d | (null)
4022 5 | Thu Mar 01 00:00:00 2012 PST | c | (null)
4023 6 | Thu Mar 01 00:00:02 2012 PST | t | (null)
4024 7 | Thu Mar 01 00:00:04 2012 PST | t | (null)
4025 8 | Thu Mar 01 00:00:06 2012 PST | s | (null)
4028 SELECT count(*) FROM dbms_stats.relation_stats_backup;
4034 SELECT count(*) FROM dbms_stats.column_stats_backup;
4041 DELETE FROM dbms_stats.relation_stats_locked;
4042 SELECT dbms_stats.lock_database_stats();
4044 ---------------------
4063 SELECT dbms_stats.unlock();
4084 SELECT count(*) FROM dbms_stats.relation_stats_locked;
4090 SELECT count(*) FROM dbms_stats.column_stats_locked;
4097 DELETE FROM dbms_stats.relation_stats_locked;
4098 SELECT dbms_stats.lock_database_stats();
4100 ---------------------
4119 DELETE FROM dbms_stats.column_stats_locked;
4120 SELECT dbms_stats.unlock();
4141 SELECT count(*) FROM dbms_stats.relation_stats_locked;
4147 SELECT count(*) FROM dbms_stats.column_stats_locked;
4154 DELETE FROM dbms_stats.relation_stats_locked;
4155 SELECT dbms_stats.unlock();
4160 SELECT count(*) FROM dbms_stats.relation_stats_locked;
4166 SELECT count(*) FROM dbms_stats.column_stats_locked;
4173 DELETE FROM dbms_stats.relation_stats_locked;
4174 SELECT dbms_stats.lock_database_stats();
4176 ---------------------
4195 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
4218 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
4234 SELECT dbms_stats.unlock('s0.st0');
4240 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
4262 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
4278 DELETE FROM dbms_stats.relation_stats_locked;
4279 SELECT dbms_stats.lock_database_stats();
4281 ---------------------
4300 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
4323 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
4339 SELECT dbms_stats.unlock('st0');
4345 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
4367 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
4383 DELETE FROM dbms_stats.relation_stats_locked;
4384 SELECT dbms_stats.lock_database_stats();
4386 ---------------------
4405 SELECT count(*) FROM dbms_stats.relation_stats_locked;
4411 SELECT count(*) FROM dbms_stats.column_stats_locked;
4417 SELECT dbms_stats.unlock('s00.s0');
4418 ERROR: schema "s00" does not exist
4419 LINE 1: SELECT dbms_stats.unlock('s00.s0');
4421 SELECT count(*) FROM dbms_stats.relation_stats_locked;
4427 SELECT count(*) FROM dbms_stats.column_stats_locked;
4434 DELETE FROM dbms_stats.relation_stats_locked;
4435 SELECT dbms_stats.lock_database_stats();
4437 ---------------------
4456 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
4479 SELECT starelid, attname, stainherit FROM columns_locked_v c;
4480 starelid | attname | stainherit
4481 ----------+---------+------------
4503 SELECT dbms_stats.unlock('s0.st0', 'id');
4509 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
4532 SELECT starelid, attname, stainherit FROM columns_locked_v c;
4533 starelid | attname | stainherit
4534 ----------+---------+------------
4555 DELETE FROM dbms_stats.relation_stats_locked;
4556 SELECT dbms_stats.lock_database_stats();
4558 ---------------------
4577 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
4600 SELECT starelid, attname, stainherit FROM columns_locked_v c;
4601 starelid | attname | stainherit
4602 ----------+---------+------------
4624 SELECT dbms_stats.unlock('s0.st0', 'dummy');
4625 ERROR: column "dummy" not found in relation "s0.st0"
4626 CONTEXT: PL/pgSQL function dbms_stats.unlock(regclass,text) line 19 at RAISE
4627 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
4650 SELECT starelid, attname, stainherit FROM columns_locked_v c;
4651 starelid | attname | stainherit
4652 ----------+---------+------------
4675 DELETE FROM dbms_stats.relation_stats_locked;
4676 SELECT dbms_stats.lock_database_stats();
4678 ---------------------
4697 DELETE FROM dbms_stats.column_stats_locked;
4698 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
4721 SELECT dbms_stats.unlock('s0.st0', 'id');
4727 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
4750 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_locked
4751 GROUP BY starelid, staattnum
4752 ORDER BY starelid, staattnum;
4753 starelid | staattnum
4754 ----------+-----------
4758 DELETE FROM dbms_stats.relation_stats_locked;
4759 SELECT dbms_stats.lock_database_stats();
4761 ---------------------
4780 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
4803 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_locked
4804 GROUP BY starelid, staattnum
4805 ORDER BY starelid, staattnum;
4806 starelid | staattnum
4807 ----------+-----------
4827 SELECT dbms_stats.unlock(NULL, 'id');
4828 ERROR: relation required
4829 CONTEXT: PL/pgSQL function dbms_stats.unlock(regclass,text) line 7 at RAISE
4830 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
4853 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_locked
4854 GROUP BY starelid, staattnum
4855 ORDER BY starelid, staattnum;
4856 starelid | staattnum
4857 ----------+-----------
4878 DELETE FROM dbms_stats.relation_stats_locked;
4879 SELECT dbms_stats.lock_database_stats();
4881 ---------------------
4900 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
4923 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_locked
4924 GROUP BY starelid, staattnum
4925 ORDER BY starelid, staattnum;
4926 starelid | staattnum
4927 ----------+-----------
4947 SELECT dbms_stats.unlock('s0.st0', NULL);
4953 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
4975 SELECT starelid::regclass, staattnum FROM dbms_stats.column_stats_locked
4976 GROUP BY starelid, staattnum
4977 ORDER BY starelid, staattnum;
4978 starelid | staattnum
4979 ----------+-----------
4998 DELETE FROM dbms_stats.relation_stats_locked;
4999 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
5000 SELECT dbms_stats.lock_database_stats();
5002 ---------------------
5021 SELECT count(*) FROM dbms_stats.relation_stats_locked;
5027 SELECT count(*) FROM dbms_stats.column_stats_locked;
5034 SELECT * FROM internal_locks;
5039 SELECT dbms_stats.unlock();
5060 SELECT * FROM internal_locks;
5062 ----------------------------------+------------------
5063 dbms_stats.column_stats_locked | RowExclusiveLock
5064 dbms_stats.relation_stats_locked | AccessShareLock
5065 dbms_stats.relation_stats_locked | RowExclusiveLock
5066 dbms_stats.relation_stats_locked | RowShareLock
5070 SELECT count(*) FROM dbms_stats.relation_stats_locked;
5076 SELECT count(*) FROM dbms_stats.column_stats_locked;
5083 * No.14-1 dbms_stats.unlock_database_stats
5086 DELETE FROM dbms_stats.relation_stats_locked;
5087 SELECT dbms_stats.lock_database_stats();
5089 ---------------------
5108 SELECT * FROM dbms_stats.backup_history
5110 id | time | unit | comment
5111 ----+-------------------------------------+------+---------
5112 1 | Wed Feb 29 23:59:56.999999 2012 PST | d | (null)
5113 2 | Wed Feb 29 23:59:57 2012 PST | d | (null)
5114 3 | Wed Feb 29 23:59:57.000001 2012 PST | t | (null)
5115 4 | Wed Feb 29 23:59:58 2012 PST | d | (null)
5116 5 | Thu Mar 01 00:00:00 2012 PST | c | (null)
5117 6 | Thu Mar 01 00:00:02 2012 PST | t | (null)
5118 7 | Thu Mar 01 00:00:04 2012 PST | t | (null)
5119 8 | Thu Mar 01 00:00:06 2012 PST | s | (null)
5122 SELECT count(*) FROM dbms_stats.relation_stats_backup;
5128 SELECT count(*) FROM dbms_stats.column_stats_backup;
5134 SELECT count(*) FROM dbms_stats.relation_stats_locked;
5140 SELECT count(*) FROM dbms_stats.column_stats_locked;
5146 SELECT dbms_stats.unlock_database_stats();
5147 unlock_database_stats
5148 -----------------------
5167 SELECT count(*) FROM dbms_stats.relation_stats_locked;
5173 SELECT count(*) FROM dbms_stats.column_stats_locked;
5179 SELECT * FROM dbms_stats.backup_history
5181 id | time | unit | comment
5182 ----+-------------------------------------+------+---------
5183 1 | Wed Feb 29 23:59:56.999999 2012 PST | d | (null)
5184 2 | Wed Feb 29 23:59:57 2012 PST | d | (null)
5185 3 | Wed Feb 29 23:59:57.000001 2012 PST | t | (null)
5186 4 | Wed Feb 29 23:59:58 2012 PST | d | (null)
5187 5 | Thu Mar 01 00:00:00 2012 PST | c | (null)
5188 6 | Thu Mar 01 00:00:02 2012 PST | t | (null)
5189 7 | Thu Mar 01 00:00:04 2012 PST | t | (null)
5190 8 | Thu Mar 01 00:00:06 2012 PST | s | (null)
5193 SELECT count(*) FROM dbms_stats.relation_stats_backup;
5199 SELECT count(*) FROM dbms_stats.column_stats_backup;
5206 DELETE FROM dbms_stats.relation_stats_locked;
5207 SELECT dbms_stats.lock_database_stats();
5209 ---------------------
5228 DELETE FROM dbms_stats.column_stats_locked;
5229 SELECT count(*) FROM dbms_stats.relation_stats_locked;
5235 SELECT dbms_stats.unlock_database_stats();
5236 unlock_database_stats
5237 -----------------------
5256 SELECT count(*) FROM dbms_stats.relation_stats_locked;
5262 SELECT count(*) FROM dbms_stats.column_stats_locked;
5269 DELETE FROM dbms_stats.relation_stats_locked;
5270 SELECT dbms_stats.unlock_database_stats();
5271 unlock_database_stats
5272 -----------------------
5275 SELECT count(*) FROM dbms_stats.relation_stats_locked;
5281 SELECT count(*) FROM dbms_stats.column_stats_locked;
5288 DELETE FROM dbms_stats.relation_stats_locked;
5289 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
5290 SELECT dbms_stats.lock_database_stats();
5292 ---------------------
5311 SELECT count(*) FROM dbms_stats.relation_stats_locked;
5317 SELECT count(*) FROM dbms_stats.column_stats_locked;
5324 SELECT * FROM internal_locks;
5329 SELECT dbms_stats.unlock_database_stats();
5330 unlock_database_stats
5331 -----------------------
5350 SELECT * FROM internal_locks;
5352 ----------------------------------+------------------
5353 dbms_stats.column_stats_locked | RowExclusiveLock
5354 dbms_stats.relation_stats_locked | AccessShareLock
5355 dbms_stats.relation_stats_locked | ExclusiveLock
5356 dbms_stats.relation_stats_locked | RowExclusiveLock
5360 SELECT count(*) FROM dbms_stats.relation_stats_locked;
5366 SELECT count(*) FROM dbms_stats.column_stats_locked;
5373 * No.14-2 dbms_stats.unlock_schema_stats
5376 DELETE FROM dbms_stats.relation_stats_locked;
5377 SELECT dbms_stats.lock_database_stats();
5379 ---------------------
5398 SELECT * FROM dbms_stats.backup_history
5400 id | time | unit | comment
5401 ----+-------------------------------------+------+---------
5402 1 | Wed Feb 29 23:59:56.999999 2012 PST | d | (null)
5403 2 | Wed Feb 29 23:59:57 2012 PST | d | (null)
5404 3 | Wed Feb 29 23:59:57.000001 2012 PST | t | (null)
5405 4 | Wed Feb 29 23:59:58 2012 PST | d | (null)
5406 5 | Thu Mar 01 00:00:00 2012 PST | c | (null)
5407 6 | Thu Mar 01 00:00:02 2012 PST | t | (null)
5408 7 | Thu Mar 01 00:00:04 2012 PST | t | (null)
5409 8 | Thu Mar 01 00:00:06 2012 PST | s | (null)
5412 SELECT count(*) FROM dbms_stats.relation_stats_backup;
5418 SELECT count(*) FROM dbms_stats.column_stats_backup;
5424 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
5447 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
5463 SELECT dbms_stats.unlock_schema_stats('s0');
5465 ---------------------
5476 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
5491 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
5502 SELECT * FROM dbms_stats.backup_history
5504 id | time | unit | comment
5505 ----+-------------------------------------+------+---------
5506 1 | Wed Feb 29 23:59:56.999999 2012 PST | d | (null)
5507 2 | Wed Feb 29 23:59:57 2012 PST | d | (null)
5508 3 | Wed Feb 29 23:59:57.000001 2012 PST | t | (null)
5509 4 | Wed Feb 29 23:59:58 2012 PST | d | (null)
5510 5 | Thu Mar 01 00:00:00 2012 PST | c | (null)
5511 6 | Thu Mar 01 00:00:02 2012 PST | t | (null)
5512 7 | Thu Mar 01 00:00:04 2012 PST | t | (null)
5513 8 | Thu Mar 01 00:00:06 2012 PST | s | (null)
5516 SELECT count(*) FROM dbms_stats.relation_stats_backup;
5522 SELECT count(*) FROM dbms_stats.column_stats_backup;
5529 DELETE FROM dbms_stats.relation_stats_locked;
5530 SELECT dbms_stats.lock_database_stats();
5532 ---------------------
5551 DELETE FROM dbms_stats.column_stats_locked;
5552 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
5575 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
5582 SELECT dbms_stats.unlock_schema_stats('s0');
5584 ---------------------
5595 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
5610 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
5618 DELETE FROM dbms_stats.relation_stats_locked;
5619 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
5626 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
5633 SELECT dbms_stats.unlock_schema_stats('s0');
5635 ---------------------
5638 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
5645 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
5653 DELETE FROM dbms_stats.relation_stats_locked;
5654 SELECT dbms_stats.lock_database_stats();
5656 ---------------------
5675 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
5698 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
5714 SELECT dbms_stats.unlock_schema_stats('s0');
5716 ---------------------
5727 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
5742 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
5754 DELETE FROM dbms_stats.relation_stats_locked;
5755 SELECT dbms_stats.lock_database_stats();
5757 ---------------------
5776 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
5799 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
5815 SELECT dbms_stats.unlock_schema_stats('s00');
5816 ERROR: schema "s00" not found
5817 CONTEXT: PL/pgSQL function dbms_stats.unlock_schema_stats(text) line 6 at RAISE
5818 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
5841 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
5858 DELETE FROM dbms_stats.relation_stats_locked;
5859 SELECT dbms_stats.lock_database_stats();
5861 ---------------------
5880 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
5903 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
5919 SELECT dbms_stats.unlock_schema_stats('pg_catalog');
5920 ERROR: unlocking statistics is not allowed for system schemas: "pg_catalog"
5921 CONTEXT: PL/pgSQL function dbms_stats.unlock_schema_stats(text) line 9 at RAISE
5922 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
5945 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
5962 DELETE FROM dbms_stats.relation_stats_locked;
5963 SELECT dbms_stats.lock_database_stats();
5965 ---------------------
5984 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6007 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6023 SELECT dbms_stats.unlock_schema_stats(NULL);
6025 ---------------------
6028 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6051 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6068 DELETE FROM dbms_stats.relation_stats_locked;
6069 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
6070 SELECT dbms_stats.lock_database_stats();
6072 ---------------------
6091 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6114 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6131 SELECT * FROM internal_locks;
6136 SELECT dbms_stats.unlock_schema_stats('s0');
6138 ---------------------
6149 SELECT * FROM internal_locks;
6151 ----------------------------------+------------------
6152 dbms_stats.column_stats_locked | RowExclusiveLock
6153 dbms_stats.relation_stats_locked | RowExclusiveLock
6154 dbms_stats.relation_stats_locked | RowShareLock
6158 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6173 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6185 * No.14-3 dbms_stats.unlock_table_stats(regclass)
6188 DELETE FROM dbms_stats.relation_stats_locked;
6189 SELECT dbms_stats.lock_database_stats();
6191 ---------------------
6210 SELECT * FROM dbms_stats.backup_history
6212 id | time | unit | comment
6213 ----+-------------------------------------+------+---------
6214 1 | Wed Feb 29 23:59:56.999999 2012 PST | d | (null)
6215 2 | Wed Feb 29 23:59:57 2012 PST | d | (null)
6216 3 | Wed Feb 29 23:59:57.000001 2012 PST | t | (null)
6217 4 | Wed Feb 29 23:59:58 2012 PST | d | (null)
6218 5 | Thu Mar 01 00:00:00 2012 PST | c | (null)
6219 6 | Thu Mar 01 00:00:02 2012 PST | t | (null)
6220 7 | Thu Mar 01 00:00:04 2012 PST | t | (null)
6221 8 | Thu Mar 01 00:00:06 2012 PST | s | (null)
6224 SELECT count(*) FROM dbms_stats.relation_stats_backup;
6230 SELECT count(*) FROM dbms_stats.column_stats_backup;
6236 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6259 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6275 SELECT dbms_stats.unlock_table_stats('s0.st0');
6277 --------------------
6281 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6303 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6318 SELECT * FROM dbms_stats.backup_history
6320 id | time | unit | comment
6321 ----+-------------------------------------+------+---------
6322 1 | Wed Feb 29 23:59:56.999999 2012 PST | d | (null)
6323 2 | Wed Feb 29 23:59:57 2012 PST | d | (null)
6324 3 | Wed Feb 29 23:59:57.000001 2012 PST | t | (null)
6325 4 | Wed Feb 29 23:59:58 2012 PST | d | (null)
6326 5 | Thu Mar 01 00:00:00 2012 PST | c | (null)
6327 6 | Thu Mar 01 00:00:02 2012 PST | t | (null)
6328 7 | Thu Mar 01 00:00:04 2012 PST | t | (null)
6329 8 | Thu Mar 01 00:00:06 2012 PST | s | (null)
6332 SELECT count(*) FROM dbms_stats.relation_stats_backup;
6338 SELECT count(*) FROM dbms_stats.column_stats_backup;
6345 DELETE FROM dbms_stats.relation_stats_locked;
6346 SELECT dbms_stats.lock_database_stats();
6348 ---------------------
6367 DELETE FROM dbms_stats.column_stats_locked;
6368 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6391 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6398 SELECT dbms_stats.unlock_table_stats('s0.st0');
6400 --------------------
6404 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6426 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6434 DELETE FROM dbms_stats.relation_stats_locked;
6435 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6442 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6449 SELECT dbms_stats.unlock_table_stats('s0.st0');
6451 --------------------
6454 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6461 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6469 DELETE FROM dbms_stats.relation_stats_locked;
6470 SELECT dbms_stats.lock_database_stats();
6472 ---------------------
6491 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6514 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6530 SELECT dbms_stats.unlock_table_stats('s0.st0');
6532 --------------------
6536 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6558 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6574 DELETE FROM dbms_stats.relation_stats_locked;
6575 SELECT dbms_stats.lock_database_stats();
6577 ---------------------
6596 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6619 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6635 SELECT dbms_stats.unlock_table_stats('st0');
6637 --------------------
6641 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6663 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6679 DELETE FROM dbms_stats.relation_stats_locked;
6680 SELECT dbms_stats.lock_database_stats();
6682 ---------------------
6701 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6724 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6740 SELECT dbms_stats.unlock_table_stats('s00.s0');
6741 ERROR: schema "s00" does not exist
6742 LINE 1: SELECT dbms_stats.unlock_table_stats('s00.s0');
6744 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6767 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6784 DELETE FROM dbms_stats.relation_stats_locked;
6785 SELECT dbms_stats.lock_database_stats();
6787 ---------------------
6806 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6829 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6845 SELECT dbms_stats.unlock_table_stats(NULL);
6847 --------------------
6850 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6873 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6890 DELETE FROM dbms_stats.relation_stats_locked;
6891 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
6892 SELECT dbms_stats.lock_database_stats();
6894 ---------------------
6913 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6936 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
6953 SELECT * FROM internal_locks;
6958 SELECT dbms_stats.unlock_table_stats('s0.st0');
6960 --------------------
6964 SELECT * FROM internal_locks;
6966 ----------------------------------+------------------
6967 dbms_stats.column_stats_locked | RowExclusiveLock
6968 dbms_stats.relation_stats_locked | RowExclusiveLock
6972 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
6994 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
7010 * No.14-4 dbms_stats.unlock_table_stats(schemaname, tablename)
7013 DELETE FROM dbms_stats.relation_stats_locked;
7014 SELECT dbms_stats.lock_database_stats();
7016 ---------------------
7035 SELECT * FROM dbms_stats.backup_history
7037 id | time | unit | comment
7038 ----+-------------------------------------+------+---------
7039 1 | Wed Feb 29 23:59:56.999999 2012 PST | d | (null)
7040 2 | Wed Feb 29 23:59:57 2012 PST | d | (null)
7041 3 | Wed Feb 29 23:59:57.000001 2012 PST | t | (null)
7042 4 | Wed Feb 29 23:59:58 2012 PST | d | (null)
7043 5 | Thu Mar 01 00:00:00 2012 PST | c | (null)
7044 6 | Thu Mar 01 00:00:02 2012 PST | t | (null)
7045 7 | Thu Mar 01 00:00:04 2012 PST | t | (null)
7046 8 | Thu Mar 01 00:00:06 2012 PST | s | (null)
7049 SELECT count(*) FROM dbms_stats.relation_stats_backup;
7055 SELECT count(*) FROM dbms_stats.column_stats_backup;
7061 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
7084 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
7100 SELECT dbms_stats.unlock_table_stats('s0','st0');
7102 --------------------
7106 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
7128 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
7143 SELECT * FROM dbms_stats.backup_history
7145 id | time | unit | comment
7146 ----+-------------------------------------+------+---------
7147 1 | Wed Feb 29 23:59:56.999999 2012 PST | d | (null)
7148 2 | Wed Feb 29 23:59:57 2012 PST | d | (null)
7149 3 | Wed Feb 29 23:59:57.000001 2012 PST | t | (null)
7150 4 | Wed Feb 29 23:59:58 2012 PST | d | (null)
7151 5 | Thu Mar 01 00:00:00 2012 PST | c | (null)
7152 6 | Thu Mar 01 00:00:02 2012 PST | t | (null)
7153 7 | Thu Mar 01 00:00:04 2012 PST | t | (null)
7154 8 | Thu Mar 01 00:00:06 2012 PST | s | (null)
7157 SELECT count(*) FROM dbms_stats.relation_stats_backup;
7163 SELECT count(*) FROM dbms_stats.column_stats_backup;
7170 DELETE FROM dbms_stats.relation_stats_locked;
7171 SELECT dbms_stats.lock_database_stats();
7173 ---------------------
7192 DELETE FROM dbms_stats.column_stats_locked;
7193 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
7216 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
7223 SELECT dbms_stats.unlock_table_stats('s0', 'st0');
7225 --------------------
7229 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
7251 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
7259 DELETE FROM dbms_stats.relation_stats_locked;
7260 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
7267 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
7274 SELECT dbms_stats.unlock_table_stats('s0', 'st0');
7276 --------------------
7279 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
7286 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
7294 DELETE FROM dbms_stats.relation_stats_locked;
7295 SELECT dbms_stats.lock_database_stats();
7297 ---------------------
7316 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
7339 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
7355 SELECT dbms_stats.unlock_table_stats('s0', 'st0');
7357 --------------------
7361 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
7383 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
7399 DELETE FROM dbms_stats.relation_stats_locked;
7400 SELECT dbms_stats.lock_database_stats();
7402 ---------------------
7421 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
7444 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
7460 SELECT dbms_stats.unlock_table_stats('s00', 's0');
7461 ERROR: schema "s00" does not exist
7462 CONTEXT: SQL function "unlock_table_stats" statement 1
7463 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
7486 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
7503 DELETE FROM dbms_stats.relation_stats_locked;
7504 SELECT dbms_stats.lock_database_stats();
7506 ---------------------
7525 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
7548 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
7564 SELECT dbms_stats.unlock_table_stats(NULL, 'st0');
7566 --------------------
7569 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
7592 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
7609 DELETE FROM dbms_stats.relation_stats_locked;
7610 SELECT dbms_stats.lock_database_stats();
7612 ---------------------
7631 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
7654 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
7670 SELECT dbms_stats.unlock_table_stats('s0', NULL);
7672 --------------------
7675 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
7698 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
7715 DELETE FROM dbms_stats.relation_stats_locked;
7716 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
7717 SELECT dbms_stats.lock_database_stats();
7719 ---------------------
7738 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
7761 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
7778 SELECT * FROM internal_locks;
7783 SELECT dbms_stats.unlock_table_stats('s0', 'st0');
7785 --------------------
7789 SELECT * FROM internal_locks;
7791 ----------------------------------+------------------
7792 dbms_stats.column_stats_locked | RowExclusiveLock
7793 dbms_stats.relation_stats_locked | RowExclusiveLock
7797 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
7819 SELECT starelid::regclass, count(*) FROM dbms_stats.column_stats_locked
7835 * No.14-5 dbms_stats.unlock_column_stats(regclass, attname)
7838 DELETE FROM dbms_stats.relation_stats_locked;
7839 SELECT dbms_stats.lock_database_stats();
7841 ---------------------
7860 SELECT * FROM dbms_stats.backup_history
7862 id | time | unit | comment
7863 ----+-------------------------------------+------+---------
7864 1 | Wed Feb 29 23:59:56.999999 2012 PST | d | (null)
7865 2 | Wed Feb 29 23:59:57 2012 PST | d | (null)
7866 3 | Wed Feb 29 23:59:57.000001 2012 PST | t | (null)
7867 4 | Wed Feb 29 23:59:58 2012 PST | d | (null)
7868 5 | Thu Mar 01 00:00:00 2012 PST | c | (null)
7869 6 | Thu Mar 01 00:00:02 2012 PST | t | (null)
7870 7 | Thu Mar 01 00:00:04 2012 PST | t | (null)
7871 8 | Thu Mar 01 00:00:06 2012 PST | s | (null)
7874 SELECT count(*) FROM dbms_stats.relation_stats_backup;
7880 SELECT count(*) FROM dbms_stats.column_stats_backup;
7886 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
7909 SELECT starelid, attname, stainherit FROM columns_locked_v c;
7910 starelid | attname | stainherit
7911 ----------+---------+------------
7933 SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
7935 ---------------------
7939 SELECT starelid, attname, stainherit FROM columns_locked_v c;
7940 starelid | attname | stainherit
7941 ----------+---------+------------
7961 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
7984 SELECT * FROM dbms_stats.backup_history
7986 id | time | unit | comment
7987 ----+-------------------------------------+------+---------
7988 1 | Wed Feb 29 23:59:56.999999 2012 PST | d | (null)
7989 2 | Wed Feb 29 23:59:57 2012 PST | d | (null)
7990 3 | Wed Feb 29 23:59:57.000001 2012 PST | t | (null)
7991 4 | Wed Feb 29 23:59:58 2012 PST | d | (null)
7992 5 | Thu Mar 01 00:00:00 2012 PST | c | (null)
7993 6 | Thu Mar 01 00:00:02 2012 PST | t | (null)
7994 7 | Thu Mar 01 00:00:04 2012 PST | t | (null)
7995 8 | Thu Mar 01 00:00:06 2012 PST | s | (null)
7998 SELECT count(*) FROM dbms_stats.relation_stats_backup;
8004 SELECT count(*) FROM dbms_stats.column_stats_backup;
8011 DELETE FROM dbms_stats.relation_stats_locked;
8012 SELECT dbms_stats.lock_database_stats();
8014 ---------------------
8033 DELETE FROM dbms_stats.column_stats_locked;
8034 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
8057 SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
8059 ---------------------
8063 SELECT count(*) FROM dbms_stats.column_stats_locked;
8069 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
8093 DELETE FROM dbms_stats.relation_stats_locked;
8094 SELECT dbms_stats.lock_database_stats();
8096 ---------------------
8115 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
8138 SELECT starelid, attname, stainherit FROM columns_locked_v c;
8139 starelid | attname | stainherit
8140 ----------+---------+------------
8162 SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
8164 ---------------------
8168 SELECT starelid, attname, stainherit FROM columns_locked_v c;
8169 starelid | attname | stainherit
8170 ----------+---------+------------
8190 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
8214 DELETE FROM dbms_stats.relation_stats_locked;
8215 SELECT dbms_stats.lock_database_stats();
8217 ---------------------
8236 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
8259 SELECT starelid, attname, stainherit FROM columns_locked_v c;
8260 starelid | attname | stainherit
8261 ----------+---------+------------
8283 SELECT dbms_stats.unlock_column_stats('st0', 'id');
8285 ---------------------
8289 SELECT starelid, attname, stainherit FROM columns_locked_v c;
8290 starelid | attname | stainherit
8291 ----------+---------+------------
8312 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
8336 DELETE FROM dbms_stats.relation_stats_locked;
8337 SELECT dbms_stats.lock_database_stats();
8339 ---------------------
8358 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
8381 SELECT starelid, attname, stainherit FROM columns_locked_v c;
8382 starelid | attname | stainherit
8383 ----------+---------+------------
8405 SELECT dbms_stats.unlock_column_stats('s0.st0', 'dummy');
8406 ERROR: column "dummy" not found in relation "s0.st0"
8407 CONTEXT: PL/pgSQL function dbms_stats.unlock_column_stats(regclass,text) line 8 at RAISE
8408 SELECT starelid, attname, stainherit FROM columns_locked_v c;
8409 starelid | attname | stainherit
8410 ----------+---------+------------
8432 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
8456 DELETE FROM dbms_stats.relation_stats_locked;
8457 SELECT dbms_stats.lock_database_stats();
8459 ---------------------
8478 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
8501 SELECT starelid, attname, stainherit FROM columns_locked_v c;
8502 starelid | attname | stainherit
8503 ----------+---------+------------
8525 SELECT dbms_stats.unlock_column_stats('s00.s0', 'id');
8526 ERROR: schema "s00" does not exist
8527 LINE 1: SELECT dbms_stats.unlock_column_stats('s00.s0', 'id');
8529 SELECT starelid, attname, stainherit FROM columns_locked_v c;
8530 starelid | attname | stainherit
8531 ----------+---------+------------
8553 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
8577 DELETE FROM dbms_stats.relation_stats_locked;
8578 SELECT dbms_stats.lock_database_stats();
8580 ---------------------
8599 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
8622 SELECT starelid, attname, stainherit FROM columns_locked_v c;
8623 starelid | attname | stainherit
8624 ----------+---------+------------
8646 SELECT dbms_stats.unlock_column_stats(NULL, 'id');
8648 ---------------------
8651 SELECT starelid, attname, stainherit FROM columns_locked_v c;
8652 starelid | attname | stainherit
8653 ----------+---------+------------
8675 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
8699 DELETE FROM dbms_stats.relation_stats_locked;
8700 SELECT dbms_stats.lock_database_stats();
8702 ---------------------
8721 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
8744 SELECT starelid, attname, stainherit FROM columns_locked_v c;
8745 starelid | attname | stainherit
8746 ----------+---------+------------
8768 SELECT dbms_stats.unlock_column_stats('s0.st0', NULL);
8770 ---------------------
8773 SELECT starelid, attname, stainherit FROM columns_locked_v c;
8774 starelid | attname | stainherit
8775 ----------+---------+------------
8797 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
8821 DELETE FROM dbms_stats.relation_stats_locked;
8822 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
8823 SELECT dbms_stats.lock_database_stats();
8825 ---------------------
8844 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
8867 SELECT starelid, attname, stainherit FROM columns_locked_v c;
8868 starelid | attname | stainherit
8869 ----------+---------+------------
8892 SELECT * FROM internal_locks;
8897 SELECT dbms_stats.unlock_column_stats('s0.st0', 'id');
8899 ---------------------
8903 SELECT * FROM internal_locks;
8905 ----------------------------------+------------------
8906 dbms_stats.column_stats_locked | RowExclusiveLock
8907 dbms_stats.relation_stats_locked | RowShareLock
8911 SELECT starelid, attname, stainherit FROM columns_locked_v c;
8912 starelid | attname | stainherit
8913 ----------+---------+------------
8933 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
8957 * No.14-6 dbms_stats.unlock_column_stats(schemaname, tablename, attname)
8960 DELETE FROM dbms_stats.relation_stats_locked;
8961 SELECT dbms_stats.lock_database_stats();
8963 ---------------------
8982 SELECT * FROM dbms_stats.backup_history
8984 id | time | unit | comment
8985 ----+-------------------------------------+------+---------
8986 1 | Wed Feb 29 23:59:56.999999 2012 PST | d | (null)
8987 2 | Wed Feb 29 23:59:57 2012 PST | d | (null)
8988 3 | Wed Feb 29 23:59:57.000001 2012 PST | t | (null)
8989 4 | Wed Feb 29 23:59:58 2012 PST | d | (null)
8990 5 | Thu Mar 01 00:00:00 2012 PST | c | (null)
8991 6 | Thu Mar 01 00:00:02 2012 PST | t | (null)
8992 7 | Thu Mar 01 00:00:04 2012 PST | t | (null)
8993 8 | Thu Mar 01 00:00:06 2012 PST | s | (null)
8996 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
9019 SELECT starelid, attname, stainherit FROM columns_locked_v c;
9020 starelid | attname | stainherit
9021 ----------+---------+------------
9043 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
9045 ---------------------
9049 SELECT starelid, attname, stainherit FROM columns_locked_v c;
9050 starelid | attname | stainherit
9051 ----------+---------+------------
9071 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
9094 SELECT * FROM dbms_stats.backup_history
9096 id | time | unit | comment
9097 ----+-------------------------------------+------+---------
9098 1 | Wed Feb 29 23:59:56.999999 2012 PST | d | (null)
9099 2 | Wed Feb 29 23:59:57 2012 PST | d | (null)
9100 3 | Wed Feb 29 23:59:57.000001 2012 PST | t | (null)
9101 4 | Wed Feb 29 23:59:58 2012 PST | d | (null)
9102 5 | Thu Mar 01 00:00:00 2012 PST | c | (null)
9103 6 | Thu Mar 01 00:00:02 2012 PST | t | (null)
9104 7 | Thu Mar 01 00:00:04 2012 PST | t | (null)
9105 8 | Thu Mar 01 00:00:06 2012 PST | s | (null)
9108 SELECT count(*) FROM dbms_stats.relation_stats_backup;
9114 SELECT count(*) FROM dbms_stats.column_stats_backup;
9121 DELETE FROM dbms_stats.relation_stats_locked;
9122 SELECT dbms_stats.lock_database_stats();
9124 ---------------------
9143 DELETE FROM dbms_stats.column_stats_locked;
9144 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
9167 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
9169 ---------------------
9173 SELECT starelid, attname, stainherit FROM columns_locked_v c;
9174 starelid | attname | stainherit
9175 ----------+---------+------------
9178 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
9202 DELETE FROM dbms_stats.relation_stats_locked;
9203 SELECT dbms_stats.lock_database_stats();
9205 ---------------------
9224 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
9247 SELECT starelid, attname, stainherit FROM columns_locked_v c;
9248 starelid | attname | stainherit
9249 ----------+---------+------------
9271 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
9273 ---------------------
9277 SELECT starelid, attname, stainherit FROM columns_locked_v c;
9278 starelid | attname | stainherit
9279 ----------+---------+------------
9299 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
9323 DELETE FROM dbms_stats.relation_stats_locked;
9324 SELECT dbms_stats.lock_database_stats();
9326 ---------------------
9345 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
9368 SELECT starelid, attname, stainherit FROM columns_locked_v c;
9369 starelid | attname | stainherit
9370 ----------+---------+------------
9392 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'dummy');
9393 ERROR: column "dummy" not found in relation "s0.st0"
9394 CONTEXT: PL/pgSQL function dbms_stats.unlock_column_stats(text,text,text) line 9 at RAISE
9395 SELECT starelid, attname, stainherit FROM columns_locked_v c;
9396 starelid | attname | stainherit
9397 ----------+---------+------------
9419 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
9443 DELETE FROM dbms_stats.relation_stats_locked;
9444 SELECT dbms_stats.lock_database_stats();
9446 ---------------------
9465 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
9488 SELECT starelid, attname, stainherit FROM columns_locked_v c;
9489 starelid | attname | stainherit
9490 ----------+---------+------------
9512 SELECT dbms_stats.unlock_column_stats(NULL, 'st0', 'id');
9514 ---------------------
9517 SELECT starelid, attname, stainherit FROM columns_locked_v c;
9518 starelid | attname | stainherit
9519 ----------+---------+------------
9541 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
9565 DELETE FROM dbms_stats.relation_stats_locked;
9566 SELECT dbms_stats.lock_database_stats();
9568 ---------------------
9587 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
9610 SELECT starelid, attname, stainherit FROM columns_locked_v c;
9611 starelid | attname | stainherit
9612 ----------+---------+------------
9634 SELECT dbms_stats.unlock_column_stats('s0', NULL, 'id');
9636 ---------------------
9639 SELECT starelid, attname, stainherit FROM columns_locked_v c;
9640 starelid | attname | stainherit
9641 ----------+---------+------------
9663 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
9687 DELETE FROM dbms_stats.relation_stats_locked;
9688 SELECT dbms_stats.lock_database_stats();
9690 ---------------------
9709 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
9732 SELECT starelid, attname, stainherit FROM columns_locked_v c;
9733 starelid | attname | stainherit
9734 ----------+---------+------------
9756 SELECT dbms_stats.unlock_column_stats('s0', 'st0', NULL);
9758 ---------------------
9761 SELECT starelid, attname, stainherit FROM columns_locked_v c;
9762 starelid | attname | stainherit
9763 ----------+---------+------------
9785 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
9809 DELETE FROM dbms_stats.relation_stats_locked;
9810 VACUUM dbms_stats.relation_stats_locked; -- in order to avoid auto vacuum
9811 SELECT dbms_stats.lock_database_stats();
9813 ---------------------
9832 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
9855 SELECT starelid, attname, stainherit FROM columns_locked_v c;
9856 starelid | attname | stainherit
9857 ----------+---------+------------
9880 SELECT * FROM internal_locks;
9885 SELECT dbms_stats.unlock_column_stats('s0', 'st0', 'id');
9887 ---------------------
9891 SELECT * FROM internal_locks;
9893 ----------------------------------+------------------
9894 dbms_stats.column_stats_locked | RowExclusiveLock
9895 dbms_stats.relation_stats_locked | RowShareLock
9899 SELECT starelid, attname, stainherit FROM columns_locked_v c;
9900 starelid | attname | stainherit
9901 ----------+---------+------------
9921 SELECT relid::regclass FROM dbms_stats.relation_stats_locked
9944 -- No.15 Make sure that the stats given by pg_dbms_stats doesn't
9945 -- ignored by ACL check
9946 CREATE FUNCTION testfunc() RETURNS TEXT AS $$
9952 FOR v IN EXPLAIN SELECT * FROM s0.st4 WHERE a < '000' LOOP
9953 -- mask unnecessary values
9954 v = regexp_replace(v, '(cost|width)=[0-9.]+', E'\\1=xxx', 'g');
9955 ret = ret || v || E'\n';
9959 $$ LANGUAGE plpgsql;
9960 SET pg_dbms_stats.use_locked_stats TO on;
9961 CREATE TABLE s0.st4 (a text) WITH (autovacuum_enabled = 'false');
9962 INSERT INTO s0.st4 SELECT '1' || md5(g::text) FROM generate_series(1, 10000) as g;
9963 VACUUM ANALYZE s0.st4;
9964 -- should estimate that rows = 1, not 5000
9967 ----------------------------------------------
9968 Seq Scan on st4 (cost=xxx rows=1 width=xxx)+
9969 Filter: (a < '000'::text) +
9973 SET pg_dbms_stats.use_locked_stats TO off;
9974 SELECT dbms_stats.lock_table_stats('s0.st4');
9980 SET pg_dbms_stats.use_locked_stats TO on;
9981 -- should estimate that rows = 1, not 5000
9984 ----------------------------------------------
9985 Seq Scan on st4 (cost=xxx rows=1 width=xxx)+
9986 Filter: (a < '000'::text) +
9991 SELECT dbms_stats.clean_up_stats();
9999 * No.15-2 Ditto for index stats
10001 CREATE TABLE s0.st4 (a double precision) WITH (autovacuum_enabled = 'false');
10002 CREATE INDEX on s0.st4 (floor(log(a)));
10003 SELECT dbms_stats.lock_table_stats('s0.st4');
10009 INSERT INTO s0.st4 (SELECT a from GENERATE_SERIES(1, 99999) a);
10011 ERROR: relation "t1" does not exist
10012 SET pg_dbms_stats.use_locked_stats TO off;
10015 --------------------------------------------------
10016 Seq Scan on st4 (cost=xxx rows=33373 width=xxx)+
10017 Filter: (a < '0'::double precision) +
10021 SET pg_dbms_stats.use_locked_stats TO on;
10024 ------------------------------------------------
10025 Seq Scan on st4 (cost=xxx rows=713 width=xxx)+
10026 Filter: (a < '0'::double precision) +
10031 DROP FUNCTION testfunc();
10032 SELECT dbms_stats.clean_up_stats();
10038 -- No.16 error description. -- abnormal case.
10039 RESET SESSION AUTHORIZATION;
10040 CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
10041 CREATE VIEW s0.vst4 AS select * FROM s0.st4;
10042 GRANT SELECT ON s0.vst4 TO regular_user;
10043 ALTER TABLE dbms_stats.relation_stats_locked OWNER TO regular_user;
10044 /* reconnection needed to flush cache */
10046 EXPLAIN (COSTS OFF) SELECT * FROM s0.vst4 WHERE a = 1;
10047 ERROR: permission denied for schema dbms_stats
10048 LINE 1: SELECT * FROM dbms_stats.column_stats_locked WHERE stareli...
10050 DETAIL: dbms_stats could not access the object as the role "regular_user".
10051 HINT: Check your settings of pg_dbms_stats.
10052 QUERY: SELECT * FROM dbms_stats.column_stats_locked WHERE starelid = $1 AND staattnum = $2 AND stainherit = $3
10054 ALTER TABLE dbms_stats.relation_stats_locked OWNER TO super_user;
10055 DROP TABLE s0.st4 CASCADE;
10056 NOTICE: drop cascades to view s0.vst4
10058 * No.20-1 confirm change at 1.3.5. Moved from ut-common.sql at 1.3.11
10060 SELECT CURRENT_USER;
10066 CREATE TABLE s0.st4 (a int, b text) WITH (autovacuum_enabled = 'false');
10067 CREATE INDEX i_st4_a on s0.st4 (a);
10068 CREATE VIEW s0.vst4 AS select * FROM s0.st4;
10069 GRANT SELECT ON s0.vst4 TO regular_user;
10070 INSERT INTO s0.st4 (SELECT a, a::text FROM generate_series(0, 999) a);
10072 SELECT dbms_stats.lock('s0.st4');
10078 DELETE FROM s0.st4;
10079 INSERT INTO s0.st4 (SELECT 1, a::text FROM generate_series(0, 999) a);
10081 EXPLAIN (COSTS OFF) SELECT * FROM s0.vst4 WHERE a = 1;
10083 ---------------------------------
10084 Index Scan using i_st4_a on st4
10085 Index Cond: (a = 1)
10088 EXPLAIN (COSTS OFF) SELECT * FROM s0.st4 WHERE a = 1;
10090 ---------------------------------
10091 Index Scan using i_st4_a on st4
10092 Index Cond: (a = 1)
10095 SET SESSION AUTHORIZATION regular_user;
10096 EXPLAIN (COSTS OFF) SELECT * FROM s0.st4 WHERE a = 1;
10097 ERROR: permission denied for table st4
10098 EXPLAIN (COSTS OFF) SELECT * FROM s0.vst4 WHERE a = 1;
10100 ---------------------------------
10101 Index Scan using i_st4_a on st4
10102 Index Cond: (a = 1)
10105 SET pg_dbms_stats.use_locked_stats TO off;
10106 EXPLAIN (COSTS OFF) SELECT * FROM s0.vst4 WHERE a = 1;
10108 -------------------
10114 ALTER TABLE dbms_stats.relation_stats_locked OWNER TO super_user;
10115 SELECT dbms_stats.unlock('s0.st4');
10121 DROP TABLE s0.st4 CASCADE;
10122 NOTICE: drop cascades to view s0.vst4