summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/vacuum.out
blob: c63a157e5f6c00309f555785ea35a3e16c8a7f83 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
--
-- VACUUM
--
CREATE TABLE vactst (i INT);
INSERT INTO vactst VALUES (1);
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst VALUES (0);
SELECT count(*) FROM vactst;
 count 
-------
  2049
(1 row)

DELETE FROM vactst WHERE i != 0;
SELECT * FROM vactst;
 i 
---
 0
(1 row)

VACUUM FULL vactst;
UPDATE vactst SET i = i + 1;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst VALUES (0);
SELECT count(*) FROM vactst;
 count 
-------
  2049
(1 row)

DELETE FROM vactst WHERE i != 0;
VACUUM (FULL) vactst;
DELETE FROM vactst;
SELECT * FROM vactst;
 i 
---
(0 rows)

VACUUM (FULL, FREEZE) vactst;
VACUUM (ANALYZE, FULL) vactst;
CREATE TABLE vaccluster (i INT PRIMARY KEY);
ALTER TABLE vaccluster CLUSTER ON vaccluster_pkey;
CLUSTER vaccluster;
CREATE FUNCTION do_analyze() RETURNS VOID VOLATILE LANGUAGE SQL
	AS 'ANALYZE pg_am';
CREATE FUNCTION wrap_do_analyze(c INT) RETURNS INT IMMUTABLE LANGUAGE SQL
	AS 'SELECT $1 FROM do_analyze()';
CREATE INDEX ON vaccluster(wrap_do_analyze(i));
INSERT INTO vaccluster VALUES (1), (2);
ANALYZE vaccluster;
ERROR:  ANALYZE cannot be executed from VACUUM or ANALYZE
CONTEXT:  SQL function "do_analyze" statement 1
SQL function "wrap_do_analyze" statement 1
-- Test ANALYZE in transaction, where the transaction surrounding
-- analyze performed modifications. This tests for the bug at
-- https://postgr.es/m/c7988239-d42c-ddc4-41db-171b23b35e4f%40ssinger.info
-- (which hopefully is unlikely to be reintroduced), but also seems
-- independently worthwhile to cover.
INSERT INTO vactst SELECT generate_series(1, 300);
DELETE FROM vactst WHERE i % 7 = 0; -- delete a few rows outside
BEGIN;
INSERT INTO vactst SELECT generate_series(301, 400);
DELETE FROM vactst WHERE i % 5 <> 0; -- delete a few rows inside
ANALYZE vactst;
COMMIT;
VACUUM FULL pg_am;
VACUUM FULL pg_class;
VACUUM FULL pg_database;
VACUUM FULL vaccluster;
ERROR:  ANALYZE cannot be executed from VACUUM or ANALYZE
CONTEXT:  SQL function "do_analyze" statement 1
SQL function "wrap_do_analyze" statement 1
VACUUM FULL vactst;
VACUUM (DISABLE_PAGE_SKIPPING) vaccluster;
-- PARALLEL option
CREATE TABLE pvactst (i INT, a INT[], p POINT) with (autovacuum_enabled = off);
INSERT INTO pvactst SELECT i, array[1,2,3], point(i, i+1) FROM generate_series(1,1000) i;
CREATE INDEX btree_pvactst ON pvactst USING btree (i);
CREATE INDEX hash_pvactst ON pvactst USING hash (i);
CREATE INDEX brin_pvactst ON pvactst USING brin (i);
CREATE INDEX gin_pvactst ON pvactst USING gin (a);
CREATE INDEX gist_pvactst ON pvactst USING gist (p);
CREATE INDEX spgist_pvactst ON pvactst USING spgist (p);
-- VACUUM invokes parallel index cleanup
SET min_parallel_index_scan_size to 0;
VACUUM (PARALLEL 2) pvactst;
-- VACUUM invokes parallel bulk-deletion
UPDATE pvactst SET i = i WHERE i < 1000;
VACUUM (PARALLEL 2) pvactst;
UPDATE pvactst SET i = i WHERE i < 1000;
VACUUM (PARALLEL 0) pvactst; -- disable parallel vacuum
VACUUM (PARALLEL -1) pvactst; -- error
ERROR:  parallel workers for vacuum must be between 0 and 1024
LINE 1: VACUUM (PARALLEL -1) pvactst;
                ^
