src/test/regress/expected/vacuum.out - master - githubmirror/postgres

src/test/regress/expected/vacuum.out
732 строки27 KB
David Rowley
Cleanup VACUUM option processing error messages
09 окт 2025, 23:25
09 окт 2025, 23:251b073cb
--
-- VACUUM
--
CREATE TABLE vactst (i INT);
INSERT INTO vactst VALUES (1);
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst VALUES (0);
SELECT count(*) FROM vactst;
 count 
-------
  2049
(1 row)

DELETE FROM vactst WHERE i != 0;
SELECT * FROM vactst;
 i 
---
 0
(1 row)

VACUUM FULL vactst;
UPDATE vactst SET i = i + 1;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst SELECT * FROM vactst;
INSERT INTO vactst VALUES (0);
SELECT count(*) FROM vactst;
 count 
-------
  2049
(1 row)

DELETE FROM vactst WHERE i != 0;
VACUUM (FULL) vactst;
DELETE FROM vactst;
SELECT * FROM vactst;
 i 
---
(0 rows)

VACUUM (FULL, FREEZE) vactst;
VACUUM (ANALYZE, FULL) vactst;
CREATE TABLE vaccluster (i INT PRIMARY KEY);
ALTER TABLE vaccluster CLUSTER ON vaccluster_pkey;
CLUSTER vaccluster;
CREATE FUNCTION do_analyze() RETURNS VOID VOLATILE LANGUAGE SQL
	AS 'ANALYZE pg_am';
CREATE FUNCTION wrap_do_analyze(c INT) RETURNS INT IMMUTABLE LANGUAGE SQL
	AS 'SELECT $1 FROM public.do_analyze()';
CREATE INDEX ON vaccluster(wrap_do_analyze(i));
INSERT INTO vaccluster VALUES (1), (2);
ANALYZE vaccluster;
ERROR:  ANALYZE cannot be executed from VACUUM or ANALYZE
CONTEXT:  SQL function "do_analyze" statement 1
SQL function "wrap_do_analyze" statement 1
-- Test ANALYZE in transaction, where the transaction surrounding
-- analyze performed modifications. This tests for the bug at
-- https://postgr.es/m/c7988239-d42c-ddc4-41db-171b23b35e4f%40ssinger.info
-- (which hopefully is unlikely to be reintroduced), but also seems
-- independently worthwhile to cover.
INSERT INTO vactst SELECT generate_series(1, 300);
DELETE FROM vactst WHERE i % 7 = 0; -- delete a few rows outside
BEGIN;
INSERT INTO vactst SELECT generate_series(301, 400);
DELETE FROM vactst WHERE i % 5 <> 0; -- delete a few rows inside
ANALYZE vactst;
COMMIT;
-- Test ANALYZE setting relhassubclass=f for non-partitioning inheritance
BEGIN;
CREATE TABLE past_inh_parent ();
CREATE TABLE past_inh_child () INHERITS (past_inh_parent);
INSERT INTO past_inh_child DEFAULT VALUES;
INSERT INTO past_inh_child DEFAULT VALUES;
ANALYZE past_inh_parent;
SELECT reltuples, relhassubclass
  FROM pg_class WHERE oid = 'past_inh_parent'::regclass;
 reltuples | relhassubclass 
-----------+----------------
         0 | t
(1 row)

DROP TABLE past_inh_child;
ANALYZE past_inh_parent;
SELECT reltuples, relhassubclass
  FROM pg_class WHERE oid = 'past_inh_parent'::regclass;
 reltuples | relhassubclass 
-----------+----------------
         0 | f
(1 row)

COMMIT;
-- Test ANALYZE setting relhassubclass=f for partitioning
BEGIN;
CREATE TABLE past_parted (i int) PARTITION BY LIST(i);
CREATE TABLE past_part PARTITION OF past_parted FOR VALUES IN (1);
INSERT INTO past_parted VALUES (1),(1);
ANALYZE past_parted;
DROP TABLE past_part;
SELECT reltuples, relhassubclass
  FROM pg_class WHERE oid = 'past_parted'::regclass;
 reltuples | relhassubclass 
