1-- 2-- VACUUM 3-- 4CREATE TABLE vactst (i INT); 5INSERT INTO vactst VALUES (1); 6INSERT INTO vactst SELECT * FROM vactst; 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 VALUES (0); 18SELECT count(*) FROM vactst; 19 count 20------- 21 2049 22(1 row) 23 24DELETE FROM vactst WHERE i != 0; 25SELECT * FROM vactst; 26 i 27--- 28 0 29(1 row) 30 31VACUUM FULL vactst; 32UPDATE vactst SET i = i + 1; 33INSERT INTO vactst SELECT * FROM vactst; 34INSERT INTO vactst SELECT * FROM vactst; 35INSERT INTO vactst SELECT * FROM vactst; 36INSERT INTO vactst SELECT * FROM vactst; 37INSERT INTO vactst SELECT * FROM vactst; 38INSERT INTO vactst SELECT * FROM vactst; 39INSERT INTO vactst SELECT * FROM vactst; 40INSERT INTO vactst SELECT * FROM vactst; 41INSERT INTO vactst SELECT * FROM vactst; 42INSERT INTO vactst SELECT * FROM vactst; 43INSERT INTO vactst SELECT * FROM vactst; 44INSERT INTO vactst VALUES (0); 45SELECT count(*) FROM vactst; 46 count 47------- 48 2049 49(1 row) 50 51DELETE FROM vactst WHERE i != 0; 52VACUUM (FULL) vactst; 53DELETE FROM vactst; 54SELECT * FROM vactst; 55 i 56--- 57(0 rows) 58 59VACUUM (FULL, FREEZE) vactst; 60VACUUM (ANALYZE, FULL) vactst; 61CREATE TABLE vaccluster (i INT PRIMARY KEY); 62ALTER TABLE vaccluster CLUSTER ON vaccluster_pkey; 63CLUSTER vaccluster; 64CREATE FUNCTION do_analyze() RETURNS VOID VOLATILE LANGUAGE SQL 65 AS 'ANALYZE pg_am'; 66CREATE FUNCTION wrap_do_analyze(c INT) RETURNS INT IMMUTABLE LANGUAGE SQL 67 AS 'SELECT $1 FROM do_analyze()'; 68CREATE INDEX ON vaccluster(wrap_do_analyze(i)); 69INSERT INTO vaccluster VALUES (1), (2); 70ANALYZE vaccluster; 71ERROR: ANALYZE cannot be executed from VACUUM or ANALYZE 72CONTEXT: SQL function "do_analyze" statement 1 73SQL 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. 79INSERT INTO vactst SELECT generate_series(1, 300); 80DELETE FROM vactst WHERE i % 7 = 0; -- delete a few rows outside 81BEGIN; 82INSERT INTO vactst SELECT generate_series(301, 400); 83DELETE FROM vactst WHERE i % 5 <> 0; -- delete a few rows inside 84ANALYZE vactst; 85COMMIT; 86VACUUM FULL pg_am; 87VACUUM FULL pg_class; 88VACUUM FULL pg_database; 89VACUUM FULL vaccluster; 90ERROR: ANALYZE cannot be executed from VACUUM or ANALYZE 91CONTEXT: SQL function "do_analyze" statement 1 92SQL function "wrap_do_analyze" statement 1 93VACUUM FULL vactst; 94VACUUM (DISABLE_PAGE_SKIPPING) vaccluster; 95-- partitioned table 96CREATE TABLE vacparted (a int, b char) PARTITION BY LIST (a); 97CREATE TABLE vacparted1 PARTITION OF vacparted FOR VALUES IN (1); 98INSERT INTO vacparted VALUES (1, 'a'); 99UPDATE vacparted SET b = 'b'; 100VACUUM (ANALYZE) vacparted; 101VACUUM (FULL) vacparted; 102VACUUM (FREEZE) vacparted; 103-- check behavior with duplicate column mentions 104VACUUM ANALYZE vacparted(a,b,a); 105ERROR: column "a" of relation "vacparted" appears more than once 106ANALYZE vacparted(a,b,b); 107ERROR: column "b" of relation "vacparted" appears more than once 108-- multiple tables specified 109VACUUM vaccluster, vactst; 110VACUUM vacparted, does_not_exist; 111ERROR: relation "does_not_exist" does not exist 112VACUUM (FREEZE) vacparted, vaccluster, vactst; 113VACUUM (FREEZE) does_not_exist, vaccluster; 114ERROR: relation "does_not_exist" does not exist 115VACUUM ANALYZE vactst, vacparted (a); 116VACUUM ANALYZE vactst (does_not_exist), vacparted (b); 117ERROR: column "does_not_exist" of relation "vactst" does not exist 118VACUUM FULL vacparted, vactst; 119VACUUM FULL vactst, vacparted (a, b), vaccluster (i); 120ERROR: ANALYZE option must be specified when a column list is provided 121ANALYZE vactst, vacparted; 122ANALYZE vacparted (b), vactst; 123ANALYZE vactst, does_not_exist, vacparted; 124ERROR: relation "does_not_exist" does not exist 125ANALYZE vactst (i), vacparted (does_not_exist); 126ERROR: column "does_not_exist" of relation "vacparted" does not exist 127ANALYZE vactst, vactst; 128BEGIN; -- ANALYZE behaves differently inside a transaction block 129ANALYZE vactst, vactst; 130COMMIT; 131-- parenthesized syntax for ANALYZE 132ANALYZE (VERBOSE) does_not_exist; 133ERROR: relation "does_not_exist" does not exist 134ANALYZE (nonexistant-arg) does_not_exist; 135ERROR: syntax error at or near "nonexistant" 136LINE 1: ANALYZE (nonexistant-arg) does_not_exist; 137 ^ 138DROP TABLE vaccluster; 139DROP TABLE vactst; 140DROP TABLE vacparted; 141