| blob | 3e70e4c788e3ca19ef14a517ef31443684f669b1 |
1 --
2 -- VACUUM
3 --
4 CREATE TABLE vactst (i INT);
5 INSERT INTO vactst VALUES (1);
6 INSERT INTO vactst SELECT * FROM vactst;
7 INSERT INTO vactst SELECT * FROM vactst;
8 INSERT INTO vactst SELECT * FROM vactst;
9 INSERT INTO vactst SELECT * FROM vactst;
10 INSERT INTO vactst SELECT * FROM vactst;
11 INSERT INTO vactst SELECT * FROM vactst;
12 INSERT INTO vactst SELECT * FROM vactst;
13 INSERT INTO vactst SELECT * FROM vactst;
14 INSERT INTO vactst SELECT * FROM vactst;
15 INSERT INTO vactst SELECT * FROM vactst;
16 INSERT INTO vactst SELECT * FROM vactst;
17 INSERT INTO vactst VALUES (0);
18 SELECT count(*) FROM vactst;
19 count
20 -------
21 2049
22 (1 row)
24 DELETE FROM vactst WHERE i != 0;
25 SELECT * FROM vactst;
26 i
27 ---
28 0
29 (1 row)
31 VACUUM FULL vactst;
32 UPDATE vactst SET i = i + 1;
33 INSERT INTO vactst SELECT * FROM vactst;
34 INSERT INTO vactst SELECT * FROM vactst;
35 INSERT INTO vactst SELECT * FROM vactst;
36 INSERT INTO vactst SELECT * FROM vactst;
37 INSERT INTO vactst SELECT * FROM vactst;
38 INSERT INTO vactst SELECT * FROM vactst;
39 INSERT INTO vactst SELECT * FROM vactst;
40 INSERT INTO vactst SELECT * FROM vactst;
41 INSERT INTO vactst SELECT * FROM vactst;
42 INSERT INTO vactst SELECT * FROM vactst;
43 INSERT INTO vactst SELECT * FROM vactst;
44 INSERT INTO vactst VALUES (0);
45 SELECT count(*) FROM vactst;
46 count
47 -------
48 2049
49 (1 row)
51 DELETE FROM vactst WHERE i != 0;
52 VACUUM (FULL) vactst;
53 DELETE FROM vactst;
54 SELECT * FROM vactst;
55 i
56 ---
57 (0 rows)
59 VACUUM (FULL, FREEZE) vactst;
60 VACUUM (ANALYZE, FULL) vactst;
61 CREATE TABLE vaccluster (i INT PRIMARY KEY);
62 ALTER TABLE vaccluster CLUSTER ON vaccluster_pkey;
63 CLUSTER vaccluster;
64 CREATE FUNCTION do_analyze() RETURNS VOID VOLATILE LANGUAGE SQL
65 AS 'ANALYZE pg_am';
66 CREATE FUNCTION wrap_do_analyze(c INT) RETURNS INT IMMUTABLE LANGUAGE SQL
67 AS 'SELECT $1 FROM do_analyze()';
68 CREATE INDEX ON vaccluster(wrap_do_analyze(i));
69 INSERT INTO vaccluster VALUES (1), (2);
70 ANALYZE vaccluster;
71 ERROR: ANALYZE cannot be executed from VACUUM or ANALYZE
72 CONTEXT: SQL function "do_analyze" statement 1
73 SQL function "wrap_do_analyze" statement 1
74 -- Test ANALYZE in transaction, where the transaction surrounding
75 -- analyze performed modifications. This tests for the bug at
76 -- https://postgr.es/m/c7988239-d42c-ddc4-41db-171b23b35e4f%40ssinger.info
77 -- (which hopefully is unlikely to be reintroduced), but also seems
78 -- independently worthwhile to cover.
79 INSERT INTO vactst SELECT generate_series(1, 300);
80 DELETE FROM vactst WHERE i % 7 = 0; -- delete a few rows outside
81 BEGIN;
82 INSERT INTO vactst SELECT generate_series(301, 400);
83 DELETE FROM vactst WHERE i % 5 <> 0; -- delete a few rows inside
84 ANALYZE vactst;
85 COMMIT;
86 VACUUM FULL pg_am;
87 VACUUM FULL pg_class;
88 VACUUM FULL pg_database;
89 VACUUM FULL vaccluster;
90 ERROR: ANALYZE cannot be executed from VACUUM or ANALYZE
91 CONTEXT: SQL function "do_analyze" statement 1
92 SQL function "wrap_do_analyze" statement 1
93 VACUUM FULL vactst;
94 VACUUM (DISABLE_PAGE_SKIPPING) vaccluster;
95 -- PARALLEL option
96 CREATE TABLE pvactst (i INT, a INT[], p POINT) with (autovacuum_enabled = off);
97 INSERT INTO pvactst SELECT i, array[1,2,3], point(i, i+1) FROM generate_series(1,1000) i;
98 CREATE INDEX btree_pvactst ON pvactst USING btree (i);
99 CREATE INDEX hash_pvactst ON pvactst USING hash (i);
100 CREATE INDEX brin_pvactst ON pvactst USING brin (i);
101 CREATE INDEX gin_pvactst ON pvactst USING gin (a);
102 CREATE INDEX gist_pvactst ON pvactst USING gist (p);
103 CREATE INDEX spgist_pvactst ON pvactst USING spgist (p);
104 -- VACUUM invokes parallel index cleanup
105 SET min_parallel_index_scan_size to 0;
106 VACUUM (PARALLEL 2) pvactst;
107 -- VACUUM invokes parallel bulk-deletion
108 UPDATE pvactst SET i = i WHERE i < 1000;
109 VACUUM (PARALLEL 2) pvactst;
110 UPDATE pvactst SET i = i WHERE i < 1000;
111 VACUUM (PARALLEL 0) pvactst; -- disable parallel vacuum
112 VACUUM (PARALLEL -1) pvactst; -- error
113 ERROR: parallel workers for vacuum must be between 0 and 1024
114 LINE 1: VACUUM (PARALLEL -1) pvactst;
115 ^
116 VACUUM (PARALLEL 2, INDEX_CLEANUP FALSE) pvactst;
117 VACUUM (PARALLEL 2, FULL TRUE) pvactst; -- error, cannot use both PARALLEL and FULL
118 ERROR: VACUUM FULL cannot be performed in parallel
119 VACUUM (PARALLEL) pvactst; -- error, cannot use PARALLEL option without parallel degree
120 ERROR: parallel option requires a value between 0 and 1024
121 LINE 1: VACUUM (PARALLEL) pvactst;
122 ^
123 -- Test different combinations of parallel and full options for temporary tables
124 CREATE TEMPORARY TABLE tmp (a int PRIMARY KEY);
125 CREATE INDEX tmp_idx1 ON tmp (a);
126 VACUUM (PARALLEL 1, FULL FALSE) tmp; -- parallel vacuum disabled for temp tables
127 WARNING: disabling parallel option of vacuum on "tmp" --- cannot vacuum temporary tables in parallel
128 VACUUM (PARALLEL 0, FULL TRUE) tmp; -- can specify parallel disabled (even though that's implied by FULL)
129 RESET min_parallel_index_scan_size;
130 DROP TABLE pvactst;
131 -- INDEX_CLEANUP option
132 CREATE TABLE no_index_cleanup (i INT PRIMARY KEY, t TEXT);
133 -- Use uncompressed data stored in toast.
134 CREATE INDEX no_index_cleanup_idx ON no_index_cleanup(t);
135 ALTER TABLE no_index_cleanup ALTER COLUMN t SET STORAGE EXTERNAL;
136 INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(1,30),
137 repeat('1234567890',269));
138 -- index cleanup option is ignored if VACUUM FULL
139 VACUUM (INDEX_CLEANUP TRUE, FULL TRUE) no_index_cleanup;
140 VACUUM (FULL TRUE) no_index_cleanup;
141 -- Toast inherits the value from its parent table.
142 ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = false);
143 DELETE FROM no_index_cleanup WHERE i < 15;
144 -- Nothing is cleaned up.
145 VACUUM no_index_cleanup;
146 -- Both parent relation and toast are cleaned up.
147 ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true);
148 VACUUM no_index_cleanup;
149 ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = auto);
150 VACUUM no_index_cleanup;
151 -- Parameter is set for both the parent table and its toast relation.
152 INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(31,60),
153 repeat('1234567890',269));
154 DELETE FROM no_index_cleanup WHERE i < 45;
155 -- Only toast index is cleaned up.
156 ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = off,
157 toast.vacuum_index_cleanup = yes);
158 VACUUM no_index_cleanup;
159 -- Only parent is cleaned up.
160 ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true,
161 toast.vacuum_index_cleanup = false);
162 VACUUM no_index_cleanup;
163 -- Test some extra relations.
164 VACUUM (INDEX_CLEANUP FALSE) vaccluster;
165 VACUUM (INDEX_CLEANUP AUTO) vactst; -- index cleanup option is ignored if no indexes
166 VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster;
167 -- TRUNCATE option
168 CREATE TABLE vac_truncate_test(i INT NOT NULL, j text)
169 WITH (vacuum_truncate=true, autovacuum_enabled=false);
170 INSERT INTO vac_truncate_test VALUES (1, NULL), (NULL, NULL);
171 ERROR: null value in column "i" of relation "vac_truncate_test" violates not-null constraint
172 DETAIL: Failing row contains (null, null).
173 VACUUM (TRUNCATE FALSE) vac_truncate_test;
174 SELECT pg_relation_size('vac_truncate_test') > 0;
175 ?column?
176 ----------
177 t
178 (1 row)
180 VACUUM vac_truncate_test;
181 SELECT pg_relation_size('vac_truncate_test') = 0;
182 ?column?
183 ----------
184 t
185 (1 row)
187 VACUUM (TRUNCATE FALSE, FULL TRUE) vac_truncate_test;
188 DROP TABLE vac_truncate_test;
189 -- partitioned table
190 CREATE TABLE vacparted (a int, b char) PARTITION BY LIST (a);
191 CREATE TABLE vacparted1 PARTITION OF vacparted FOR VALUES IN (1);
192 INSERT INTO vacparted VALUES (1, 'a');
193 UPDATE vacparted SET b = 'b';
194 VACUUM (ANALYZE) vacparted;
195 VACUUM (FULL) vacparted;
196 VACUUM (FREEZE) vacparted;
197 -- check behavior with duplicate column mentions
198 VACUUM ANALYZE vacparted(a,b,a);
199 ERROR: column "a" of relation "vacparted" appears more than once
200 ANALYZE vacparted(a,b,b);
201 ERROR: column "b" of relation "vacparted" appears more than once
202 -- partitioned table with index
203 CREATE TABLE vacparted_i (a int primary key, b varchar(100))
204 PARTITION BY HASH (a);
205 CREATE TABLE vacparted_i1 PARTITION OF vacparted_i
206 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
207 CREATE TABLE vacparted_i2 PARTITION OF vacparted_i
208 FOR VALUES WITH (MODULUS 2, REMAINDER 1);
209 INSERT INTO vacparted_i SELECT i, 'test_'|| i from generate_series(1,10) i;
210 VACUUM (ANALYZE) vacparted_i;
211 VACUUM (FULL) vacparted_i;
212 VACUUM (FREEZE) vacparted_i;
213 SELECT relname, relhasindex FROM pg_class
214 WHERE relname LIKE 'vacparted_i%' AND relkind IN ('p','r')
215 ORDER BY relname;
216 relname | relhasindex
217 --------------+-------------
218 vacparted_i | t
219 vacparted_i1 | t
220 vacparted_i2 | t
221 (3 rows)
223 DROP TABLE vacparted_i;
224 -- multiple tables specified
225 VACUUM vaccluster, vactst;
226 VACUUM vacparted, does_not_exist;
227 ERROR: relation "does_not_exist" does not exist
228 VACUUM (FREEZE) vacparted, vaccluster, vactst;
229 VACUUM (FREEZE) does_not_exist, vaccluster;
230 ERROR: relation "does_not_exist" does not exist
231 VACUUM ANALYZE vactst, vacparted (a);
232 VACUUM ANALYZE vactst (does_not_exist), vacparted (b);
233 ERROR: column "does_not_exist" of relation "vactst" does not exist
234 VACUUM FULL vacparted, vactst;
235 VACUUM FULL vactst, vacparted (a, b), vaccluster (i);
236 ERROR: ANALYZE option must be specified when a column list is provided
237 ANALYZE vactst, vacparted;
238 ANALYZE vacparted (b), vactst;
239 ANALYZE vactst, does_not_exist, vacparted;
240 ERROR: relation "does_not_exist" does not exist
241 ANALYZE vactst (i), vacparted (does_not_exist);
242 ERROR: column "does_not_exist" of relation "vacparted" does not exist
243 ANALYZE vactst, vactst;
244 BEGIN; -- ANALYZE behaves differently inside a transaction block
245 ANALYZE vactst, vactst;
246 COMMIT;
247 -- parenthesized syntax for ANALYZE
248 ANALYZE (VERBOSE) does_not_exist;
249 ERROR: relation "does_not_exist" does not exist
250 ANALYZE (nonexistent-arg) does_not_exist;
251 ERROR: syntax error at or near "arg"
252 LINE 1: ANALYZE (nonexistent-arg) does_not_exist;
253 ^
254 ANALYZE (nonexistentarg) does_not_exit;
255 ERROR: unrecognized ANALYZE option "nonexistentarg"
256 LINE 1: ANALYZE (nonexistentarg) does_not_exit;
257 ^
258 -- ensure argument order independence, and that SKIP_LOCKED on non-existing
259 -- relation still errors out. Suppress WARNING messages caused by concurrent
260 -- autovacuums.
261 SET client_min_messages TO 'ERROR';
262 ANALYZE (SKIP_LOCKED, VERBOSE) does_not_exist;
263 ERROR: relation "does_not_exist" does not exist
264 ANALYZE (VERBOSE, SKIP_LOCKED) does_not_exist;
265 ERROR: relation "does_not_exist" does not exist
266 -- SKIP_LOCKED option
267 VACUUM (SKIP_LOCKED) vactst;
268 VACUUM (SKIP_LOCKED, FULL) vactst;
269 ANALYZE (SKIP_LOCKED) vactst;
270 RESET client_min_messages;
271 -- ensure VACUUM and ANALYZE don't have a problem with serializable
272 SET default_transaction_isolation = serializable;
273 VACUUM vactst;
274 ANALYZE vactst;
275 RESET default_transaction_isolation;
276 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
277 ANALYZE vactst;
278 COMMIT;
279 -- PROCESS_TOAST option
280 ALTER TABLE vactst ADD COLUMN t TEXT;
281 ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
282 VACUUM (PROCESS_TOAST FALSE) vactst;
283 VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
284 ERROR: PROCESS_TOAST required with VACUUM FULL
285 DROP TABLE vaccluster;
286 DROP TABLE vactst;
287 DROP TABLE vacparted;
288 DROP TABLE no_index_cleanup;
289 -- relation ownership, WARNING logs generated as all are skipped.
290 CREATE TABLE vacowned (a int);
291 CREATE TABLE vacowned_parted (a int) PARTITION BY LIST (a);
292 CREATE TABLE vacowned_part1 PARTITION OF vacowned_parted FOR VALUES IN (1);
293 CREATE TABLE vacowned_part2 PARTITION OF vacowned_parted FOR VALUES IN (2);
294 CREATE ROLE regress_vacuum;
295 SET ROLE regress_vacuum;
296 -- Simple table
297 VACUUM vacowned;
298 WARNING: skipping "vacowned" --- only table or database owner can vacuum it
299 ANALYZE vacowned;
300 WARNING: skipping "vacowned" --- only table or database owner can analyze it
301 VACUUM (ANALYZE) vacowned;
302 WARNING: skipping "vacowned" --- only table or database owner can vacuum it
303 -- Catalog
304 VACUUM pg_catalog.pg_class;
305 WARNING: skipping "pg_class" --- only superuser or database owner can vacuum it
306 ANALYZE pg_catalog.pg_class;
307 WARNING: skipping "pg_class" --- only superuser or database owner can analyze it
308 VACUUM (ANALYZE) pg_catalog.pg_class;
309 WARNING: skipping "pg_class" --- only superuser or database owner can vacuum it
310 -- Shared catalog
311 VACUUM pg_catalog.pg_authid;
312 WARNING: skipping "pg_authid" --- only superuser can vacuum it
313 ANALYZE pg_catalog.pg_authid;
314 WARNING: skipping "pg_authid" --- only superuser can analyze it
315 VACUUM (ANALYZE) pg_catalog.pg_authid;
316 WARNING: skipping "pg_authid" --- only superuser can vacuum it
317 -- Partitioned table and its partitions, nothing owned by other user.
318 -- Relations are not listed in a single command to test ownership
319 -- independently.
320 VACUUM vacowned_parted;
321 WARNING: skipping "vacowned_parted" --- only table or database owner can vacuum it
322 WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
323 WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
324 VACUUM vacowned_part1;
325 WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
326 VACUUM vacowned_part2;
327 WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
328 ANALYZE vacowned_parted;
329 WARNING: skipping "vacowned_parted" --- only table or database owner can analyze it
330 WARNING: skipping "vacowned_part1" --- only table or database owner can analyze it
331 WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
332 ANALYZE vacowned_part1;
333 WARNING: skipping "vacowned_part1" --- only table or database owner can analyze it
334 ANALYZE vacowned_part2;
335 WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
336 VACUUM (ANALYZE) vacowned_parted;
337 WARNING: skipping "vacowned_parted" --- only table or database owner can vacuum it
338 WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
339 WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
340 VACUUM (ANALYZE) vacowned_part1;
341 WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
342 VACUUM (ANALYZE) vacowned_part2;
343 WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
344 RESET ROLE;
345 -- Partitioned table and one partition owned by other user.
346 ALTER TABLE vacowned_parted OWNER TO regress_vacuum;
347 ALTER TABLE vacowned_part1 OWNER TO regress_vacuum;
348 SET ROLE regress_vacuum;
349 VACUUM vacowned_parted;
350 WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
351 VACUUM vacowned_part1;
352 VACUUM vacowned_part2;
353 WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
354 ANALYZE vacowned_parted;
355 WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
356 ANALYZE vacowned_part1;
357 ANALYZE vacowned_part2;
358 WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
359 VACUUM (ANALYZE) vacowned_parted;
360 WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
361 VACUUM (ANALYZE) vacowned_part1;
362 VACUUM (ANALYZE) vacowned_part2;
363 WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
364 RESET ROLE;
365 -- Only one partition owned by other user.
366 ALTER TABLE vacowned_parted OWNER TO CURRENT_USER;
367 SET ROLE regress_vacuum;
368 VACUUM vacowned_parted;
369 WARNING: skipping "vacowned_parted" --- only table or database owner can vacuum it
370 WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
371 VACUUM vacowned_part1;
372 VACUUM vacowned_part2;
373 WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
374 ANALYZE vacowned_parted;
375 WARNING: skipping "vacowned_parted" --- only table or database owner can analyze it
376 WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
377 ANALYZE vacowned_part1;
378 ANALYZE vacowned_part2;
379 WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
380 VACUUM (ANALYZE) vacowned_parted;
381 WARNING: skipping "vacowned_parted" --- only table or database owner can vacuum it
382 WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
383 VACUUM (ANALYZE) vacowned_part1;
384 VACUUM (ANALYZE) vacowned_part2;
385 WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
386 RESET ROLE;
387 -- Only partitioned table owned by other user.
388 ALTER TABLE vacowned_parted OWNER TO regress_vacuum;
389 ALTER TABLE vacowned_part1 OWNER TO CURRENT_USER;
390 SET ROLE regress_vacuum;
391 VACUUM vacowned_parted;
392 WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
393 WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
394 VACUUM vacowned_part1;
395 WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
396 VACUUM vacowned_part2;
397 WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
398 ANALYZE vacowned_parted;
399 WARNING: skipping "vacowned_part1" --- only table or database owner can analyze it
400 WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
401 ANALYZE vacowned_part1;
402 WARNING: skipping "vacowned_part1" --- only table or database owner can analyze it
403 ANALYZE vacowned_part2;
404 WARNING: skipping "vacowned_part2" --- only table or database owner can analyze it
405 VACUUM (ANALYZE) vacowned_parted;
406 WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
407 WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
408 VACUUM (ANALYZE) vacowned_part1;
409 WARNING: skipping "vacowned_part1" --- only table or database owner can vacuum it
410 VACUUM (ANALYZE) vacowned_part2;
411 WARNING: skipping "vacowned_part2" --- only table or database owner can vacuum it
412 RESET ROLE;
413 DROP TABLE vacowned;
414 DROP TABLE vacowned_parted;
415 DROP ROLE regress_vacuum;