-----------+----------------
         2 | t
(1 row)

ANALYZE past_parted;
SELECT reltuples, relhassubclass
  FROM pg_class WHERE oid = 'past_parted'::regclass;
 reltuples | relhassubclass 
-----------+----------------
         0 | f
(1 row)

COMMIT;
VACUUM FULL pg_am;
VACUUM FULL pg_class;
VACUUM FULL pg_database;
VACUUM FULL vaccluster;
ERROR:  ANALYZE cannot be executed from VACUUM or ANALYZE
CONTEXT:  SQL function "do_analyze" statement 1
SQL function "wrap_do_analyze" statement 1
VACUUM FULL vactst;
VACUUM (DISABLE_PAGE_SKIPPING) vaccluster;
-- PARALLEL option
CREATE TABLE pvactst (i INT, a INT[], p POINT) with (autovacuum_enabled = off);
INSERT INTO pvactst SELECT i, array[1,2,3], point(i, i+1) FROM generate_series(1,1000) i;
CREATE INDEX btree_pvactst ON pvactst USING btree (i);
CREATE INDEX hash_pvactst ON pvactst USING hash (i);
CREATE INDEX brin_pvactst ON pvactst USING brin (i);
CREATE INDEX gin_pvactst ON pvactst USING gin (a);
CREATE INDEX gist_pvactst ON pvactst USING gist (p);
CREATE INDEX spgist_pvactst ON pvactst USING spgist (p);
CREATE TABLE pvactst2 (i INT) WITH (autovacuum_enabled = off);
INSERT INTO pvactst2 SELECT generate_series(1, 1000);
CREATE INDEX ON pvactst2 (i);
CREATE INDEX ON pvactst2 (i);
-- VACUUM invokes parallel index cleanup
SET min_parallel_index_scan_size to 0;
VACUUM (PARALLEL 2) pvactst;
-- VACUUM invokes parallel bulk-deletion
UPDATE pvactst SET i = i WHERE i < 1000;
VACUUM (PARALLEL 2) pvactst;
UPDATE pvactst SET i = i WHERE i < 1000;
VACUUM (PARALLEL 0) pvactst; -- disable parallel vacuum
VACUUM (PARALLEL -1) pvactst; -- error
ERROR:  PARALLEL option must be between 0 and 1024
LINE 1: VACUUM (PARALLEL -1) pvactst;
                ^
VACUUM (PARALLEL 2, INDEX_CLEANUP FALSE) pvactst;
VACUUM (PARALLEL 2, FULL TRUE) pvactst; -- error, cannot use both PARALLEL and FULL
ERROR:  VACUUM FULL cannot be performed in parallel
VACUUM (PARALLEL) pvactst; -- error, cannot use PARALLEL option without parallel degree
ERROR:  parallel requires an integer value
-- Test parallel vacuum using the minimum maintenance_work_mem with and without
-- dead tuples.
SET maintenance_work_mem TO 64;
VACUUM (PARALLEL 2) pvactst2;
DELETE FROM pvactst2 WHERE i < 1000;
VACUUM (PARALLEL 2) pvactst2;
RESET maintenance_work_mem;
-- Test different combinations of parallel and full options for temporary tables
CREATE TEMPORARY TABLE tmp (a int PRIMARY KEY);
CREATE INDEX tmp_idx1 ON tmp (a);
VACUUM (PARALLEL 1, FULL FALSE) tmp; -- parallel vacuum disabled for temp tables
WARNING:  disabling parallel option of vacuum on "tmp" --- cannot vacuum temporary tables in parallel
VACUUM (PARALLEL 0, FULL TRUE) tmp; -- can specify parallel disabled (even though that's implied by FULL)
RESET min_parallel_index_scan_size;
DROP TABLE pvactst;
DROP TABLE pvactst2;
-- INDEX_CLEANUP option
CREATE TABLE no_index_cleanup (i INT PRIMARY KEY, t TEXT);
-- Use uncompressed data stored in toast.
CREATE INDEX no_index_cleanup_idx ON no_index_cleanup(t);
ALTER TABLE no_index_cleanup ALTER COLUMN t SET STORAGE EXTERNAL;
INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(1,30),
    repeat('1234567890',269));
