v4-0001-Avoid-scribbling-VACUUM-options.patch

From 2f2390c05725695d2e2d60845e5146710926fbbc Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Tue, 24 Jun 2025 10:06:09 +0900
Subject: [PATCH v4 1/2] Avoid scribbling VACUUM options

Blah, this is the basic patch that goes through all the back branches,
with tests.

Author: Nathan B.
Backpatch-through: 13
---
 src/backend/commands/vacuum.c                | 20 ++++--
 src/test/regress/expected/vacuum.out         | 71 ++++++++++++++++++++
 src/test/regress/sql/vacuum.sql              | 32 +++++++++
 contrib/pgstattuple/expected/pgstattuple.out | 47 +++++++++++++
 contrib/pgstattuple/sql/pgstattuple.sql      | 32 +++++++++
 5 files changed, 198 insertions(+), 4 deletions(-)

diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 33a33bf6b1cf..a43f090ee178 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -634,7 +634,15 @@ vacuum(List *relations, VacuumParams *params, BufferAccessStrategy bstrategy,
 
 			if (params->options & VACOPT_VACUUM)
 			{
-				if (!vacuum_rel(vrel->oid, vrel->relation, params, bstrategy))
+				VacuumParams params_copy;
+
+				/*
+				 * vacuum_rel() scribbles on the parameters, so give it a copy
+				 * to avoid affecting other relations.
+				 */
+				memcpy(&params_copy, params, sizeof(VacuumParams));
+
+				if (!vacuum_rel(vrel->oid, vrel->relation, &params_copy, bstrategy))
 					continue;
 			}
 
@@ -2008,9 +2016,16 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params,
 	Oid			save_userid;
 	int			save_sec_context;
 	int			save_nestlevel;
+	VacuumParams toast_vacuum_params;
 
 	Assert(params != NULL);
 
+	/*
+	 * This function scribbles on the parameters, so make a copy early to
+	 * avoid affecting the TOAST table (if we do end up recursing to it).
+	 */
+	memcpy(&toast_vacuum_params, params, sizeof(VacuumParams));
+
 	/* Begin a transaction for vacuuming this relation */
 	StartTransactionCommand();
 
@@ -2299,15 +2314,12 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params,
 	 */
 	if (toast_relid != InvalidOid)
 	{
-		VacuumParams toast_vacuum_params;
-
 		/*
 		 * Force VACOPT_PROCESS_MAIN so vacuum_rel() processes it.  Likewise,
 		 * set toast_parent so that the privilege checks are done on the main
 		 * relation.  NB: This is only safe to do because we hold a session
 		 * lock on the main relation that prevents concurrent deletion.
 		 */
-		memcpy(&toast_vacuum_params, params, sizeof(VacuumParams));
 		toast_vacuum_params.options |= VACOPT_PROCESS_MAIN;
 		toast_vacuum_params.toast_parent = relid;
 
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 0abcc99989e0..f403ac190601 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -686,3 +686,74 @@ 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('vac_truncate_on_toast_off') > 0 AS has_data;
+ has_data 
+----------
+ f
+(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 
+----------
+ f
+(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..234753f4e1a1 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -495,3 +495,35 @@ 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('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';
+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;
-- 
2.50.0