vacuum_opts_tests.txt

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;