-- index cleanup option is ignored if VACUUM FULL
VACUUM (INDEX_CLEANUP TRUE, FULL TRUE) no_index_cleanup;
VACUUM (FULL TRUE) no_index_cleanup;
-- Toast inherits the value from its parent table.
ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = false);
DELETE FROM no_index_cleanup WHERE i < 15;
-- Nothing is cleaned up.
VACUUM no_index_cleanup;
-- Both parent relation and toast are cleaned up.
ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true);
VACUUM no_index_cleanup;
ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = auto);
VACUUM no_index_cleanup;
-- Parameter is set for both the parent table and its toast relation.
INSERT INTO no_index_cleanup(i, t) VALUES (generate_series(31,60),
    repeat('1234567890',269));
DELETE FROM no_index_cleanup WHERE i < 45;
-- Only toast index is cleaned up.
ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = off,
    toast.vacuum_index_cleanup = yes);
VACUUM no_index_cleanup;
-- Only parent is cleaned up.
ALTER TABLE no_index_cleanup SET (vacuum_index_cleanup = true,
    toast.vacuum_index_cleanup = false);
VACUUM no_index_cleanup;
-- Test some extra relations.
VACUUM (INDEX_CLEANUP FALSE) vaccluster;
VACUUM (INDEX_CLEANUP AUTO) vactst; -- index cleanup option is ignored if no indexes
VACUUM (INDEX_CLEANUP FALSE, FREEZE TRUE) vaccluster;
-- TRUNCATE option
CREATE TEMP TABLE vac_truncate_test(i INT NOT NULL, j text)
	WITH (vacuum_truncate=true, autovacuum_enabled=false);
INSERT INTO vac_truncate_test VALUES (1, NULL), (NULL, NULL);
ERROR:  null value in column "i" of relation "vac_truncate_test" violates not-null constraint
DETAIL:  Failing row contains (null, null).
VACUUM (TRUNCATE FALSE, DISABLE_PAGE_SKIPPING) vac_truncate_test;
SELECT pg_relation_size('vac_truncate_test') > 0;
 ?column? 
----------
 t
(1 row)

SET vacuum_truncate = false;
VACUUM (DISABLE_PAGE_SKIPPING) vac_truncate_test;
SELECT pg_relation_size('vac_truncate_test') = 0;
 ?column? 
----------
 t
(1 row)

VACUUM (TRUNCATE FALSE, FULL TRUE) vac_truncate_test;
ALTER TABLE vac_truncate_test RESET (vacuum_truncate);
INSERT INTO vac_truncate_test VALUES (1, NULL), (NULL, NULL);
ERROR:  null value in column "i" of relation "vac_truncate_test" violates not-null constraint
DETAIL:  Failing row contains (null, null).
VACUUM (DISABLE_PAGE_SKIPPING) vac_truncate_test;
SELECT pg_relation_size('vac_truncate_test') > 0;
 ?column? 
----------
 t
(1 row)

RESET vacuum_truncate;
VACUUM (TRUNCATE FALSE, DISABLE_PAGE_SKIPPING) vac_truncate_test;
SELECT pg_relation_size('vac_truncate_test') > 0;
 ?column? 
----------
 t
(1 row)

VACUUM (DISABLE_PAGE_SKIPPING) vac_truncate_test;
SELECT pg_relation_size('vac_truncate_test') = 0;
 ?column? 
----------
 t
(1 row)

DROP TABLE vac_truncate_test;
-- partitioned table
CREATE TABLE vacparted (a int, b char) PARTITION BY LIST (a);
CREATE TABLE vacparted1 PARTITION OF vacparted FOR VALUES IN (1);
INSERT INTO vacparted VALUES (1, 'a');
UPDATE vacparted SET b = 'b';
VACUUM (ANALYZE) vacparted;
VACUUM (FULL) vacparted;
VACUUM (FREEZE) vacparted;
-- check behavior with duplicate column mentions
VACUUM ANALYZE vacparted(a,b,a);
ERROR:  column "a" of relation "vacparted" appears more than once
ANALYZE vacparted(a,b,b);
ERROR:  column "b" of relation "vacparted" appears more than once
-- partitioned table with index
CREATE TABLE vacparted_i (a int primary key, b varchar(100))
  PARTITION BY HASH (a);
