Subject: [PATCH v4 1/1] add PROCESS_MAIN to VACUUM

From: Nathan Bossart <[email-masked]>
Date: Thu, 29 Dec 2022 15:31:49 -0800

---
 doc/src/sgml/ref/vacuum.sgml         | 13 ++++++
 doc/src/sgml/ref/vacuumdb.sgml       | 15 +++++++
 src/backend/commands/vacuum.c        | 28 ++++++++++---
 src/backend/postmaster/autovacuum.c  |  4 +-
 src/bin/psql/tab-complete.c          |  4 +-
 src/bin/scripts/t/100_vacuumdb.pl    |  7 ++++
 src/bin/scripts/vacuumdb.c           | 24 +++++++++++
 src/include/commands/vacuum.h        |  9 +++--
 src/test/regress/expected/vacuum.out | 60 ++++++++++++++++++++++++++++
 src/test/regress/sql/vacuum.sql      | 24 +++++++++++
 10 files changed, 175 insertions(+), 13 deletions(-)

diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 545b23b54f..b6d30b5764 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -33,6 +33,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
     DISABLE_PAGE_SKIPPING [ <replaceable class="parameter">boolean</replaceable> ]
     SKIP_LOCKED [ <replaceable class="parameter">boolean</replaceable> ]
     INDEX_CLEANUP { AUTO | ON | OFF }
+    PROCESS_MAIN [ <replaceable class="parameter">boolean</replaceable> ]
     PROCESS_TOAST [ <replaceable class="parameter">boolean</replaceable> ]
     TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
     PARALLEL <replaceable class="parameter">integer</replaceable>
@@ -238,6 +239,18 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>PROCESS_MAIN</literal></term>
+    <listitem>
+     <para>
+      Specifies that <command>VACUUM</command> should attempt to process the
+      main relation. This is usually the desired behavior and is the default.
+      Setting this option to false may be useful when it is only necessary to
+      vacuum a relation's corresponding <literal>TOAST</literal> table.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>PROCESS_TOAST</literal></term>
     <listitem>
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 841aced3bd..74bac2d4ba 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -317,6 +317,21 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--no-process-main</option></term>
+      <listitem>
+       <para>
+        Skip the main relation.
+       </para>
+       <note>
+        <para>
+         This option is only available for servers running
+         <productname>PostgreSQL</productname> 16 and later.
+        </para>
+       </note>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--no-process-toast</option></term>
       <listitem>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index aa79d9de4d..88ac1df2b5 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -115,6 +115,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 	bool		freeze = false;
 	bool		full = false;
 	bool		disable_page_skipping = false;
+	bool		process_main = true;
 	bool		process_toast = true;
 	bool		skip_database_stats = false;
 	bool		only_database_stats = false;
@@ -168,6 +169,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 					params.index_cleanup = get_vacoptval_from_boolean(opt);
 			}
 		}
+		else if (strcmp(opt->defname, "process_main") == 0)
+			process_main = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "process_toast") == 0)
 			process_toast = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "truncate") == 0)
@@ -226,7 +229,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0) |
 		(process_toast ? VACOPT_PROCESS_TOAST : 0) |
 		(skip_database_stats ? VACOPT_SKIP_DATABASE_STATS : 0) |
-		(only_database_stats ? VACOPT_ONLY_DATABASE_STATS : 0);
+		(only_database_stats ? VACOPT_ONLY_DATABASE_STATS : 0) |
+		(process_main ? VACOPT_PROCESS_MAIN : 0);
 
 	/* sanity checks on options */
 	Assert(params.options & (VACOPT_VACUUM | VACOPT_ANALYZE));
@@ -367,9 +371,10 @@ vacuum(List *relations, VacuumParams *params,
 			ereport(ERROR,
 					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 					 errmsg("ONLY_DATABASE_STATS cannot be specified with a list of tables")));
-		/* don't require people to turn off PROCESS_TOAST explicitly */
+		/* don't require people to turn off PROCESS_TOAST/MAIN explicitly */
 		if (params->options & ~(VACOPT_VACUUM |
 								VACOPT_VERBOSE |
+								VACOPT_PROCESS_MAIN |
 								VACOPT_PROCESS_TOAST |
 								VACOPT_ONLY_DATABASE_STATS))
 			ereport(ERROR,
@@ -2031,10 +2036,12 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params, bool skip_privs)
 	/*
 	 * Remember the relation's TOAST relation for later, if the caller asked
 	 * us to process it.  In VACUUM FULL, though, the toast table is
-	 * automatically rebuilt by cluster_rel so we shouldn't recurse to it.
+	 * automatically rebuilt by cluster_rel so we shouldn't recurse to it
+	 * unless PROCESS_MAIN is disabled.
 	 */
 	if ((params->options & VACOPT_PROCESS_TOAST) != 0 &&
-		(params->options & VACOPT_FULL) == 0)
+		((params->options & VACOPT_FULL) == 0 ||
+		 (params->options & VACOPT_PROCESS_MAIN) == 0))
 		toast_relid = rel->rd_rel->reltoastrelid;
 	else
 		toast_relid = InvalidOid;
