diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out index 0abcc99989e0..a4d9c5510cfc 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -686,3 +686,61 @@ RESET ROLE; DROP TABLE vacowned; DROP TABLE vacowned_parted; DROP ROLE regress_vacuum; +-- TRUNCATE option with VACUUM of more than 1 relation. +CREATE TABLE vac_truncate_on_toast_off(i int, j text) WITH + (autovacuum_enabled=false, vacuum_truncate=true, toast.vacuum_truncate=false); +CREATE TABLE vac_truncate_off_toast_on(i int, j text) WITH + (autovacuum_enabled=false, vacuum_truncate=false, toast.vacuum_truncate=true); +-- EXTERNAL to force data on TOAST table, uncompressed. +ALTER TABLE vac_truncate_on_toast_off ALTER COLUMN j SET STORAGE EXTERNAL; +ALTER TABLE vac_truncate_off_toast_on ALTER COLUMN j SET STORAGE EXTERNAL; +INSERT INTO vac_truncate_on_toast_off SELECT generate_series(1, 1000), NULL; +INSERT INTO vac_truncate_on_toast_off SELECT generate_series(1000, 1010), repeat('1234567890', 1000); +INSERT INTO vac_truncate_off_toast_on SELECT generate_series(1, 1000), NULL; +INSERT INTO vac_truncate_off_toast_on SELECT generate_series(1000, 1010), repeat('1234567890', 1000); +SELECT pg_relation_size('vac_truncate_on_toast_off') > 0 AS has_data; + has_data +---------- + t +(1 row) + +SELECT pg_relation_size(reltoastrelid) > 0 AS has_data FROM pg_class + WHERE relname = 'vac_truncate_on_toast_off'; + has_data +---------- + t +(1 row) + +SELECT pg_relation_size('vac_truncate_off_toast_on') > 0 AS has_data; + has_data +---------- + t +(1 row) + +SELECT pg_relation_size(reltoastrelid) > 0 AS has_data FROM pg_class + WHERE relname = 'vac_truncate_off_toast_on'; + has_data +---------- + t +(1 row) + +DELETE FROM vac_truncate_on_toast_off; +DELETE FROM vac_truncate_off_toast_on; +-- TRUNCATE options are retrieved from their respective relations. +-- Do an aggressive VACUUM to prevent page-skipping. +VACUUM (FREEZE, ANALYZE) vac_truncate_on_toast_off, vac_truncate_off_toast_on; +SELECT pg_relation_size(reltoastrelid) > 0 AS has_data FROM pg_class + WHERE relname = 'vac_truncate_on_toast_off'; + has_data +---------- + t +(1 row) + +SELECT pg_relation_size('vac_truncate_off_toast_on') > 0 AS has_data; + has_data +---------- + t +(1 row) + +DROP TABLE vac_truncate_on_toast_off; +DROP TABLE vac_truncate_off_toast_on; diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql index a72bdb5b619d..865582f93d17 100644 --- a/src/test/regress/sql/vacuum.sql +++ b/src/test/regress/sql/vacuum.sql @@ -495,3 +495,32 @@ RESET ROLE; DROP TABLE vacowned; DROP TABLE vacowned_parted; DROP ROLE regress_vacuum; + +-- TRUNCATE option with VACUUM of more than 1 relation. +CREATE TABLE vac_truncate_on_toast_off(i int, j text) WITH + (autovacuum_enabled=false, vacuum_truncate=true, toast.vacuum_truncate=false); +CREATE TABLE vac_truncate_off_toast_on(i int, j text) WITH + (autovacuum_enabled=false, vacuum_truncate=false, toast.vacuum_truncate=true); +-- EXTERNAL to force data on TOAST table, uncompressed. +ALTER TABLE vac_truncate_on_toast_off ALTER COLUMN j SET STORAGE EXTERNAL; +ALTER TABLE vac_truncate_off_toast_on ALTER COLUMN j SET STORAGE EXTERNAL; +INSERT INTO vac_truncate_on_toast_off SELECT generate_series(1, 1000), NULL; +INSERT INTO vac_truncate_on_toast_off SELECT generate_series(1000, 1010), repeat('1234567890', 1000); +INSERT INTO vac_truncate_off_toast_on SELECT generate_series(1, 1000), NULL; +INSERT INTO vac_truncate_off_toast_on SELECT generate_series(1000, 1010), repeat('1234567890', 1000); +SELECT pg_relation_size('vac_truncate_on_toast_off') > 0 AS has_data; +SELECT pg_relation_size(reltoastrelid) > 0 AS has_data FROM pg_class + WHERE relname = 'vac_truncate_on_toast_off'; +SELECT pg_relation_size('vac_truncate_off_toast_on') > 0 AS has_data; +SELECT pg_relation_size(reltoastrelid) > 0 AS has_data FROM pg_class + WHERE relname = 'vac_truncate_off_toast_on'; +DELETE FROM vac_truncate_on_toast_off; +DELETE FROM vac_truncate_off_toast_on; +-- TRUNCATE options are retrieved from their respective relations. +-- Do an aggressive VACUUM to prevent page-skipping. +VACUUM (FREEZE, ANALYZE) vac_truncate_on_toast_off, vac_truncate_off_toast_on; +SELECT pg_relation_size(reltoastrelid) > 0 AS has_data FROM pg_class + WHERE relname = 'vac_truncate_on_toast_off'; +SELECT pg_relation_size('vac_truncate_off_toast_on') > 0 AS has_data; +DROP TABLE vac_truncate_on_toast_off; +DROP TABLE vac_truncate_off_toast_on; diff --git a/contrib/pgstattuple/expected/pgstattuple.out b/contrib/pgstattuple/expected/pgstattuple.out index 9176dc98b6a9..f0bdf7717e30 100644 --- a/contrib/pgstattuple/expected/pgstattuple.out +++ b/contrib/pgstattuple/expected/pgstattuple.out @@ -303,3 +303,50 @@ drop view test_view; drop foreign table test_foreign_table; drop server dummy_server; drop foreign data wrapper dummy; +-- INDEX_CLEANUP option with VACUUM of more than 1 relation. +CREATE TABLE vac_index_cleanup_on_toast_off(i int primary key, j text) WITH + (autovacuum_enabled=false, vacuum_index_cleanup=true, toast.vacuum_index_cleanup=false); +CREATE TABLE vac_index_cleanup_off_toast_on(i int primary key, j text) + WITH (autovacuum_enabled=false, vacuum_index_cleanup=false, toast.vacuum_index_cleanup=true); +-- EXTERNAL to force data on TOAST table, uncompressed. +ALTER TABLE vac_index_cleanup_on_toast_off ALTER COLUMN j SET STORAGE EXTERNAL; +ALTER TABLE vac_index_cleanup_off_toast_on ALTER COLUMN j SET STORAGE EXTERNAL; +INSERT INTO vac_index_cleanup_on_toast_off SELECT generate_series(1, 1000), NULL; +INSERT INTO vac_index_cleanup_on_toast_off SELECT generate_series(1001, 1010), repeat('1234567890', 10000); +INSERT INTO vac_index_cleanup_off_toast_on SELECT generate_series(1, 1000), NULL; +INSERT INTO vac_index_cleanup_off_toast_on SELECT generate_series(1001, 1010), repeat('1234567890', 10000); +DELETE FROM vac_index_cleanup_on_toast_off; +DELETE FROM vac_index_cleanup_off_toast_on; +-- Do an aggressive VACUUM to prevent page-skipping +VACUUM (FREEZE, ANALYZE) vac_index_cleanup_on_toast_off, vac_index_cleanup_off_toast_on; +-- Check cleanup state of main table indexes +SELECT deleted_pages > 0 AS has_del_pages FROM pgstatindex('vac_index_cleanup_off_toast_on_pkey'); + has_del_pages +--------------- + f +(1 row) + +SELECT deleted_pages > 0 AS has_del_pages FROM pgstatindex('vac_index_cleanup_on_toast_off_pkey'); + has_del_pages +--------------- + t +(1 row) + +-- Check cleanup state of TOAST indexes +WITH index_data AS ( + SELECT indexrelid::regclass AS indname, c.relname AS tabname FROM pg_class AS c, pg_index AS i + WHERE c.reltoastrelid = i.indrelid AND + c.relname IN ('vac_index_cleanup_off_toast_on', + 'vac_index_cleanup_on_toast_off') +) +SELECT tabname, deleted_pages > 0 AS has_del_pages + FROM index_data, pgstatindex(index_data.indname) + ORDER BY tabname COLLATE "C"; + tabname | has_del_pages +--------------------------------+--------------- + vac_index_cleanup_off_toast_on | t + vac_index_cleanup_on_toast_off | f +(2 rows) + +DROP TABLE vac_index_cleanup_on_toast_off; +DROP TABLE vac_index_cleanup_off_toast_on; diff --git a/contrib/pgstattuple/sql/pgstattuple.sql b/contrib/pgstattuple/sql/pgstattuple.sql index 7e72c567a064..9a0d284f9b14 100644 --- a/contrib/pgstattuple/sql/pgstattuple.sql +++ b/contrib/pgstattuple/sql/pgstattuple.sql @@ -136,3 +136,35 @@ drop view test_view; drop foreign table test_foreign_table; drop server dummy_server; drop foreign data wrapper dummy; + +-- INDEX_CLEANUP option with VACUUM of more than 1 relation. +CREATE TABLE vac_index_cleanup_on_toast_off(i int primary key, j text) WITH + (autovacuum_enabled=false, vacuum_index_cleanup=true, toast.vacuum_index_cleanup=false); +CREATE TABLE vac_index_cleanup_off_toast_on(i int primary key, j text) + WITH (autovacuum_enabled=false, vacuum_index_cleanup=false, toast.vacuum_index_cleanup=true); +-- EXTERNAL to force data on TOAST table, uncompressed. +ALTER TABLE vac_index_cleanup_on_toast_off ALTER COLUMN j SET STORAGE EXTERNAL; +ALTER TABLE vac_index_cleanup_off_toast_on ALTER COLUMN j SET STORAGE EXTERNAL; +INSERT INTO vac_index_cleanup_on_toast_off SELECT generate_series(1, 1000), NULL; +INSERT INTO vac_index_cleanup_on_toast_off SELECT generate_series(1001, 1010), repeat('1234567890', 10000); +INSERT INTO vac_index_cleanup_off_toast_on SELECT generate_series(1, 1000), NULL; +INSERT INTO vac_index_cleanup_off_toast_on SELECT generate_series(1001, 1010), repeat('1234567890', 10000); +DELETE FROM vac_index_cleanup_on_toast_off; +DELETE FROM vac_index_cleanup_off_toast_on; +-- Do an aggressive VACUUM to prevent page-skipping +VACUUM (FREEZE, ANALYZE) vac_index_cleanup_on_toast_off, vac_index_cleanup_off_toast_on; +-- Check cleanup state of main table indexes +SELECT deleted_pages > 0 AS has_del_pages FROM pgstatindex('vac_index_cleanup_off_toast_on_pkey'); +SELECT deleted_pages > 0 AS has_del_pages FROM pgstatindex('vac_index_cleanup_on_toast_off_pkey'); +-- Check cleanup state of TOAST indexes +WITH index_data AS ( + SELECT indexrelid::regclass AS indname, c.relname AS tabname FROM pg_class AS c, pg_index AS i + WHERE c.reltoastrelid = i.indrelid AND + c.relname IN ('vac_index_cleanup_off_toast_on', + 'vac_index_cleanup_on_toast_off') +) +SELECT tabname, deleted_pages > 0 AS has_del_pages + FROM index_data, pgstatindex(index_data.indname) + ORDER BY tabname COLLATE "C"; +DROP TABLE vac_index_cleanup_on_toast_off; +DROP TABLE vac_index_cleanup_off_toast_on;