CREATE TABLE vacparted_i1 PARTITION OF vacparted_i
  FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE vacparted_i2 PARTITION OF vacparted_i
  FOR VALUES WITH (MODULUS 2, REMAINDER 1);
INSERT INTO vacparted_i SELECT i, 'test_'|| i from generate_series(1,10) i;
VACUUM (ANALYZE) vacparted_i;
VACUUM (FULL) vacparted_i;
VACUUM (FREEZE) vacparted_i;
SELECT relname, relhasindex FROM pg_class
  WHERE relname LIKE 'vacparted_i%' AND relkind IN ('p','r')
  ORDER BY relname;
   relname    | relhasindex 
--------------+-------------
 vacparted_i  | t
 vacparted_i1 | t
 vacparted_i2 | t
(3 rows)

DROP TABLE vacparted_i;
-- multiple tables specified
VACUUM vaccluster, vactst;
VACUUM vacparted, does_not_exist;
ERROR:  relation "does_not_exist" does not exist
VACUUM (FREEZE) vacparted, vaccluster, vactst;
VACUUM (FREEZE) does_not_exist, vaccluster;
ERROR:  relation "does_not_exist" does not exist
VACUUM ANALYZE vactst, vacparted (a);
VACUUM ANALYZE vactst (does_not_exist), vacparted (b);
ERROR:  column "does_not_exist" of relation "vactst" does not exist
VACUUM FULL vacparted, vactst;
VACUUM FULL vactst, vacparted (a, b), vaccluster (i);
ERROR:  ANALYZE option must be specified when a column list is provided
ANALYZE vactst, vacparted;
ANALYZE vacparted (b), vactst;
ANALYZE vactst, does_not_exist, vacparted;
ERROR:  relation "does_not_exist" does not exist
ANALYZE vactst (i), vacparted (does_not_exist);
ERROR:  column "does_not_exist" of relation "vacparted" does not exist
ANALYZE vactst, vactst;
BEGIN;  -- ANALYZE behaves differently inside a transaction block
ANALYZE vactst, vactst;
COMMIT;
--
-- Tests for ANALYZE ONLY / VACUUM ONLY on partitioned tables
--
CREATE TABLE only_parted (a int, b text) PARTITION BY LIST (a);
CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
INSERT INTO only_parted VALUES (1, 'a');
-- Ensure only the partitioned table is analyzed
ANALYZE ONLY only_parted;
SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
  FROM pg_stat_user_tables
  WHERE relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
  ORDER BY relname;
   relname    | analyzed | vacuumed 
--------------+----------+----------
 only_parted  | t        | f
 only_parted1 | f        | f
(2 rows)

-- Ensure partitioned table and the partitions are analyzed
ANALYZE only_parted;
SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
  FROM pg_stat_user_tables
  WHERE relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
  ORDER BY relname;
   relname    | analyzed | vacuumed 
--------------+----------+----------
 only_parted  | t        | f
 only_parted1 | t        | f
(2 rows)

DROP TABLE only_parted;
-- VACUUM ONLY on a partitioned table does nothing, ensure we get a warning.
VACUUM ONLY vacparted;
WARNING:  VACUUM ONLY of partitioned table "vacparted" has no effect
-- Try ANALYZE ONLY with a column list
ANALYZE ONLY vacparted(a,b);
--
-- Tests for VACUUM ONLY / ANALYZE ONLY on inheritance tables
--
CREATE TABLE only_inh_parent (a int primary key, b TEXT);
CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
-- Ensure only parent is analyzed
ANALYZE ONLY only_inh_parent;
SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
  FROM pg_stat_user_tables
  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
  ORDER BY relname;
     relname     | analyzed | vacuumed 
-----------------+----------+----------
 only_inh_child  | f        | f
 only_inh_parent | t        | f
(2 rows)