@@ -2053,7 +2060,8 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params, bool skip_privs)
 	/*
 	 * Do the actual work --- either FULL or "lazy" vacuum
 	 */
-	if (params->options & VACOPT_FULL)
+	if (params->options & VACOPT_FULL &&
+		params->options & VACOPT_PROCESS_MAIN)
 	{
 		ClusterParams cluster_params = {0};
 
@@ -2067,7 +2075,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params, bool skip_privs)
 		/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
 		cluster_rel(relid, InvalidOid, &cluster_params);
 	}
-	else
+	else if (params->options & VACOPT_PROCESS_MAIN)
 		table_relation_vacuum(rel, params, vac_strategy);
 
 	/* Roll back any GUC changes executed by index functions */
@@ -2094,7 +2102,15 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params, bool skip_privs)
 	 * totally unimportant for toast relations.
 	 */
 	if (toast_relid != InvalidOid)
+	{
+		/* we force VACOPT_PROCESS_MAIN so vacuum_rel() processes it */
+		bool force_opt = ((params->options & VACOPT_PROCESS_MAIN) == 0);
+
+		params->options |= VACOPT_PROCESS_MAIN;
 		vacuum_rel(toast_relid, NULL, params, true);
+		if (force_opt)
+			params->options &= ~VACOPT_PROCESS_MAIN;
+	}
 
 	/*
 	 * Now release the session-level lock on the main table.
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index ff6149a179..c0e2e00a7e 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2860,7 +2860,9 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 		 * skip vac_update_datfrozenxid(); we'll do that separately.
 		 */
 		tab->at_params.options =
-			(dovacuum ? (VACOPT_VACUUM | VACOPT_SKIP_DATABASE_STATS) : 0) |
+			(dovacuum ? (VACOPT_VACUUM |
+						 VACOPT_PROCESS_MAIN |
+						 VACOPT_SKIP_DATABASE_STATS) : 0) |
 			(doanalyze ? VACOPT_ANALYZE : 0) |
 			(!wraparound ? VACOPT_SKIP_LOCKED : 0);
 
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 5e1882eaea..8f12af799b 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -4618,10 +4618,10 @@ psql_completion(const char *text, int start, int end)
 		if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
 			COMPLETE_WITH("FULL", "FREEZE", "ANALYZE", "VERBOSE",
 						  "DISABLE_PAGE_SKIPPING", "SKIP_LOCKED",
-						  "INDEX_CLEANUP", "PROCESS_TOAST",
+						  "INDEX_CLEANUP", "PROCESS_MAIN", "PROCESS_TOAST",
 						  "TRUNCATE", "PARALLEL", "SKIP_DATABASE_STATS",
 						  "ONLY_DATABASE_STATS");
-		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|PROCESS_TOAST|TRUNCATE|SKIP_DATABASE_STATS|ONLY_DATABASE_STATS"))
+		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|PROCESS_MAIN|PROCESS_TOAST|TRUNCATE|SKIP_DATABASE_STATS|ONLY_DATABASE_STATS"))
 			COMPLETE_WITH("ON", "OFF");
 		else if (TailMatches("INDEX_CLEANUP"))
 			COMPLETE_WITH("AUTO", "ON", "OFF");
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index 3cfbaaec0d..46101899ae 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -65,6 +65,13 @@ $node->issues_sql_like(
 $node->command_fails(
 	[ 'vacuumdb', '--analyze-only', '--no-truncate', 'postgres' ],
 	'--analyze-only and --no-truncate specified together');
+$node->issues_sql_like(
+	[ 'vacuumdb', '--no-process-main', 'postgres' ],
+	qr/statement: VACUUM \(PROCESS_MAIN FALSE, SKIP_DATABASE_STATS\).*;/,
+	'vacuumdb --no-process-main');
+$node->command_fails(
+	[ 'vacuumdb', '--analyze-only', '--no-process-main', 'postgres' ],
+	'--analyze-only and --no-process_main specified together');
 $node->issues_sql_like(
 	[ 'vacuumdb', '--no-process-toast', 'postgres' ],
 	qr/statement: VACUUM \(PROCESS_TOAST FALSE, SKIP_DATABASE_STATS\).*;/,
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 58b894216b..39be265b5b 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -43,6 +43,7 @@ typedef struct vacuumingOptions
 	bool		no_index_cleanup;
 	bool		force_index_cleanup;
 	bool		do_truncate;
+	bool		process_main;
 	bool		process_toast;
 	bool		skip_database_stats;
 } vacuumingOptions;
@@ -121,6 +122,7 @@ main(int argc, char *argv[])
 		{"force-index-cleanup", no_argument, NULL, 9},
 		{"no-truncate", no_argument, NULL, 10},
 		{"no-process-toast", no_argument, NULL, 11},
+		{"no-process-main", no_argument, NULL, 12},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -148,6 +150,7 @@ main(int argc, char *argv[])
 	vacopts.no_index_cleanup = false;
 	vacopts.force_index_cleanup = false;
 	vacopts.do_truncate = true;
+	vacopts.process_main = true;
 	vacopts.process_toast = true;
 
 	pg_logging_init(argv[0]);
@@ -260,6 +263,9 @@ main(int argc, char *argv[])
 			case 11:
 				vacopts.process_toast = false;
 				break;
+			case 12:
+				vacopts.process_main = false;
+				break;
 			default:
 				/* getopt_long already emitted a complaint */
 				pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -312,6 +318,9 @@ main(int argc, char *argv[])
 		if (!vacopts.do_truncate)
 			pg_fatal("cannot use the \"%s\" option when performing only analyze",
 					 "no-truncate");
+		if (!vacopts.process_main)
+			pg_fatal("cannot use the \"%s\" option when performing only analyze",
+					 "no-process-main");
 		if (!vacopts.process_toast)
 			pg_fatal("cannot use the \"%s\" option when performing only analyze",
 					 "no-process-toast");
@@ -508,6 +517,13 @@ vacuum_one_database(ConnParams *cparams,
 				 "no-truncate", "12");
 	}
 
