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