VACUUM (PARALLEL 2, INDEX_CLEANUP FALSE) pvactst;
VACUUM (PARALLEL 2, FULL TRUE) pvactst; -- error, cannot use both PARALLEL and FULL
ERROR:  VACUUM FULL cannot be performed in parallel
VACUUM (PARALLEL) pvactst; -- error, cannot use PARALLEL option without parallel degree
ERROR:  parallel option requires a value between 0 and 1024
LINE 1: VACUUM (PARALLEL) pvactst;
                ^
-- Test different combinations of parallel and full options for temporary tables
CREATE TEMPORARY TABLE tmp (a int PRIMARY KEY);
CREATE INDEX tmp_idx1 ON tmp (a);
VACUUM (PARALLEL 1, FULL FALSE) tmp; -- parallel vacuum disabled for temp tables
WARNING:  disabling parallel option of vacuum on "tmp" --- cannot vacuum temporary tables in parallel
VACUUM (PARALLEL 0, FULL TRUE) tmp; -- can specify parallel disabled (even though that's implied by FULL)
RESET min_parallel_index_scan_size;
DROP TABLE pvactst;
-- INDEX_CLEANUP option
CREATE TABLE no_index_cleanup (i INT PRIMARY KEY, t TEXT);
-- Use uncompressed data stored in toast.
CREATE INDEX no_index_cleanup_idx ON no_index_cleanup(t);
ALTER TABLE no_index_cleanup ALTER COLUMN t SET STORAGE EXTERNAL;
INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(1,30),
    repeat('1234567890',269));
-- index cleanup option is ignored if VACUUM FULL
VACUUM (INDEX_CLEANUP TRUE, FULL TRUE) no_index_cleanup;
VACUUM (FULL TRUE) no_index_cleanup;
-- Toast inherits the value from its parent table.
ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = false);
DELETE FROM no_index_cleanup WHERE i < 15;
-- Nothing is cleaned up.
VACUUM no_index_cleanup;
-- Both parent relation and toast are cleaned up.
ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true);
VACUUM no_index_cleanup;
ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = auto);
VACUUM no_index_cleanup;
-- Parameter is set for both the parent table and its toast relation.
INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(31,60),
    repeat('1234567890',269));
DELETE FROM no_index_cleanup WHERE i < 45;
-- Only toast index is cleaned up.
ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = off,
    toast.vacuum_index_cleanup = yes);
VACUUM no_index_cleanup;
-- Only parent is cleaned up.
ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true,
    toast.vacuum_index_cleanup = false);
VACUUM no_index_cleanup;
-- Test some extra relations.
VACUUM (INDEX_CLEANUP FALSE) vaccluster;
VACUUM (INDEX_CLEANUP AUTO) vactst; -- index cleanup option is ignored if no indexes
VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster;
-- TRUNCATE option
CREATE TEMP TABLE vac_truncate_test(i INT NOT NULL, j text)
	WITH (vacuum_truncate=true, autovacuum_enabled=false);
INSERT INTO vac_truncate_test VALUES (1, NULL), (NULL, NULL);
ERROR:  null value in column "i" of relation "vac_truncate_test" violates not-null constraint
DETAIL:  Failing row contains (null, null).
VACUUM (TRUNCATE FALSE, DISABLE_PAGE_SKIPPING) vac_truncate_test;
SELECT pg_relation_size('vac_truncate_test') > 0;
 ?column? 
----------
 t
(1 row)

VACUUM (DISABLE_PAGE_SKIPPING) vac_truncate_test;
SELECT pg_relation_size('vac_truncate_test') = 0;
 ?column? 
----------
 t
(1 row)

