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; 130-- Parameter is set for both the parent table and its toast relation. 131INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(31,60), 132 repeat('1234567890',269)); 133DELETE FROM no_index_cleanup WHERE i < 45; 134-- Only toast index is cleaned up. 135ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = false, 136 toast.vacuum_index_cleanup = true); 137VACUUM no_index_cleanup; 138-- Only parent is cleaned up. 139ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true, 140 toast.vacuum_index_cleanup = false); 141VACUUM no_index_cleanup; 142-- Test some extra relations. 143VACUUM (INDEX_CLEANUP FALSE) vaccluster; 144VACUUM (INDEX_CLEANUP FALSE) vactst; -- index cleanup option is ignored if no indexes 145VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster; 146 147-- TRUNCATE option 148CREATE TABLE vac_truncate_test(i INT NOT NULL, j text) 149 WITH (vacuum_truncate=true, autovacuum_enabled=false); 150INSERT INTO vac_truncate_test VALUES (1, NULL), (NULL, NULL); 151VACUUM (TRUNCATE FALSE) vac_truncate_test; 152SELECT pg_relation_size('vac_truncate_test') > 0; 153VACUUM vac_truncate_test; 154SELECT pg_relation_size('vac_truncate_test') = 0; 155VACUUM (TRUNCATE FALSE, FULL TRUE) vac_truncate_test; 156DROP TABLE vac_truncate_test; 157 158-- partitioned table 159CREATE TABLE vacparted (a int, b char) PARTITION BY LIST (a); 160CREATE TABLE vacparted1 PARTITION OF vacparted FOR VALUES IN (1); 161INSERT INTO vacparted VALUES (1, 'a'); 162UPDATE vacparted SET b = 'b'; 163VACUUM (ANALYZE) vacparted; 164VACUUM (FULL) vacparted; 165VACUUM (FREEZE) vacparted; 166 167-- check behavior with duplicate column mentions 168VACUUM ANALYZE vacparted(a,b,a); 169ANALYZE vacparted(a,b,b); 170 171-- multiple tables specified 172VACUUM vaccluster, vactst; 173VACUUM vacparted, does_not_exist; 174VACUUM (FREEZE) vacparted, vaccluster, vactst; 175VACUUM (FREEZE) does_not_exist, vaccluster; 176VACUUM ANALYZE vactst, vacparted (a); 177VACUUM ANALYZE vactst (does_not_exist), vacparted (b); 178VACUUM FULL vacparted, vactst; 179VACUUM FULL vactst, vacparted (a, b), vaccluster (i); 180ANALYZE vactst, vacparted; 181ANALYZE vacparted (b), vactst; 182ANALYZE vactst, does_not_exist, vacparted; 183ANALYZE vactst (i), vacparted (does_not_exist); 184ANALYZE vactst, vactst; 185BEGIN; -- ANALYZE behaves differently inside a transaction block 186ANALYZE vactst, vactst; 187COMMIT; 188 189-- parenthesized syntax for ANALYZE 190ANALYZE (VERBOSE) does_not_exist; 191ANALYZE (nonexistent-arg) does_not_exist; 192ANALYZE (nonexistentarg) does_not_exit; 193 194-- ensure argument order independence, and that SKIP_LOCKED on non-existing 195-- relation still errors out. Suppress WARNING messages caused by concurrent 196-- autovacuums. 197SET client_min_messages TO 'ERROR'; 198ANALYZE (SKIP_LOCKED, VERBOSE) does_not_exist; 199ANALYZE (VERBOSE, SKIP_LOCKED) does_not_exist; 200 201-- SKIP_LOCKED option 202VACUUM (SKIP_LOCKED) vactst; 203VACUUM (SKIP_LOCKED, FULL) vactst; 204ANALYZE (SKIP_LOCKED) vactst; 205RESET client_min_messages; 206 207-- ensure VACUUM and ANALYZE don't have a problem with serializable 208SET default_transaction_isolation = serializable; 209VACUUM vactst; 210ANALYZE vactst; 211RESET default_transaction_isolation; 212BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; 213ANALYZE vactst; 214COMMIT; 215 216DROP TABLE vaccluster; 217DROP TABLE vactst; 218DROP TABLE vacparted; 219DROP TABLE no_index_cleanup; 220 221-- relation ownership, WARNING logs generated as all are skipped. 222CREATE TABLE vacowned (a int); 223CREATE TABLE vacowned_parted (a int) PARTITION BY LIST (a); 224CREATE TABLE vacowned_part1 PARTITION OF vacowned_parted FOR VALUES IN (1); 225CREATE TABLE vacowned_part2 PARTITION OF vacowned_parted FOR VALUES IN (2); 226CREATE ROLE regress_vacuum; 227SET ROLE regress_vacuum; 228-- Simple table 229VACUUM vacowned; 230ANALYZE vacowned; 231VACUUM (ANALYZE) vacowned; 232-- Catalog 233VACUUM pg_catalog.pg_class; 234ANALYZE pg_catalog.pg_class; 235VACUUM (ANALYZE) pg_catalog.pg_class; 236-- Shared catalog 237VACUUM pg_catalog.pg_authid; 238ANALYZE pg_catalog.pg_authid; 239VACUUM (ANALYZE) pg_catalog.pg_authid; 240-- Partitioned table and its partitions, nothing owned by other user. 241-- Relations are not listed in a single command to test ownership 242-- independently. 243VACUUM vacowned_parted; 244VACUUM vacowned_part1; 245VACUUM vacowned_part2; 246ANALYZE vacowned_parted; 247ANALYZE vacowned_part1; 248ANALYZE vacowned_part2; 249VACUUM (ANALYZE) vacowned_parted; 250VACUUM (ANALYZE) vacowned_part1; 251VACUUM (ANALYZE) vacowned_part2; 252RESET ROLE; 253-- Partitioned table and one partition owned by other user. 254ALTER TABLE vacowned_parted OWNER TO regress_vacuum; 255ALTER TABLE vacowned_part1 OWNER TO regress_vacuum; 256SET ROLE regress_vacuum; 257VACUUM vacowned_parted; 258VACUUM vacowned_part1; 259VACUUM vacowned_part2; 260ANALYZE vacowned_parted; 261ANALYZE vacowned_part1; 262ANALYZE vacowned_part2; 263VACUUM (ANALYZE) vacowned_parted; 264VACUUM (ANALYZE) vacowned_part1; 265VACUUM (ANALYZE) vacowned_part2; 266RESET ROLE; 267-- Only one partition owned by other user. 268ALTER TABLE vacowned_parted OWNER TO CURRENT_USER; 269SET ROLE regress_vacuum; 270VACUUM vacowned_parted; 271VACUUM vacowned_part1; 272VACUUM vacowned_part2; 273ANALYZE vacowned_parted; 274ANALYZE vacowned_part1; 275ANALYZE vacowned_part2; 276VACUUM (ANALYZE) vacowned_parted; 277VACUUM (ANALYZE) vacowned_part1; 278VACUUM (ANALYZE) vacowned_part2; 279RESET ROLE; 280-- Only partitioned table owned by other user. 281ALTER TABLE vacowned_parted OWNER TO regress_vacuum; 282ALTER TABLE vacowned_part1 OWNER TO CURRENT_USER; 283SET ROLE regress_vacuum; 284VACUUM vacowned_parted; 285VACUUM vacowned_part1; 286VACUUM vacowned_part2; 287ANALYZE vacowned_parted; 288ANALYZE vacowned_part1; 289ANALYZE vacowned_part2; 290VACUUM (ANALYZE) vacowned_parted; 291VACUUM (ANALYZE) vacowned_part1; 292VACUUM (ANALYZE) vacowned_part2; 293RESET ROLE; 294DROP TABLE vacowned; 295DROP TABLE vacowned_parted; 296DROP ROLE regress_vacuum; 297