-- Ensure the parent and child are analyzed
ANALYZE only_inh_parent;
SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
  FROM pg_stat_user_tables
  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
  ORDER BY relname;
     relname     | analyzed | vacuumed 
-----------------+----------+----------
 only_inh_child  | t        | f
 only_inh_parent | t        | f
(2 rows)

-- Ensure only the parent is vacuumed
VACUUM ONLY only_inh_parent;
SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
  FROM pg_stat_user_tables
  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
  ORDER BY relname;
     relname     | analyzed | vacuumed 
-----------------+----------+----------
 only_inh_child  | t        | f
 only_inh_parent | t        | t
(2 rows)

-- Ensure parent and child are vacuumed
VACUUM only_inh_parent;
SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
  FROM pg_stat_user_tables
  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
  ORDER BY relname;
     relname     | analyzed | vacuumed 
-----------------+----------+----------
 only_inh_child  | t        | t
 only_inh_parent | t        | t
(2 rows)

DROP TABLE only_inh_parent CASCADE;
NOTICE:  drop cascades to table only_inh_child
-- parenthesized syntax for ANALYZE
ANALYZE (VERBOSE) does_not_exist;
ERROR:  relation "does_not_exist" does not exist
ANALYZE (nonexistent-arg) does_not_exist;
ERROR:  syntax error at or near "arg"
LINE 1: ANALYZE (nonexistent-arg) does_not_exist;
                             ^
ANALYZE (nonexistentarg) does_not_exit;
ERROR:  unrecognized ANALYZE option "nonexistentarg"
LINE 1: ANALYZE (nonexistentarg) does_not_exit;
                 ^
-- ensure argument order independence, and that SKIP_LOCKED on non-existing
-- relation still errors out.  Suppress WARNING messages caused by concurrent
-- autovacuums.
SET client_min_messages TO 'ERROR';
ANALYZE (SKIP_LOCKED, VERBOSE) does_not_exist;
ERROR:  relation "does_not_exist" does not exist
ANALYZE (VERBOSE, SKIP_LOCKED) does_not_exist;
ERROR:  relation "does_not_exist" does not exist
-- SKIP_LOCKED option
VACUUM (SKIP_LOCKED) vactst;
VACUUM (SKIP_LOCKED, FULL) vactst;
ANALYZE (SKIP_LOCKED) vactst;
RESET client_min_messages;
-- ensure VACUUM and ANALYZE don't have a problem with serializable
SET default_transaction_isolation = serializable;
VACUUM vactst;
ANALYZE vactst;
RESET default_transaction_isolation;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ANALYZE vactst;
COMMIT;
-- PROCESS_TOAST option
CREATE TABLE vac_option_tab (a INT, t TEXT);
INSERT INTO vac_option_tab SELECT a, 't' || a FROM generate_series(1, 10) AS a;
ALTER TABLE vac_option_tab ALTER COLUMN t SET STORAGE EXTERNAL;
-- Check the number of vacuums done on table vac_option_tab and on its
-- toast relation, to check that PROCESS_TOAST and PROCESS_MAIN work on
-- what they should.
CREATE VIEW vac_option_tab_counts AS
  SELECT CASE WHEN c.relname IS NULL
    THEN 'main' ELSE 'toast' END as rel,
  s.vacuum_count
  FROM pg_stat_all_tables s
  LEFT JOIN pg_class c ON s.relid = c.reltoastrelid
  WHERE c.relname = 'vac_option_tab' OR s.relname = 'vac_option_tab'
  ORDER BY rel;
VACUUM (PROCESS_TOAST TRUE) vac_option_tab;
SELECT * FROM vac_option_tab_counts;
  rel  | vacuum_count 
-------+--------------
 main  |            1
 toast |            1
(2 rows)

VACUUM (PROCESS_TOAST FALSE) vac_option_tab;
SELECT * FROM vac_option_tab_counts;
  rel  | vacuum_count 
-------+--------------
 main  |            2
 toast |            1
(2 rows)

