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 74VACUUM FULL pg_am; 75VACUUM FULL pg_class; 76VACUUM FULL pg_database; 77VACUUM FULL vaccluster; 78ERROR: ANALYZE cannot be executed from VACUUM or ANALYZE 79CONTEXT: SQL function "do_analyze" statement 1 80SQL function "wrap_do_analyze" statement 1 81VACUUM FULL vactst; 82VACUUM (DISABLE_PAGE_SKIPPING) vaccluster; 83DROP TABLE vaccluster; 84DROP TABLE vactst; 85-- partitioned table 86CREATE TABLE vacparted (a int, b char) PARTITION BY LIST (a); 87CREATE TABLE vacparted1 PARTITION OF vacparted FOR VALUES IN (1); 88INSERT INTO vacparted VALUES (1, 'a'); 89UPDATE vacparted SET b = 'b'; 90VACUUM (ANALYZE) vacparted; 91VACUUM (FULL) vacparted; 92VACUUM (FREEZE) vacparted; 93-- check behavior with duplicate column mentions 94VACUUM ANALYZE vacparted(a,b,a); 95ERROR: column "a" of relation "vacparted" appears more than once 96ANALYZE vacparted(a,b,b); 97ERROR: column "b" of relation "vacparted" appears more than once 98DROP TABLE vacparted; 99