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