VACUUM (PROCESS_TOAST FALSE, FULL) vac_option_tab; -- error
ERROR:  PROCESS_TOAST required with VACUUM FULL
-- PROCESS_MAIN option
-- Only the toast table is processed.
VACUUM (PROCESS_MAIN FALSE) vac_option_tab;
SELECT * FROM vac_option_tab_counts;
  rel  | vacuum_count 
-------+--------------
 main  |            2
 toast |            2
(2 rows)

-- Nothing is processed.
VACUUM (PROCESS_MAIN FALSE, PROCESS_TOAST FALSE) vac_option_tab;
SELECT * FROM vac_option_tab_counts;
  rel  | vacuum_count 
-------+--------------
 main  |            2
 toast |            2
(2 rows)

-- Check if the filenodes nodes have been updated as wanted after FULL.
SELECT relfilenode AS main_filenode FROM pg_class
  WHERE relname = 'vac_option_tab' \gset
SELECT t.relfilenode AS toast_filenode FROM pg_class c, pg_class t
  WHERE c.reltoastrelid = t.oid AND c.relname = 'vac_option_tab' \gset
-- Only the toast relation is processed.
VACUUM (PROCESS_MAIN FALSE, FULL) vac_option_tab;
SELECT relfilenode = :main_filenode AS is_same_main_filenode
  FROM pg_class WHERE relname = 'vac_option_tab';
 is_same_main_filenode 
-----------------------
 t
(1 row)

SELECT t.relfilenode = :toast_filenode AS is_same_toast_filenode
  FROM pg_class c, pg_class t
  WHERE c.reltoastrelid = t.oid AND c.relname = 'vac_option_tab';
 is_same_toast_filenode 
------------------------
 f
(1 row)

