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