VACUUM (TRUNCATE FALSE, FULL TRUE) vac_truncate_test;
DROP TABLE vac_truncate_test;
-- partitioned table
CREATE TABLE vacparted (a int, b char) PARTITION BY LIST (a);
CREATE TABLE vacparted1 PARTITION OF vacparted FOR VALUES IN (1);
INSERT INTO vacparted VALUES (1, 'a');
UPDATE vacparted SET b = 'b';
VACUUM (ANALYZE) vacparted;
VACUUM (FULL) vacparted;
VACUUM (FREEZE) vacparted;
-- check behavior with duplicate column mentions
VACUUM ANALYZE vacparted(a,b,a);
ERROR:  column "a" of relation "vacparted" appears more than once
ANALYZE vacparted(a,b,b);
ERROR:  column "b" of relation "vacparted" appears more than once
-- partitioned table with index
CREATE TABLE vacparted_i (a int primary key, b varchar(100))
  PARTITION BY HASH (a);
CREATE TABLE vacparted_i1 PARTITION OF vacparted_i
  FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE vacparted_i2 PARTITION OF vacparted_i
  FOR VALUES WITH (MODULUS 2, REMAINDER 1);
INSERT INTO vacparted_i SELECT i, 'test_'|| i from generate_series(1,10) i;
VACUUM (ANALYZE) vacparted_i;
VACUUM (FULL) vacparted_i;
VACUUM (FREEZE) vacparted_i;
SELECT relname, relhasindex FROM pg_class
  WHERE relname LIKE 'vacparted_i%' AND relkind IN ('p','r')
  ORDER BY relname;
   relname    | relhasindex 
--------------+-------------
 vacparted_i  | t
 vacparted_i1 | t
 vacparted_i2 | t
(3 rows)

DROP TABLE vacparted_i;
-- multiple tables specified
VACUUM vaccluster, vactst;
VACUUM vacparted, does_not_exist;
ERROR:  relation "does_not_exist" does not exist
VACUUM (FREEZE) vacparted, vaccluster, vactst;
VACUUM (FREEZE) does_not_exist, vaccluster;
ERROR:  relation "does_not_exist" does not exist
VACUUM ANALYZE vactst, vacparted (a);
VACUUM ANALYZE vactst (does_not_exist), vacparted (b);
ERROR:  column "does_not_exist" of relation "vactst" does not exist
VACUUM FULL vacparted, vactst;
VACUUM FULL vactst, vacparted (a, b), vaccluster (i);
ERROR:  ANALYZE option must be specified when a column list is provided
ANALYZE vactst, vacparted;
ANALYZE vacparted (b), vactst;
ANALYZE vactst, does_not_exist, vacparted;
ERROR:  relation "does_not_exist" does not exist
ANALYZE vactst (i), vacparted (does_not_exist);
ERROR:  column "does_not_exist" of relation "vacparted" does not exist
ANALYZE vactst, vactst;
BEGIN;  -- ANALYZE behaves differently inside a transaction block
ANALYZE vactst, vactst;
COMMIT;
-- parenthesized syntax for ANALYZE
ANALYZE (VERBOSE) does_not_exist;
ERROR:  relation "does_not_exist" does not exist
ANALYZE (nonexistent-arg) does_not_exist;
ERROR:  syntax error at or near "arg"
LINE 1: ANALYZE (nonexistent-arg) does_not_exist;
                             ^
ANALYZE (nonexistentarg) does_not_exit;
ERROR:  unrecognized ANALYZE option "nonexistentarg"
LINE 1: ANALYZE (nonexistentarg) does_not_exit;
                 ^