-- BUFFER_USAGE_LIMIT option
VACUUM (BUFFER_USAGE_LIMIT '512 kB') vac_option_tab;
ANALYZE (BUFFER_USAGE_LIMIT '512 kB') vac_option_tab;
-- try disabling the buffer usage limit
VACUUM (BUFFER_USAGE_LIMIT 0) vac_option_tab;
ANALYZE (BUFFER_USAGE_LIMIT 0) vac_option_tab;
-- value exceeds max size error
VACUUM (BUFFER_USAGE_LIMIT 16777220) vac_option_tab;
ERROR:  BUFFER_USAGE_LIMIT option must be 0 or between 128 kB and 16777216 kB
-- value is less than min size error
VACUUM (BUFFER_USAGE_LIMIT 120) vac_option_tab;
ERROR:  BUFFER_USAGE_LIMIT option must be 0 or between 128 kB and 16777216 kB
-- integer overflow error
VACUUM (BUFFER_USAGE_LIMIT 10000000000) vac_option_tab;
ERROR:  BUFFER_USAGE_LIMIT option must be 0 or between 128 kB and 16777216 kB
HINT:  Value exceeds integer range.
-- incompatible with VACUUM FULL error
VACUUM (BUFFER_USAGE_LIMIT '512 kB', FULL) vac_option_tab;
ERROR:  BUFFER_USAGE_LIMIT cannot be specified for VACUUM FULL
-- SKIP_DATABASE_STATS option
VACUUM (SKIP_DATABASE_STATS) vactst;
-- ONLY_DATABASE_STATS option
VACUUM (ONLY_DATABASE_STATS);
VACUUM (ONLY_DATABASE_STATS) vactst;  -- error
ERROR:  ONLY_DATABASE_STATS cannot be specified with a list of tables
DROP VIEW vac_option_tab_counts;
DROP TABLE vac_option_tab;
DROP TABLE vaccluster;
DROP TABLE vactst;
DROP TABLE vacparted;
DROP TABLE no_index_cleanup;
-- relation ownership, WARNING logs generated as all are skipped.
CREATE TABLE vacowned (a int);
CREATE TABLE vacowned_parted (a int) PARTITION BY LIST (a);
CREATE TABLE vacowned_part1 PARTITION OF vacowned_parted FOR VALUES IN (1);
CREATE TABLE vacowned_part2 PARTITION OF vacowned_parted FOR VALUES IN (2);
CREATE ROLE regress_vacuum;
SET ROLE regress_vacuum;
-- Simple table
VACUUM vacowned;
WARNING:  permission denied to vacuum "vacowned", skipping it
ANALYZE vacowned;
WARNING:  permission denied to analyze "vacowned", skipping it
VACUUM (ANALYZE) vacowned;
WARNING:  permission denied to vacuum "vacowned", skipping it
-- Catalog
VACUUM pg_catalog.pg_class;
WARNING:  permission denied to vacuum "pg_class", skipping it
ANALYZE pg_catalog.pg_class;
WARNING:  permission denied to analyze "pg_class", skipping it
VACUUM (ANALYZE) pg_catalog.pg_class;
WARNING:  permission denied to vacuum "pg_class", skipping it
-- Shared catalog
VACUUM pg_catalog.pg_authid;
WARNING:  permission denied to vacuum "pg_authid", skipping it
ANALYZE pg_catalog.pg_authid;
WARNING:  permission denied to analyze "pg_authid", skipping it
VACUUM (ANALYZE) pg_catalog.pg_authid;
WARNING:  permission denied to vacuum "pg_authid", skipping it
-- Partitioned table and its partitions, nothing owned by other user.
-- Relations are not listed in a single command to test ownership
-- independently.
VACUUM vacowned_parted;
WARNING:  permission denied to vacuum "vacowned_parted", skipping it
WARNING:  permission denied to vacuum "vacowned_part1", skipping it
WARNING:  permission denied to vacuum "vacowned_part2", skipping it
VACUUM vacowned_part1;
WARNING:  permission denied to vacuum "vacowned_part1", skipping it
VACUUM vacowned_part2;
WARNING:  permission denied to vacuum "vacowned_part2", skipping it
ANALYZE vacowned_parted;
WARNING:  permission denied to analyze "vacowned_parted", skipping it
WARNING:  permission denied to analyze "vacowned_part1", skipping it
WARNING:  permission denied to analyze "vacowned_part2", skipping it
ANALYZE vacowned_part1;
WARNING:  permission denied to analyze "vacowned_part1", skipping it
ANALYZE vacowned_part2;
WARNING:  permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_parted;
WARNING:  permission denied to vacuum "vacowned_parted", skipping it
WARNING:  permission denied to vacuum "vacowned_part1", skipping it
WARNING:  permission denied to vacuum "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
WARNING:  permission denied to vacuum "vacowned_part1", skipping it
VACUUM (ANALYZE) vacowned_part2;
WARNING:  permission denied to vacuum "vacowned_part2", skipping it
RESET ROLE;
-- Partitioned table and one partition owned by other user.
ALTER TABLE vacowned_parted OWNER TO regress_vacuum;
ALTER TABLE vacowned_part1 OWNER TO regress_vacuum;
SET ROLE regress_vacuum;
VACUUM vacowned_parted;
WARNING:  permission denied to vacuum "vacowned_part2", skipping it
VACUUM vacowned_part1;
VACUUM vacowned_part2;
WARNING:  permission denied to vacuum "vacowned_part2", skipping it
ANALYZE vacowned_parted;
WARNING:  permission denied to analyze "vacowned_part2", skipping it
ANALYZE vacowned_part1;
ANALYZE vacowned_part2;
WARNING:  permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_parted;
WARNING:  permission denied to vacuum "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
VACUUM (ANALYZE) vacowned_part2;
WARNING:  permission denied to vacuum "vacowned_part2", skipping it
RESET ROLE;
-- Only one partition owned by other user.
ALTER TABLE vacowned_parted OWNER TO CURRENT_USER;
SET ROLE regress_vacuum;
VACUUM vacowned_parted;
WARNING:  permission denied to vacuum "vacowned_parted", skipping it
WARNING:  permission denied to vacuum "vacowned_part2", skipping it
VACUUM vacowned_part1;
VACUUM vacowned_part2;
WARNING:  permission denied to vacuum "vacowned_part2", skipping it
ANALYZE vacowned_parted;
WARNING:  permission denied to analyze "vacowned_parted", skipping it
WARNING:  permission denied to analyze "vacowned_part2", skipping it
ANALYZE vacowned_part1;
ANALYZE vacowned_part2;
WARNING:  permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_parted;
WARNING:  permission denied to vacuum "vacowned_parted", skipping it
WARNING:  permission denied to vacuum "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
VACUUM (ANALYZE) vacowned_part2;
WARNING:  permission denied to vacuum "vacowned_part2", skipping it
RESET ROLE;
-- Only partitioned table owned by other user.
ALTER TABLE vacowned_parted OWNER TO regress_vacuum;
ALTER TABLE vacowned_part1 OWNER TO CURRENT_USER;
SET ROLE regress_vacuum;
VACUUM vacowned_parted;
WARNING:  permission denied to vacuum "vacowned_part1", skipping it
WARNING:  permission denied to vacuum "vacowned_part2", skipping it
VACUUM vacowned_part1;
WARNING:  permission denied to vacuum "vacowned_part1", skipping it
VACUUM vacowned_part2;
WARNING:  permission denied to vacuum "vacowned_part2", skipping it
ANALYZE vacowned_parted;
WARNING:  permission denied to analyze "vacowned_part1", skipping it
WARNING:  permission denied to analyze "vacowned_part2", skipping it
ANALYZE vacowned_part1;
WARNING:  permission denied to analyze "vacowned_part1", skipping it
ANALYZE vacowned_part2;
WARNING:  permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_parted;
WARNING:  permission denied to vacuum "vacowned_part1", skipping it
WARNING:  permission denied to vacuum "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
WARNING:  permission denied to vacuum "vacowned_part1", skipping it
VACUUM (ANALYZE) vacowned_part2;
WARNING:  permission denied to vacuum "vacowned_part2", skipping it
RESET ROLE;
DROP TABLE vacowned;
DROP TABLE vacowned_parted;
DROP ROLE regress_vacuum;
-- Test checking how new toast values are allocated on rewrite.
-- Create table with plain storage (forces inline storage initially).
CREATE TABLE vac_rewrite_toast (id int, f1 TEXT STORAGE plain);
-- Insert tuple large enough to trigger toast storage on rewrite, still
-- small enough to fit on a page.
INSERT INTO vac_rewrite_toast values (1, repeat('a', 7000));
-- Switch to external storage to force toast table usage.
ALTER TABLE vac_rewrite_toast ALTER COLUMN f1 SET STORAGE EXTERNAL;
-- This second tuple is toasted, its value should still be the
-- same after rewrite.
INSERT INTO vac_rewrite_toast values (2, repeat('a', 7000));
SELECT pg_column_toast_chunk_id(f1) AS id_2_chunk FROM vac_rewrite_toast
  WHERE id = 2 \gset