+	if (!vacopts->process_main && PQserverVersion(conn) < 160000)
+	{
+		PQfinish(conn);
+		pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+				 "no-process-main", "16");
+	}
+
 	if (!vacopts->process_toast && PQserverVersion(conn) < 140000)
 	{
 		PQfinish(conn);
@@ -976,6 +992,13 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
 				appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
 				sep = comma;
 			}
+			if (!vacopts->process_main)
+			{
+				/* PROCESS_MAIN is supported since v16 */
+				Assert(serverVersion >= 160000);
+				appendPQExpBuffer(sql, "%sPROCESS_MAIN FALSE", sep);
+				sep = comma;
+			}
 			if (!vacopts->process_toast)
 			{
 				/* PROCESS_TOAST is supported since v14 */
@@ -1090,6 +1113,7 @@ help(const char *progname)
 	printf(_("      --min-mxid-age=MXID_AGE     minimum multixact ID age of tables to vacuum\n"));
 	printf(_("      --min-xid-age=XID_AGE       minimum transaction ID age of tables to vacuum\n"));
 	printf(_("      --no-index-cleanup          don't remove index entries that point to dead tuples\n"));
+	printf(_("      --no-process-main           skip the main relation\n"));
 	printf(_("      --no-process-toast          skip the TOAST table associated with the table to vacuum\n"));
 	printf(_("      --no-truncate               don't truncate empty pages at the end of the table\n"));
 	printf(_("  -n, --schema=PATTERN            vacuum tables in the specified schema(s) only\n"));
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 689dbb7702..bdfd96cfec 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -186,10 +186,11 @@ typedef struct VacAttrStats
 #define VACOPT_FREEZE 0x08		/* FREEZE option */
 #define VACOPT_FULL 0x10		/* FULL (non-concurrent) vacuum */
 #define VACOPT_SKIP_LOCKED 0x20 /* skip if cannot get lock */
-#define VACOPT_PROCESS_TOAST 0x40	/* process the TOAST table, if any */
-#define VACOPT_DISABLE_PAGE_SKIPPING 0x80	/* don't skip any pages */
-#define VACOPT_SKIP_DATABASE_STATS 0x100	/* skip vac_update_datfrozenxid() */
-#define VACOPT_ONLY_DATABASE_STATS 0x200	/* only vac_update_datfrozenxid() */
+#define VACOPT_PROCESS_MAIN 0x40	/* process main relation */
+#define VACOPT_PROCESS_TOAST 0x80	/* process the TOAST table, if any */
+#define VACOPT_DISABLE_PAGE_SKIPPING 0x100	/* don't skip any pages */
+#define VACOPT_SKIP_DATABASE_STATS 0x200	/* skip vac_update_datfrozenxid() */
+#define VACOPT_ONLY_DATABASE_STATS 0x400	/* only vac_update_datfrozenxid() */
 
 /*
  * Values used by index_cleanup and truncate params.
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 458adee7f8..17fba0fe06 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -276,10 +276,29 @@ RESET default_transaction_isolation;
 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
+CREATE VIEW vactst_vacuum_counts AS
+	SELECT left(s.relname, 8), s.vacuum_count
+	FROM pg_stat_all_tables s
+	LEFT JOIN pg_class c ON s.relid = c.reltoastrelid
+	WHERE c.relname = 'vactst' OR s.relname = 'vactst'
+	ORDER BY s.relname;
 -- PROCESS_TOAST option
 ALTER TABLE vactst ADD COLUMN t TEXT;
 ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+SELECT pg_stat_reset();
+ pg_stat_reset 
+---------------
+ 
+(1 row)
+
 VACUUM (PROCESS_TOAST FALSE) vactst;
+SELECT * FROM vactst_vacuum_counts;
+   left   | vacuum_count 
+----------+--------------
+ pg_toast |            0
+ vactst   |            1
+(2 rows)
+
 VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
 ERROR:  PROCESS_TOAST required with VACUUM FULL
 -- SKIP_DATABASE_STATS option
@@ -288,6 +307,47 @@ VACUUM (SKIP_DATABASE_STATS) vactst;
 VACUUM (ONLY_DATABASE_STATS);
 VACUUM (ONLY_DATABASE_STATS) vactst;  -- error
 ERROR:  ONLY_DATABASE_STATS cannot be specified with a list of tables
+-- PROCESS_MAIN option
+SELECT pg_stat_reset();
+ pg_stat_reset 
+---------------
+ 
+(1 row)
+
+VACUUM (PROCESS_MAIN FALSE) vactst;
+SELECT * FROM vactst_vacuum_counts;
+   left   | vacuum_count 
+----------+--------------
+ pg_toast |            1
+ vactst   |            0
+(2 rows)
+
+VACUUM (PROCESS_MAIN FALSE, PROCESS_TOAST FALSE) vactst;
+SELECT * FROM vactst_vacuum_counts;
+   left   | vacuum_count 
+----------+--------------
+ pg_toast |            1
+ vactst   |            0
+(2 rows)
+
+SELECT relfilenode AS main_filenode FROM pg_class WHERE relname = 'vactst' \gset
+SELECT t.relfilenode AS toast_filenode FROM pg_class c, pg_class t
+	WHERE c.reltoastrelid = t.oid AND c.relname = 'vactst' \gset
+VACUUM (PROCESS_MAIN FALSE, FULL) vactst;
+SELECT relfilenode = :main_filenode FROM pg_class WHERE relname = 'vactst';
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT t.relfilenode = :toast_filenode FROM pg_class c, pg_class t
+	WHERE c.reltoastrelid = t.oid AND c.relname = 'vactst';
+ ?column? 
+----------
+ f
+(1 row)
+
+DROP VIEW vactst_vacuum_counts;
 DROP TABLE vaccluster;
 DROP TABLE vactst;
 DROP TABLE vacparted;
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 9da8f3e830..2985c1a99b 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -231,10 +231,19 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
 
+CREATE VIEW vactst_vacuum_counts AS
+	SELECT left(s.relname, 8), s.vacuum_count
+	FROM pg_stat_all_tables s
+	LEFT JOIN pg_class c ON s.relid = c.reltoastrelid
+	WHERE c.relname = 'vactst' OR s.relname = 'vactst'
+	ORDER BY s.relname;
+
 -- PROCESS_TOAST option
 ALTER TABLE vactst ADD COLUMN t TEXT;
 ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+SELECT pg_stat_reset();
 VACUUM (PROCESS_TOAST FALSE) vactst;
+SELECT * FROM vactst_vacuum_counts;
 VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
 
 -- SKIP_DATABASE_STATS option
@@ -244,6 +253,21 @@ VACUUM (SKIP_DATABASE_STATS) vactst;
 VACUUM (ONLY_DATABASE_STATS);
 VACUUM (ONLY_DATABASE_STATS) vactst;  -- error
 
+-- PROCESS_MAIN option
+SELECT pg_stat_reset();
+VACUUM (PROCESS_MAIN FALSE) vactst;
+SELECT * FROM vactst_vacuum_counts;
+VACUUM (PROCESS_MAIN FALSE, PROCESS_TOAST FALSE) vactst;
+SELECT * FROM vactst_vacuum_counts;
+SELECT relfilenode AS main_filenode FROM pg_class WHERE relname = 'vactst' \gset
+SELECT t.relfilenode AS toast_filenode FROM pg_class c, pg_class t
+	WHERE c.reltoastrelid = t.oid AND c.relname = 'vactst' \gset
+VACUUM (PROCESS_MAIN FALSE, FULL) vactst;
+SELECT relfilenode = :main_filenode FROM pg_class WHERE relname = 'vactst';
+SELECT t.relfilenode = :toast_filenode FROM pg_class c, pg_class t
+	WHERE c.reltoastrelid = t.oid AND c.relname = 'vactst';
+
+DROP VIEW vactst_vacuum_counts;
 DROP TABLE vaccluster;
 DROP TABLE vactst;
 DROP TABLE vacparted;
-- 
2.25.1

--FL5UXtIhxfXey3p5--