-- ensure argument order independence, and that SKIP_LOCKED on non-existing
-- relation still errors out.  Suppress WARNING messages caused by concurrent
-- autovacuums.
SET client_min_messages TO 'ERROR';
ANALYZE (SKIP_LOCKED, VERBOSE) does_not_exist;
ERROR:  relation "does_not_exist" does not exist
ANALYZE (VERBOSE, SKIP_LOCKED) does_not_exist;
ERROR:  relation "does_not_exist" does not exist
-- SKIP_LOCKED option
VACUUM (SKIP_LOCKED) vactst;
VACUUM (SKIP_LOCKED, FULL) vactst;
ANALYZE (SKIP_LOCKED) vactst;
RESET client_min_messages;
-- ensure VACUUM and ANALYZE don't have a problem with serializable
SET default_transaction_isolation = serializable;
VACUUM vactst;
ANALYZE vactst;
RESET default_transaction_isolation;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ANALYZE vactst;
COMMIT;
-- PROCESS_TOAST option
ALTER TABLE vactst ADD COLUMN t TEXT;
ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
VACUUM (PROCESS_TOAST FALSE) vactst;
VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
ERROR:  PROCESS_TOAST required with VACUUM FULL
DROP TABLE vaccluster;
DROP TABLE vactst;
DROP TABLE vacparted;
DROP TABLE no_index_cleanup;
-- relation ownership, WARNING logs generated as all are skipped.
CREATE TABLE vacowned (a int);
CREATE TABLE vacowned_parted (a int) PARTITION BY LIST (a);
CREATE TABLE vacowned_part1 PARTITION OF vacowned_parted FOR VALUES IN (1);
CREATE TABLE vacowned_part2 PARTITION OF vacowned_parted FOR VALUES IN (2);
CREATE ROLE regress_vacuum;
SET ROLE regress_vacuum;
-- Simple table
VACUUM vacowned;
WARNING:  skipping "vacowned" --- only table or database owner can vacuum it
ANALYZE vacowned;
WARNING:  skipping "vacowned" --- only table or database owner can analyze it
VACUUM (ANALYZE) vacowned;
WARNING:  skipping "vacowned" --- only table or database owner can vacuum it
-- Catalog
VACUUM pg_catalog.pg_class;
WARNING:  skipping "pg_class" --- only superuser or database owner can vacuum it
ANALYZE pg_catalog.pg_class;
WARNING:  skipping "pg_class" --- only superuser or database owner can analyze it
VACUUM (ANALYZE) pg_catalog.pg_class;
WARNING:  skipping "pg_class" --- only superuser or database owner can vacuum it
-- Shared catalog
VACUUM pg_catalog.pg_authid;
WARNING:  skipping "pg_authid" --- only superuser can vacuum it
ANALYZE pg_catalog.pg_authid;
WARNING:  skipping "pg_authid" --- only superuser can analyze it
VACUUM (ANALYZE) pg_catalog.pg_authid;
WARNING:  skipping "pg_authid" --- only superuser can vacuum it
-- Partitioned table and its partitions, nothing owned by other user.
-- Relations are not listed in a single command to test ownership
-- independently.
VACUUM vacowned_parted;
WARNING:  skipping "vacowned_parted" --- only table or database owner can vacuum it
WARNING:  skipping "vacowned_part1" --- only table or database owner can vacuum it
WARNING:  skipping "vacowned_part2" --- only table or database owner can vacuum it
VACUUM vacowned_part1;
WARNING:  skipping "vacowned_part1" --- only table or database owner can vacuum it
VACUUM vacowned_part2;
WARNING:  skipping "vacowned_part2" --- only table or database owner can vacuum it
ANALYZE vacowned_parted;
WARNING:  skipping "vacowned_parted" --- only table or database owner can analyze it
WARNING:  skipping "vacowned_part1" --- only table or database owner can analyze it
WARNING:  skipping "vacowned_part2" --- only table or database owner can analyze it
ANALYZE vacowned_part1;
WARNING:  skipping "vacowned_part1" --- only table or database owner can analyze it
ANALYZE vacowned_part2;
WARNING:  skipping "vacowned_part2" --- only table or database owner can analyze it
VACUUM (ANALYZE) vacowned_parted;
WARNING:  skipping "vacowned_parted" --- only table or database owner can vacuum it
WARNING:  skipping "vacowned_part1" --- only table or database owner can vacuum it
WARNING:  skipping "vacowned_part2" --- only table or database owner can vacuum it
VACUUM (ANALYZE) vacowned_part1;
WARNING:  skipping "vacowned_part1" --- only table or database owner can vacuum it
VACUUM (ANALYZE) vacowned_part2;
WARNING:  skipping "vacowned_part2" --- only table or database owner can vacuum it
RESET ROLE;
-- Partitioned table and one partition owned by other user.
ALTER TABLE vacowned_parted OWNER TO regress_vacuum;
ALTER TABLE vacowned_part1 OWNER TO regress_vacuum;
SET ROLE regress_vacuum;
VACUUM vacowned_parted;
WARNING:  skipping "vacowned_part2" --- only table or database owner can vacuum it
VACUUM vacowned_part1;
VACUUM vacowned_part2;
WARNING:  skipping "vacowned_part2" --- only table or database owner can vacuum it
ANALYZE vacowned_parted;
WARNING:  skipping "vacowned_part2" --- only table or database owner can analyze it
ANALYZE vacowned_part1;
ANALYZE vacowned_part2;
WARNING:  skipping "vacowned_part2" --- only table or database owner can analyze it
VACUUM (ANALYZE) vacowned_parted;
WARNING:  skipping "vacowned_part2" --- only table or database owner can vacuum it
VACUUM (ANALYZE) vacowned_part1;
VACUUM (ANALYZE) vacowned_part2;
WARNING:  skipping "vacowned_part2" --- only table or database owner can vacuum it
RESET ROLE;
-- Only one partition owned by other user.
ALTER TABLE vacowned_parted OWNER TO CURRENT_USER;
SET ROLE regress_vacuum;
VACUUM vacowned_parted;
WARNING:  skipping "vacowned_parted" --- only table or database owner can vacuum it
WARNING:  skipping "vacowned_part2" --- only table or database owner can vacuum it
VACUUM vacowned_part1;
VACUUM vacowned_part2;
WARNING:  skipping "vacowned_part2" --- only table or database owner can vacuum it
ANALYZE vacowned_parted;
WARNING:  skipping "vacowned_parted" --- only table or database owner can analyze it
WARNING:  skipping "vacowned_part2" --- only table or database owner can analyze it
ANALYZE vacowned_part1;
ANALYZE vacowned_part2;
WARNING:  skipping "vacowned_part2" --- only table or database owner can analyze it
VACUUM (ANALYZE) vacowned_parted;
WARNING:  skipping "vacowned_parted" --- only table or database owner can vacuum it
WARNING:  skipping "vacowned_part2" --- only table or database owner can vacuum it
VACUUM (ANALYZE) vacowned_part1;
VACUUM (ANALYZE) vacowned_part2;
WARNING:  skipping "vacowned_part2" --- only table or database owner can vacuum it
RESET ROLE;
-- Only partitioned table owned by other user.
ALTER TABLE vacowned_parted OWNER TO regress_vacuum;
ALTER TABLE vacowned_part1 OWNER TO CURRENT_USER;
SET ROLE regress_vacuum;
VACUUM vacowned_parted;
WARNING:  skipping "vacowned_part1" --- only table or database owner can vacuum it
WARNING:  skipping "vacowned_part2" --- only table or database owner can vacuum it
VACUUM vacowned_part1;
WARNING:  skipping "vacowned_part1" --- only table or database owner can vacuum it
VACUUM vacowned_part2;
WARNING:  skipping "vacowned_part2" --- only table or database owner can vacuum it
ANALYZE vacowned_parted;
WARNING:  skipping "vacowned_part1" --- only table or database owner can analyze it
WARNING:  skipping "vacowned_part2" --- only table or database owner can analyze it
ANALYZE vacowned_part1;
WARNING:  skipping "vacowned_part1" --- only table or database owner can analyze it
ANALYZE vacowned_part2;
WARNING:  skipping "vacowned_part2" --- only table or database owner can analyze it
VACUUM (ANALYZE) vacowned_parted;
WARNING:  skipping "vacowned_part1" --- only table or database owner can vacuum it
WARNING:  skipping "vacowned_part2" --- only table or database owner can vacuum it
VACUUM (ANALYZE) vacowned_part1;
WARNING:  skipping "vacowned_part1" --- only table or database owner can vacuum it
VACUUM (ANALYZE) vacowned_part2;
WARNING:  skipping "vacowned_part2" --- only table or database owner can vacuum it
RESET ROLE;
DROP TABLE vacowned;
DROP TABLE vacowned_parted;
DROP ROLE regress_vacuum;