-- Check initial state of the data.
SELECT id, pg_column_toast_chunk_id(f1) IS NULL AS f1_chunk_null,
  substr(f1, 5, 10) AS f1_data,
  pg_column_compression(f1) AS f1_comp
  FROM vac_rewrite_toast ORDER BY id;
 id | f1_chunk_null |  f1_data   | f1_comp 
----+---------------+------------+---------
  1 | t             | aaaaaaaaaa | 
  2 | f             | aaaaaaaaaa | 
(2 rows)

-- VACUUM FULL forces toast data rewrite.
VACUUM FULL vac_rewrite_toast;
-- Check after rewrite.
SELECT id, pg_column_toast_chunk_id(f1) IS NULL AS f1_chunk_null,
  substr(f1, 5, 10) AS f1_data,
  pg_column_compression(f1) AS f1_comp
  FROM vac_rewrite_toast ORDER BY id;
 id | f1_chunk_null |  f1_data   | f1_comp 
----+---------------+------------+---------
  1 | f             | aaaaaaaaaa | 
  2 | f             | aaaaaaaaaa | 
(2 rows)

-- The same value is reused for the tuple toasted before the rewrite.
SELECT pg_column_toast_chunk_id(f1) = :'id_2_chunk' AS same_chunk
  FROM vac_rewrite_toast WHERE id = 2;
 same_chunk 
------------
 t
(1 row)

DROP TABLE vac_rewrite_toast;