Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

michael@paquier.xyz

almost 6 years ago

In reply to: Bossart, Nathan (#1)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On Tue, Jan 21, 2020 at 09:21:46PM +0000, Bossart, Nathan wrote:

I've attached a patch for a couple of new options for VACUUM:
MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP. The motive
behind these options is to allow table owners to easily vacuum only
the TOAST table or only the main relation. This is especially useful
for TOAST tables since roles do not have access to the pg_toast schema
by default and some users may find it difficult to discover the name
of a relation's TOAST table. Next, I will explain a couple of the
main design decisions.

So that's similar to the autovacuum reloptions, but to be able to
enforce one policy or another manually. Any issues with autovacuum
not able to keep up the bloat pace and where you need to issue manual
VACUUMs in periods of low activity, like nightly VACUUMs?

I chose to call the option SECONDARY_RELATION_CLEANUP instead of
something like TOAST_TABLE_CLEANUP for two reasons. First, other
types of secondary relations may be added in the future, and it may be
convenient to put them under the umbrella of this option. Second, it
seemed like it could be outside of the project's style to use the name
of internal storage mechanisms in a user-facing VACUUM option.
However, I am not wedded to the chosen name, as I am sure there are
good arguments for something like TOAST_TABLE_CLEANUP.

If other types of relations are added in the future, wouldn't it make
sense to have one switch for each one of those types then? A relation
could have a toast relation associated to it, as much as a foo
relation or a hoge relation, in which case SECONDARY brings little
control.

I chose to implement MAIN_RELATION_CLEANUP within vacuum_rel() instead
of expand_vacuum_rel()/get_all_vacuum_rels(). This allows us to reuse
most of the existing code with minimal changes, and it avoids adding
complexity to the lookups and ownership checks in expand_vacuum_rel()
and get_all_vacuum_rels() (especially the partition lookup logic).
The main tradeoffs of this approach are that we will still create a
transaction for the main relation and that we will still lock the main
relation.

Yeah, likely we should not make things more confusing in this area.
This was tricky enough to deal with with the recent VACUUM
refactoring for multiple relations.

I reused the existing VACOPT_SKIPTOAST option to implement
SECONDARY_RELATION_CLEANUP. This option is currently only used for
autovacuum.

My take would be to rename this option, and reuse it for consistency.

I chose to disallow disabling both *_RELATION_CLEANUP options
together, as this would essentially cause the VACUUM command to take
no action.

My first reaction is why? Agreed that it is a bit crazy to combine
both options, but if you add the argument related to more relation
types like toast..
--
Michael

bossartn@amazon.com

almost 6 years ago

In reply to: Vik Fearing (#2)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On 1/21/20, 1:39 PM, "Vik Fearing" <vik.fearing@2ndquadrant.com> wrote:

On 21/01/2020 22:21, Bossart, Nathan wrote:

I've attached a patch for a couple of new options for VACUUM:
MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP. The motive
behind these options is to allow table owners to easily vacuum only
the TOAST table or only the main relation. This is especially useful
for TOAST tables since roles do not have access to the pg_toast schema
by default and some users may find it difficult to discover the name
of a relation's TOAST table.

Could you explain why one would want to do this? Autovacuum will
already deal with the tables separately as needed, but I don't see when
a manual vacuum would want to make this distinction.

The main use case I'm targeting is when the level of bloat or
transaction ages of a relation and its TOAST table have significantly
diverged. In these scenarios, it could be beneficial to be able to
vacuum just one or the other, especially if the tables are large.

Nathan

bossartn@amazon.com

almost 6 years ago

In reply to: Michael Paquier (#3)

1 attachment(s)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

Hi Michael,

Thanks for taking a look.

On 1/21/20, 9:02 PM, "Michael Paquier" <michael@paquier.xyz> wrote:

On Tue, Jan 21, 2020 at 09:21:46PM +0000, Bossart, Nathan wrote:

I've attached a patch for a couple of new options for VACUUM:
MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP. The motive
behind these options is to allow table owners to easily vacuum only
the TOAST table or only the main relation. This is especially useful
for TOAST tables since roles do not have access to the pg_toast schema
by default and some users may find it difficult to discover the name
of a relation's TOAST table. Next, I will explain a couple of the
main design decisions.

So that's similar to the autovacuum reloptions, but to be able to
enforce one policy or another manually. Any issues with autovacuum
not able to keep up the bloat pace and where you need to issue manual
VACUUMs in periods of low activity, like nightly VACUUMs?

There have been a couple of occasions where I have seen the TOAST
table become the most bloated part of the relation. When this
happens, it would be handy to be able to avoid scanning the heap and
indexes. I am not aware of any concrete problems with autovacuum
other than needing to tune the parameters for certain workloads.

I chose to call the option SECONDARY_RELATION_CLEANUP instead of
something like TOAST_TABLE_CLEANUP for two reasons. First, other
types of secondary relations may be added in the future, and it may be
convenient to put them under the umbrella of this option. Second, it
seemed like it could be outside of the project's style to use the name
of internal storage mechanisms in a user-facing VACUUM option.
However, I am not wedded to the chosen name, as I am sure there are
good arguments for something like TOAST_TABLE_CLEANUP.

If other types of relations are added in the future, wouldn't it make
sense to have one switch for each one of those types then? A relation
could have a toast relation associated to it, as much as a foo
relation or a hoge relation, in which case SECONDARY brings little
control.

This is a good point. I've renamed the option to TOAST_TABLE_CLEANUP
in v2.

I chose to implement MAIN_RELATION_CLEANUP within vacuum_rel() instead
of expand_vacuum_rel()/get_all_vacuum_rels(). This allows us to reuse
most of the existing code with minimal changes, and it avoids adding
complexity to the lookups and ownership checks in expand_vacuum_rel()
and get_all_vacuum_rels() (especially the partition lookup logic).
The main tradeoffs of this approach are that we will still create a
transaction for the main relation and that we will still lock the main
relation.

Yeah, likely we should not make things more confusing in this area.
This was tricky enough to deal with with the recent VACUUM
refactoring for multiple relations.

Finding a way to avoid the lock on the main relation could be a future
improvement, as that would allow you to manually vacuum both the main
relation and its TOAST table in parallel.

I reused the existing VACOPT_SKIPTOAST option to implement
SECONDARY_RELATION_CLEANUP. This option is currently only used for
autovacuum.

My take would be to rename this option, and reuse it for consistency.

Done.

I chose to disallow disabling both *_RELATION_CLEANUP options
together, as this would essentially cause the VACUUM command to take
no action.

My first reaction is why? Agreed that it is a bit crazy to combine
both options, but if you add the argument related to more relation
types like toast..

Yes, I suppose we have the same problem if you disable
MAIN_RELATION_CLEANUP and the relation has no TOAST table. In any
case, allowing both options to be disabled shouldn't hurt anything.

Nathan

Attachments:

v2-0001-Add-MAIN_RELATION_CLEANUP-and-TOAST_TABLE_CLEANUP.patchapplication/octet-stream; name=v2-0001-Add-MAIN_RELATION_CLEANUP-and-TOAST_TABLE_CLEANUP.patchDownload

From cc8aa2a0cdc73e296a56b18230911bd172883319 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Fri, 24 Jan 2020 19:48:38 +0000
Subject: [PATCH v2 1/1] Add MAIN_RELATION_CLEANUP and TOAST_TABLE_CLEANUP
 options to VACUUM.

---
 doc/src/sgml/ref/vacuum.sgml         | 30 +++++++++++++
 src/backend/commands/vacuum.c        | 86 ++++++++++++++++++++++++++++--------
 src/backend/postmaster/autovacuum.c  |  2 +-
 src/bin/psql/tab-complete.c          |  5 ++-
 src/include/commands/vacuum.h        |  5 ++-
 src/test/regress/expected/vacuum.out | 11 +++++
 src/test/regress/sql/vacuum.sql      | 10 +++++
 7 files changed, 125 insertions(+), 24 deletions(-)

diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 846056a353..b70c65f4e4 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -33,6 +33,8 @@ 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 [ <replaceable class="parameter">boolean</replaceable> ]
+    MAIN_RELATION_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
+    TOAST_TABLE_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
     TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
     PARALLEL <replaceable class="parameter">integer</replaceable>
 
@@ -210,6 +212,34 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>MAIN_RELATION_CLEANUP</literal></term>
+    <listitem>
+     <para>
+      Specifies that <command>VACUUM</command> should attempt to process the
+      main relation.  This is normally the desired behavior and is the default.
+      Setting this option to false may be useful when it is necessary to only
+      vacuum a relation's corresponding <literal>TOAST</literal> table.  This
+      option cannot be disabled when the <literal>ANALYZE</literal> option is
+      specified.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>TOAST_TABLE_CLEANUP</literal></term>
+    <listitem>
+     <para>
+      Specifies that <command>VACUUM</command> should attempt to process the
+      corresponding <literal>TOAST</literal> table for each relation, if one
+      exists.  This is normally the desired behavior and is the default.
+      Setting this option to false may be useful when it is necessary to only
+      vacuum the main relation.  This option cannot be disabled when the
+      <literal>FULL</literal> option is specified.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>TRUNCATE</literal></term>
     <listitem>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index d625d17bf4..68473e6c2b 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -84,7 +84,10 @@ static void vac_truncate_clog(TransactionId frozenXID,
 							  MultiXactId minMulti,
 							  TransactionId lastSaneFrozenXid,
 							  MultiXactId lastSaneMinMulti);
-static bool vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params);
+static bool vacuum_rel(Oid relid,
+					   RangeVar *relation,
+					   VacuumParams *params,
+					   bool processing_toast_table);
 static double compute_parallel_delay(void);
 static VacOptTernaryValue get_vacopt_ternary_value(DefElem *def);
 
@@ -105,6 +108,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 	bool		full = false;
 	bool		disable_page_skipping = false;
 	bool		parallel_option = false;
+	bool		main_rel_cleanup = true;
+	bool		toast_cleanup = true;
 	ListCell   *lc;
 
 	/* Set default value */
@@ -141,6 +146,10 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 			disable_page_skipping = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "index_cleanup") == 0)
 			params.index_cleanup = get_vacopt_ternary_value(opt);
+		else if (strcmp(opt->defname, "main_relation_cleanup") == 0)
+			main_rel_cleanup = defGetBoolean(opt);
+		else if (strcmp(opt->defname, "toast_table_cleanup") == 0)
+			toast_cleanup = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "truncate") == 0)
 			params.truncate = get_vacopt_ternary_value(opt);
 		else if (strcmp(opt->defname, "parallel") == 0)
@@ -191,13 +200,14 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 		(analyze ? VACOPT_ANALYZE : 0) |
 		(freeze ? VACOPT_FREEZE : 0) |
 		(full ? VACOPT_FULL : 0) |
-		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0);
+		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0) |
+		(main_rel_cleanup ? VACOPT_MAIN_REL_CLEANUP : 0) |
+		(toast_cleanup ? VACOPT_TOAST_CLEANUP : 0);
 
 	/* sanity checks on options */
 	Assert(params.options & (VACOPT_VACUUM | VACOPT_ANALYZE));
 	Assert((params.options & VACOPT_VACUUM) ||
 		   !(params.options & (VACOPT_FULL | VACOPT_FREEZE)));
-	Assert(!(params.options & VACOPT_SKIPTOAST));
 
 	if ((params.options & VACOPT_FULL) && parallel_option)
 		ereport(ERROR,
@@ -320,6 +330,26 @@ vacuum(List *relations, VacuumParams *params,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("VACUUM option DISABLE_PAGE_SKIPPING cannot be used with FULL")));
 
+	/*
+	 * Sanity check TOAST_TABLE_CLEANUP option.
+	 */
+	if ((params->options & VACOPT_FULL) != 0 &&
+		(params->options & VACOPT_TOAST_CLEANUP) == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("VACUUM option TOAST_TABLE_CLEANUP cannot be "
+						"disabled when FULL is specified")));
+
+	/*
+	 * Sanity check MAIN_RELATION_CLEANUP option.
+	 */
+	if ((params->options & VACOPT_ANALYZE) != 0 &&
+		(params->options & VACOPT_MAIN_REL_CLEANUP) == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("VACUUM option MAIN_RELATION_CLEANUP cannot be "
+						"disabled when ANALYZE is specified")));
+
 	/*
 	 * Send info about dead objects to the statistics collector, unless we are
 	 * in autovacuum --- autovacuum.c does this for itself.
@@ -448,7 +478,7 @@ vacuum(List *relations, VacuumParams *params,
 
 			if (params->options & VACOPT_VACUUM)
 			{
-				if (!vacuum_rel(vrel->oid, vrel->relation, params))
+				if (!vacuum_rel(vrel->oid, vrel->relation, params, false))
 					continue;
 			}
 
@@ -1667,7 +1697,10 @@ vac_truncate_clog(TransactionId frozenXID,
  *		At entry and exit, we are not inside a transaction.
  */
 static bool
-vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
+vacuum_rel(Oid relid,
+		   RangeVar *relation,
+		   VacuumParams *params,
+		   bool processing_toast_table)
 {
 	LOCKMODE	lmode;
 	Relation	onerel;
@@ -1676,6 +1709,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	Oid			save_userid;
 	int			save_sec_context;
 	int			save_nestlevel;
+	bool		process_toast;
 
 	Assert(params != NULL);
 
@@ -1843,9 +1877,16 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	/*
 	 * 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 MAIN_RELATION_CLEANUP is disabled.
 	 */
-	if (!(params->options & VACOPT_SKIPTOAST) && !(params->options & VACOPT_FULL))
+	process_toast = (params->options & VACOPT_TOAST_CLEANUP) != 0;
+
+	if (params->options & VACOPT_FULL &&
+		(params->options & VACOPT_MAIN_REL_CLEANUP) != 0)
+		process_toast = false;
+
+	if (process_toast)
 		toast_relid = onerel->rd_rel->reltoastrelid;
 	else
 		toast_relid = InvalidOid;
@@ -1863,23 +1904,30 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 
 	/*
 	 * Do the actual work --- either FULL or "lazy" vacuum
+	 *
+	 * We skip this part if we're processing the main relation and
+	 * MAIN_RELATION_CLEANUP has been disabled.
 	 */
-	if (params->options & VACOPT_FULL)
+	if ((params->options & VACOPT_MAIN_REL_CLEANUP) != 0 ||
+		processing_toast_table)
 	{
-		int			cluster_options = 0;
+		if (params->options & VACOPT_FULL)
+		{
+			int			cluster_options = 0;
 
-		/* close relation before vacuuming, but hold lock until commit */
-		relation_close(onerel, NoLock);
-		onerel = NULL;
+			/* close relation before vacuuming, but hold lock until commit */
+			relation_close(onerel, NoLock);
+			onerel = NULL;
 
-		if ((params->options & VACOPT_VERBOSE) != 0)
-			cluster_options |= CLUOPT_VERBOSE;
+			if ((params->options & VACOPT_VERBOSE) != 0)
+				cluster_options |= CLUOPT_VERBOSE;
 
-		/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
-		cluster_rel(relid, InvalidOid, cluster_options);
+			/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
+			cluster_rel(relid, InvalidOid, cluster_options);
+		}
+		else
+			table_relation_vacuum(onerel, params, vac_strategy);
 	}
-	else
-		table_relation_vacuum(onerel, params, vac_strategy);
 
 	/* Roll back any GUC changes executed by index functions */
 	AtEOXact_GUC(false, save_nestlevel);
@@ -1905,7 +1953,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	 * totally unimportant for toast relations.
 	 */
 	if (toast_relid != InvalidOid)
-		vacuum_rel(toast_relid, NULL, params);
+		vacuum_rel(toast_relid, NULL, params, true);
 
 	/*
 	 * Now release the session-level lock on the master table.
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 6d1f28c327..fc3e276e0b 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2880,7 +2880,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 		tab = palloc(sizeof(autovac_table));
 		tab->at_relid = relid;
 		tab->at_sharedrel = classForm->relisshared;
-		tab->at_params.options = VACOPT_SKIPTOAST |
+		tab->at_params.options = VACOPT_MAIN_REL_CLEANUP |
 			(dovacuum ? VACOPT_VACUUM : 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 dc03fbde13..be3e7184e1 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3649,8 +3649,9 @@ 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", "TRUNCATE", "PARALLEL");
-		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE"))
+						  "INDEX_CLEANUP", "TRUNCATE", "PARALLEL",
+						  "MAIN_RELATION_CLEANUP", "TOAST_TABLE_CLEANUP");
+		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE|MAIN_RELATION_CLEANUP|TOAST_TABLE_CLEANUP"))
 			COMPLETE_WITH("ON", "OFF");
 	}
 	else if (HeadMatches("VACUUM") && TailMatches("("))
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index c27d255d8d..630a6b70de 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -182,8 +182,9 @@ typedef enum VacuumOption
 	VACOPT_FREEZE = 1 << 3,		/* FREEZE option */
 	VACOPT_FULL = 1 << 4,		/* FULL (non-concurrent) vacuum */
 	VACOPT_SKIP_LOCKED = 1 << 5,	/* skip if cannot get lock */
-	VACOPT_SKIPTOAST = 1 << 6,	/* don't process the TOAST table, if any */
-	VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7	/* don't skip any pages */
+	VACOPT_TOAST_CLEANUP = 1 << 6,	/* process TOAST table, if any */
+	VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7,	/* don't skip any pages */
+	VACOPT_MAIN_REL_CLEANUP = 1 << 8	/* process main relation */
 } VacuumOption;
 
 /*
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index f4250a433a..24b0bbdb13 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -250,6 +250,17 @@ RESET default_transaction_isolation;
 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
+-- MAIN_RELATION_CLEANUP and TOAST_TABLE_CLEANUP options
+ALTER TABLE vactst ADD COLUMN t TEXT;
+ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, ANALYZE) vactst;
+ERROR:  VACUUM option MAIN_RELATION_CLEANUP cannot be disabled when ANALYZE is specified
+VACUUM (TOAST_TABLE_CLEANUP FALSE, FULL) vactst;
+ERROR:  VACUUM option TOAST_TABLE_CLEANUP cannot be disabled when FULL is specified
+VACUUM (MAIN_RELATION_CLEANUP FALSE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, FULL) vactst;
 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 cf741f7b11..19607f629e 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -210,6 +210,16 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
 
+-- MAIN_RELATION_CLEANUP and TOAST_TABLE_CLEANUP options
+ALTER TABLE vactst ADD COLUMN t TEXT;
+ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, ANALYZE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE, FULL) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, FULL) vactst;
+
 DROP TABLE vaccluster;
 DROP TABLE vactst;
 DROP TABLE vacparted;
-- 
2.16.5

bossartn@amazon.com

almost 6 years ago

In reply to: Bossart, Nathan (#5)

Re: [UNVERIFIED SENDER] Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On 1/24/20, 1:32 PM, "Bossart, Nathan" <bossartn@amazon.com> wrote:

I chose to disallow disabling both *_RELATION_CLEANUP options
together, as this would essentially cause the VACUUM command to take
no action.

My first reaction is why? Agreed that it is a bit crazy to combine
both options, but if you add the argument related to more relation
types like toast..

Yes, I suppose we have the same problem if you disable
MAIN_RELATION_CLEANUP and the relation has no TOAST table. In any
case, allowing both options to be disabled shouldn't hurt anything.

I've been thinking further in this area, and I'm wondering if it also
makes sense to remove the restriction on ANALYZE with
MAIN_RELATION_CLEANUP disabled. A command like

VACUUM (ANALYZE, MAIN_RELATION_CLEANUP FALSE) test;

could be interpreted as meaning we should vacuum the TOAST table and
analyze the main relation. Since the word "cleanup" is present in the
option name, this might not be too confusing.

Nathan

Import Notes

Resolved by subject fallback

michael@paquier.xyz

almost 6 years ago

In reply to: Bossart, Nathan (#5)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On Fri, Jan 24, 2020 at 09:31:26PM +0000, Bossart, Nathan wrote:

On 1/21/20, 9:02 PM, "Michael Paquier" <michael@paquier.xyz> wrote:

On Tue, Jan 21, 2020 at 09:21:46PM +0000, Bossart, Nathan wrote:

I've attached a patch for a couple of new options for VACUUM:
MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP. The motive
behind these options is to allow table owners to easily vacuum only
the TOAST table or only the main relation. This is especially useful
for TOAST tables since roles do not have access to the pg_toast schema
by default and some users may find it difficult to discover the name
of a relation's TOAST table. Next, I will explain a couple of the
main design decisions.

So that's similar to the autovacuum reloptions, but to be able to
enforce one policy or another manually. Any issues with autovacuum
not able to keep up the bloat pace and where you need to issue manual
VACUUMs in periods of low activity, like nightly VACUUMs?

There have been a couple of occasions where I have seen the TOAST
table become the most bloated part of the relation. When this
happens, it would be handy to be able to avoid scanning the heap and
indexes. I am not aware of any concrete problems with autovacuum
other than needing to tune the parameters for certain workloads.

That's something I have faced as well. I have some applications
around here where toast tables were the most bloated, and the
vacuuming of the main relation ate time, putting more pressure on the
vacuuming of the toast relation. So that's a fair argument in my
opinion.

I chose to implement MAIN_RELATION_CLEANUP within vacuum_rel() instead
of expand_vacuum_rel()/get_all_vacuum_rels(). This allows us to reuse
most of the existing code with minimal changes, and it avoids adding
complexity to the lookups and ownership checks in expand_vacuum_rel()
and get_all_vacuum_rels() (especially the partition lookup logic).
The main tradeoffs of this approach are that we will still create a
transaction for the main relation and that we will still lock the main
relation.

Yeah, likely we should not make things more confusing in this area.
This was tricky enough to deal with with the recent VACUUM
refactoring for multiple relations.

Finding a way to avoid the lock on the main relation could be a future
improvement, as that would allow you to manually vacuum both the main
relation and its TOAST table in parallel.

I am not sure that we actually need that at all, any catalog changes
take a lock on the parent relation first, and that's the conflicts we
are looking at here with a share update exclusive lock.
--
Michael

bossartn@amazon.com

almost 6 years ago

In reply to: Michael Paquier (#7)

1 attachment(s)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On 1/24/20, 2:14 PM, "Bossart, Nathan" <bossartn@amazon.com> wrote:

Yes, I suppose we have the same problem if you disable
MAIN_RELATION_CLEANUP and the relation has no TOAST table. In any
case, allowing both options to be disabled shouldn't hurt anything.

I've been thinking further in this area, and I'm wondering if it also
makes sense to remove the restriction on ANALYZE with
MAIN_RELATION_CLEANUP disabled. A command like

VACUUM (ANALYZE, MAIN_RELATION_CLEANUP FALSE) test;

could be interpreted as meaning we should vacuum the TOAST table and
analyze the main relation. Since the word "cleanup" is present in the
option name, this might not be too confusing.

I've attached v3 of the patch, which removes the restriction on
ANALYZE with MAIN_RELATION_CLEANUP disabled.

Nathan

Attachments:

v3-0001-Add-MAIN_RELATION_CLEANUP-and-TOAST_TABLE_CLEANUP.patchapplication/octet-stream; name=v3-0001-Add-MAIN_RELATION_CLEANUP-and-TOAST_TABLE_CLEANUP.patchDownload

From 4ca75f44068517c444223bf021ef0d7fb33b2c85 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Wed, 5 Feb 2020 21:03:58 +0000
Subject: [PATCH v3 1/1] Add MAIN_RELATION_CLEANUP and TOAST_TABLE_CLEANUP
 options to VACUUM.

---
 doc/src/sgml/ref/vacuum.sgml         | 28 +++++++++++++
 src/backend/commands/vacuum.c        | 76 +++++++++++++++++++++++++++---------
 src/backend/postmaster/autovacuum.c  |  2 +-
 src/bin/psql/tab-complete.c          |  5 ++-
 src/include/commands/vacuum.h        |  5 ++-
 src/test/regress/expected/vacuum.out | 10 +++++
 src/test/regress/sql/vacuum.sql      | 10 +++++
 7 files changed, 112 insertions(+), 24 deletions(-)

diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 846056a353..4b61c646d4 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -33,6 +33,8 @@ 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 [ <replaceable class="parameter">boolean</replaceable> ]
+    MAIN_RELATION_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
+    TOAST_TABLE_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
     TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
     PARALLEL <replaceable class="parameter">integer</replaceable>
 
@@ -210,6 +212,32 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>MAIN_RELATION_CLEANUP</literal></term>
+    <listitem>
+     <para>
+      Specifies that <command>VACUUM</command> should attempt to process the
+      main relation.  This is normally the desired behavior and is the default.
+      Setting this option to false may be useful when it is necessary to only
+      vacuum a relation's corresponding <literal>TOAST</literal> table.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>TOAST_TABLE_CLEANUP</literal></term>
+    <listitem>
+     <para>
+      Specifies that <command>VACUUM</command> should attempt to process the
+      corresponding <literal>TOAST</literal> table for each relation, if one
+      exists.  This is normally the desired behavior and is the default.
+      Setting this option to false may be useful when it is necessary to only
+      vacuum the main relation.  This option cannot be disabled when the
+      <literal>FULL</literal> option is specified.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>TRUNCATE</literal></term>
     <listitem>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index d625d17bf4..770fbaddef 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -84,7 +84,10 @@ static void vac_truncate_clog(TransactionId frozenXID,
 							  MultiXactId minMulti,
 							  TransactionId lastSaneFrozenXid,
 							  MultiXactId lastSaneMinMulti);
-static bool vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params);
+static bool vacuum_rel(Oid relid,
+					   RangeVar *relation,
+					   VacuumParams *params,
+					   bool processing_toast_table);
 static double compute_parallel_delay(void);
 static VacOptTernaryValue get_vacopt_ternary_value(DefElem *def);
 
@@ -105,6 +108,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 	bool		full = false;
 	bool		disable_page_skipping = false;
 	bool		parallel_option = false;
+	bool		main_rel_cleanup = true;
+	bool		toast_cleanup = true;
 	ListCell   *lc;
 
 	/* Set default value */
@@ -141,6 +146,10 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 			disable_page_skipping = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "index_cleanup") == 0)
 			params.index_cleanup = get_vacopt_ternary_value(opt);
+		else if (strcmp(opt->defname, "main_relation_cleanup") == 0)
+			main_rel_cleanup = defGetBoolean(opt);
+		else if (strcmp(opt->defname, "toast_table_cleanup") == 0)
+			toast_cleanup = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "truncate") == 0)
 			params.truncate = get_vacopt_ternary_value(opt);
 		else if (strcmp(opt->defname, "parallel") == 0)
@@ -191,13 +200,14 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 		(analyze ? VACOPT_ANALYZE : 0) |
 		(freeze ? VACOPT_FREEZE : 0) |
 		(full ? VACOPT_FULL : 0) |
-		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0);
+		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0) |
+		(main_rel_cleanup ? VACOPT_MAIN_REL_CLEANUP : 0) |
+		(toast_cleanup ? VACOPT_TOAST_CLEANUP : 0);
 
 	/* sanity checks on options */
 	Assert(params.options & (VACOPT_VACUUM | VACOPT_ANALYZE));
 	Assert((params.options & VACOPT_VACUUM) ||
 		   !(params.options & (VACOPT_FULL | VACOPT_FREEZE)));
-	Assert(!(params.options & VACOPT_SKIPTOAST));
 
 	if ((params.options & VACOPT_FULL) && parallel_option)
 		ereport(ERROR,
@@ -320,6 +330,16 @@ vacuum(List *relations, VacuumParams *params,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("VACUUM option DISABLE_PAGE_SKIPPING cannot be used with FULL")));
 
+	/*
+	 * Sanity check TOAST_TABLE_CLEANUP option.
+	 */
+	if ((params->options & VACOPT_FULL) != 0 &&
+		(params->options & VACOPT_TOAST_CLEANUP) == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("VACUUM option TOAST_TABLE_CLEANUP cannot be "
+						"disabled when FULL is specified")));
+
 	/*
 	 * Send info about dead objects to the statistics collector, unless we are
 	 * in autovacuum --- autovacuum.c does this for itself.
@@ -448,7 +468,7 @@ vacuum(List *relations, VacuumParams *params,
 
 			if (params->options & VACOPT_VACUUM)
 			{
-				if (!vacuum_rel(vrel->oid, vrel->relation, params))
+				if (!vacuum_rel(vrel->oid, vrel->relation, params, false))
 					continue;
 			}
 
@@ -1667,7 +1687,10 @@ vac_truncate_clog(TransactionId frozenXID,
  *		At entry and exit, we are not inside a transaction.
  */
 static bool
-vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
+vacuum_rel(Oid relid,
+		   RangeVar *relation,
+		   VacuumParams *params,
+		   bool processing_toast_table)
 {
 	LOCKMODE	lmode;
 	Relation	onerel;
@@ -1676,6 +1699,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	Oid			save_userid;
 	int			save_sec_context;
 	int			save_nestlevel;
+	bool		process_toast;
 
 	Assert(params != NULL);
 
@@ -1843,9 +1867,16 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	/*
 	 * 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 MAIN_RELATION_CLEANUP is disabled.
 	 */
-	if (!(params->options & VACOPT_SKIPTOAST) && !(params->options & VACOPT_FULL))
+	process_toast = (params->options & VACOPT_TOAST_CLEANUP) != 0;
+
+	if ((params->options & VACOPT_FULL) != 0 &&
+		(params->options & VACOPT_MAIN_REL_CLEANUP) != 0)
+		process_toast = false;
+
+	if (process_toast)
 		toast_relid = onerel->rd_rel->reltoastrelid;
 	else
 		toast_relid = InvalidOid;
@@ -1863,23 +1894,30 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 
 	/*
 	 * Do the actual work --- either FULL or "lazy" vacuum
+	 *
+	 * We skip this part if we're processing the main relation and
+	 * MAIN_RELATION_CLEANUP has been disabled.
 	 */
-	if (params->options & VACOPT_FULL)
+	if ((params->options & VACOPT_MAIN_REL_CLEANUP) != 0 ||
+		processing_toast_table)
 	{
-		int			cluster_options = 0;
+		if (params->options & VACOPT_FULL)
+		{
+			int			cluster_options = 0;
 
-		/* close relation before vacuuming, but hold lock until commit */
-		relation_close(onerel, NoLock);
-		onerel = NULL;
+			/* close relation before vacuuming, but hold lock until commit */
+			relation_close(onerel, NoLock);
+			onerel = NULL;
 
-		if ((params->options & VACOPT_VERBOSE) != 0)
-			cluster_options |= CLUOPT_VERBOSE;
+			if ((params->options & VACOPT_VERBOSE) != 0)
+				cluster_options |= CLUOPT_VERBOSE;
 
-		/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
-		cluster_rel(relid, InvalidOid, cluster_options);
+			/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
+			cluster_rel(relid, InvalidOid, cluster_options);
+		}
+		else
+			table_relation_vacuum(onerel, params, vac_strategy);
 	}
-	else
-		table_relation_vacuum(onerel, params, vac_strategy);
 
 	/* Roll back any GUC changes executed by index functions */
 	AtEOXact_GUC(false, save_nestlevel);
@@ -1905,7 +1943,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	 * totally unimportant for toast relations.
 	 */
 	if (toast_relid != InvalidOid)
-		vacuum_rel(toast_relid, NULL, params);
+		vacuum_rel(toast_relid, NULL, params, true);
 
 	/*
 	 * Now release the session-level lock on the master table.
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 6d1f28c327..fc3e276e0b 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2880,7 +2880,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 		tab = palloc(sizeof(autovac_table));
 		tab->at_relid = relid;
 		tab->at_sharedrel = classForm->relisshared;
-		tab->at_params.options = VACOPT_SKIPTOAST |
+		tab->at_params.options = VACOPT_MAIN_REL_CLEANUP |
 			(dovacuum ? VACOPT_VACUUM : 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 dc03fbde13..be3e7184e1 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3649,8 +3649,9 @@ 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", "TRUNCATE", "PARALLEL");
-		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE"))
+						  "INDEX_CLEANUP", "TRUNCATE", "PARALLEL",
+						  "MAIN_RELATION_CLEANUP", "TOAST_TABLE_CLEANUP");
+		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE|MAIN_RELATION_CLEANUP|TOAST_TABLE_CLEANUP"))
 			COMPLETE_WITH("ON", "OFF");
 	}
 	else if (HeadMatches("VACUUM") && TailMatches("("))
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index c27d255d8d..630a6b70de 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -182,8 +182,9 @@ typedef enum VacuumOption
 	VACOPT_FREEZE = 1 << 3,		/* FREEZE option */
 	VACOPT_FULL = 1 << 4,		/* FULL (non-concurrent) vacuum */
 	VACOPT_SKIP_LOCKED = 1 << 5,	/* skip if cannot get lock */
-	VACOPT_SKIPTOAST = 1 << 6,	/* don't process the TOAST table, if any */
-	VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7	/* don't skip any pages */
+	VACOPT_TOAST_CLEANUP = 1 << 6,	/* process TOAST table, if any */
+	VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7,	/* don't skip any pages */
+	VACOPT_MAIN_REL_CLEANUP = 1 << 8	/* process main relation */
 } VacuumOption;
 
 /*
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 0cfe28e63f..bdf6f1025c 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -250,6 +250,16 @@ RESET default_transaction_isolation;
 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
+-- MAIN_RELATION_CLEANUP and TOAST_TABLE_CLEANUP options
+ALTER TABLE vactst ADD COLUMN t TEXT;
+ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, ANALYZE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE, FULL) vactst;
+ERROR:  VACUUM option TOAST_TABLE_CLEANUP cannot be disabled when FULL is specified
+VACUUM (MAIN_RELATION_CLEANUP FALSE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, FULL) vactst;
 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 cf741f7b11..19607f629e 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -210,6 +210,16 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
 
+-- MAIN_RELATION_CLEANUP and TOAST_TABLE_CLEANUP options
+ALTER TABLE vactst ADD COLUMN t TEXT;
+ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, ANALYZE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE, FULL) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, FULL) vactst;
+
 DROP TABLE vaccluster;
 DROP TABLE vactst;
 DROP TABLE vacparted;
-- 
2.16.6

bossartn@amazon.com

over 5 years ago

In reply to: Bossart, Nathan (#8)

1 attachment(s)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

Here is a rebased version of the patch.

Nathan

Attachments:

v4-0001-Add-MAIN_RELATION_CLEANUP-and-TOAST_TABLE_CLEANUP.patchapplication/octet-stream; name=v4-0001-Add-MAIN_RELATION_CLEANUP-and-TOAST_TABLE_CLEANUP.patchDownload

From 27ccbb7af5d1e16da65c819b7fb93955395643d8 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Sun, 31 May 2020 21:29:39 +0000
Subject: [PATCH v4 1/1] Add MAIN_RELATION_CLEANUP and TOAST_TABLE_CLEANUP
 options to VACUUM.

---
 doc/src/sgml/ref/vacuum.sgml         | 28 +++++++++++++
 src/backend/commands/vacuum.c        | 76 +++++++++++++++++++++++++++---------
 src/backend/postmaster/autovacuum.c  |  2 +-
 src/bin/psql/tab-complete.c          |  5 ++-
 src/include/commands/vacuum.h        |  5 ++-
 src/test/regress/expected/vacuum.out | 10 +++++
 src/test/regress/sql/vacuum.sql      | 10 +++++
 7 files changed, 112 insertions(+), 24 deletions(-)

diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index a48f75ad7b..3e15824eaa 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -33,6 +33,8 @@ 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 [ <replaceable class="parameter">boolean</replaceable> ]
+    MAIN_RELATION_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
+    TOAST_TABLE_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
     TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
     PARALLEL <replaceable class="parameter">integer</replaceable>
 
@@ -210,6 +212,32 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>MAIN_RELATION_CLEANUP</literal></term>
+    <listitem>
+     <para>
+      Specifies that <command>VACUUM</command> should attempt to process the
+      main relation.  This is normally the desired behavior and is the default.
+      Setting this option to false may be useful when it is necessary to only
+      vacuum a relation's corresponding <literal>TOAST</literal> table.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>TOAST_TABLE_CLEANUP</literal></term>
+    <listitem>
+     <para>
+      Specifies that <command>VACUUM</command> should attempt to process the
+      corresponding <literal>TOAST</literal> table for each relation, if one
+      exists.  This is normally the desired behavior and is the default.
+      Setting this option to false may be useful when it is necessary to only
+      vacuum the main relation.  This option cannot be disabled when the
+      <literal>FULL</literal> option is specified.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>TRUNCATE</literal></term>
     <listitem>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 5a110edb07..2cfd28f45c 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -84,7 +84,10 @@ static void vac_truncate_clog(TransactionId frozenXID,
 							  MultiXactId minMulti,
 							  TransactionId lastSaneFrozenXid,
 							  MultiXactId lastSaneMinMulti);
-static bool vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params);
+static bool vacuum_rel(Oid relid,
+					   RangeVar *relation,
+					   VacuumParams *params,
+					   bool processing_toast_table);
 static double compute_parallel_delay(void);
 static VacOptTernaryValue get_vacopt_ternary_value(DefElem *def);
 
@@ -104,6 +107,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 	bool		freeze = false;
 	bool		full = false;
 	bool		disable_page_skipping = false;
+	bool		main_rel_cleanup = true;
+	bool		toast_cleanup = true;
 	ListCell   *lc;
 
 	/* Set default value */
@@ -140,6 +145,10 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 			disable_page_skipping = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "index_cleanup") == 0)
 			params.index_cleanup = get_vacopt_ternary_value(opt);
+		else if (strcmp(opt->defname, "main_relation_cleanup") == 0)
+			main_rel_cleanup = defGetBoolean(opt);
+		else if (strcmp(opt->defname, "toast_table_cleanup") == 0)
+			toast_cleanup = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "truncate") == 0)
 			params.truncate = get_vacopt_ternary_value(opt);
 		else if (strcmp(opt->defname, "parallel") == 0)
@@ -189,13 +198,14 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 		(analyze ? VACOPT_ANALYZE : 0) |
 		(freeze ? VACOPT_FREEZE : 0) |
 		(full ? VACOPT_FULL : 0) |
-		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0);
+		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0) |
+		(main_rel_cleanup ? VACOPT_MAIN_REL_CLEANUP : 0) |
+		(toast_cleanup ? VACOPT_TOAST_CLEANUP : 0);
 
 	/* sanity checks on options */
 	Assert(params.options & (VACOPT_VACUUM | VACOPT_ANALYZE));
 	Assert((params.options & VACOPT_VACUUM) ||
 		   !(params.options & (VACOPT_FULL | VACOPT_FREEZE)));
-	Assert(!(params.options & VACOPT_SKIPTOAST));
 
 	if ((params.options & VACOPT_FULL) && params.nworkers > 0)
 		ereport(ERROR,
@@ -318,6 +328,16 @@ vacuum(List *relations, VacuumParams *params,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("VACUUM option DISABLE_PAGE_SKIPPING cannot be used with FULL")));
 
+	/*
+	 * Sanity check TOAST_TABLE_CLEANUP option.
+	 */
+	if ((params->options & VACOPT_FULL) != 0 &&
+		(params->options & VACOPT_TOAST_CLEANUP) == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("VACUUM option TOAST_TABLE_CLEANUP cannot be "
+						"disabled when FULL is specified")));
+
 	/*
 	 * Send info about dead objects to the statistics collector, unless we are
 	 * in autovacuum --- autovacuum.c does this for itself.
@@ -446,7 +466,7 @@ vacuum(List *relations, VacuumParams *params,
 
 			if (params->options & VACOPT_VACUUM)
 			{
-				if (!vacuum_rel(vrel->oid, vrel->relation, params))
+				if (!vacuum_rel(vrel->oid, vrel->relation, params, false))
 					continue;
 			}
 
@@ -1665,7 +1685,10 @@ vac_truncate_clog(TransactionId frozenXID,
  *		At entry and exit, we are not inside a transaction.
  */
 static bool
-vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
+vacuum_rel(Oid relid,
+		   RangeVar *relation,
+		   VacuumParams *params,
+		   bool processing_toast_table)
 {
 	LOCKMODE	lmode;
 	Relation	onerel;
@@ -1674,6 +1697,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	Oid			save_userid;
 	int			save_sec_context;
 	int			save_nestlevel;
+	bool		process_toast;
 
 	Assert(params != NULL);
 
@@ -1841,9 +1865,16 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	/*
 	 * 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 MAIN_RELATION_CLEANUP is disabled.
 	 */
-	if (!(params->options & VACOPT_SKIPTOAST) && !(params->options & VACOPT_FULL))
+	process_toast = (params->options & VACOPT_TOAST_CLEANUP) != 0;
+
+	if ((params->options & VACOPT_FULL) != 0 &&
+		(params->options & VACOPT_MAIN_REL_CLEANUP) != 0)
+		process_toast = false;
+
+	if (process_toast)
 		toast_relid = onerel->rd_rel->reltoastrelid;
 	else
 		toast_relid = InvalidOid;
@@ -1861,23 +1892,30 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 
 	/*
 	 * Do the actual work --- either FULL or "lazy" vacuum
+	 *
+	 * We skip this part if we're processing the main relation and
+	 * MAIN_RELATION_CLEANUP has been disabled.
 	 */
-	if (params->options & VACOPT_FULL)
+	if ((params->options & VACOPT_MAIN_REL_CLEANUP) != 0 ||
+		processing_toast_table)
 	{
-		int			cluster_options = 0;
+		if (params->options & VACOPT_FULL)
+		{
+			int			cluster_options = 0;
 
-		/* close relation before vacuuming, but hold lock until commit */
-		relation_close(onerel, NoLock);
-		onerel = NULL;
+			/* close relation before vacuuming, but hold lock until commit */
+			relation_close(onerel, NoLock);
+			onerel = NULL;
 
-		if ((params->options & VACOPT_VERBOSE) != 0)
-			cluster_options |= CLUOPT_VERBOSE;
+			if ((params->options & VACOPT_VERBOSE) != 0)
+				cluster_options |= CLUOPT_VERBOSE;
 
-		/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
-		cluster_rel(relid, InvalidOid, cluster_options);
+			/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
+			cluster_rel(relid, InvalidOid, cluster_options);
+		}
+		else
+			table_relation_vacuum(onerel, params, vac_strategy);
 	}
-	else
-		table_relation_vacuum(onerel, params, vac_strategy);
 
 	/* Roll back any GUC changes executed by index functions */
 	AtEOXact_GUC(false, save_nestlevel);
@@ -1903,7 +1941,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	 * totally unimportant for toast relations.
 	 */
 	if (toast_relid != InvalidOid)
-		vacuum_rel(toast_relid, NULL, params);
+		vacuum_rel(toast_relid, NULL, params, true);
 
 	/*
 	 * Now release the session-level lock on the master table.
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index a8d4dfdd7c..e4f2402e5c 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2883,7 +2883,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 		tab = palloc(sizeof(autovac_table));
 		tab->at_relid = relid;
 		tab->at_sharedrel = classForm->relisshared;
-		tab->at_params.options = VACOPT_SKIPTOAST |
+		tab->at_params.options = VACOPT_MAIN_REL_CLEANUP |
 			(dovacuum ? VACOPT_VACUUM : 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 eb018854a5..e71df26096 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3665,8 +3665,9 @@ 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", "TRUNCATE", "PARALLEL");
-		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE"))
+						  "INDEX_CLEANUP", "TRUNCATE", "PARALLEL",
+						  "MAIN_RELATION_CLEANUP", "TOAST_TABLE_CLEANUP");
+		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE|MAIN_RELATION_CLEANUP|TOAST_TABLE_CLEANUP"))
 			COMPLETE_WITH("ON", "OFF");
 	}
 	else if (HeadMatches("VACUUM") && TailMatches("("))
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index a4cd721400..8a7e5b2aa1 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -182,8 +182,9 @@ typedef enum VacuumOption
 	VACOPT_FREEZE = 1 << 3,		/* FREEZE option */
 	VACOPT_FULL = 1 << 4,		/* FULL (non-concurrent) vacuum */
 	VACOPT_SKIP_LOCKED = 1 << 5,	/* skip if cannot get lock */
-	VACOPT_SKIPTOAST = 1 << 6,	/* don't process the TOAST table, if any */
-	VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7	/* don't skip any pages */
+	VACOPT_TOAST_CLEANUP = 1 << 6,	/* process TOAST table, if any */
+	VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7,	/* don't skip any pages */
+	VACOPT_MAIN_REL_CLEANUP = 1 << 8	/* process main relation */
 } VacuumOption;
 
 /*
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 3fccb183c0..43cb9f25d4 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -252,6 +252,16 @@ RESET default_transaction_isolation;
 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
+-- MAIN_RELATION_CLEANUP and TOAST_TABLE_CLEANUP options
+ALTER TABLE vactst ADD COLUMN t TEXT;
+ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, ANALYZE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE, FULL) vactst;
+ERROR:  VACUUM option TOAST_TABLE_CLEANUP cannot be disabled when FULL is specified
+VACUUM (MAIN_RELATION_CLEANUP FALSE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, FULL) vactst;
 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 c7b5f96f6b..ba046e439b 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -213,6 +213,16 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
 
+-- MAIN_RELATION_CLEANUP and TOAST_TABLE_CLEANUP options
+ALTER TABLE vactst ADD COLUMN t TEXT;
+ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, ANALYZE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE, FULL) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, FULL) vactst;
+
 DROP TABLE vaccluster;
 DROP TABLE vactst;
 DROP TABLE vacparted;
-- 
2.16.6

#10

pryzby@telsasoft.com

over 5 years ago

In reply to: Bossart, Nathan (#9)

3 attachment(s)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On Sun, May 31, 2020 at 10:13:39PM +0000, Bossart, Nathan wrote:

Here is a rebased version of the patch.

Should bin/vacuumdb support this?

Should vacuumdb have a way to pass an arbitrary option to the server, instead
of tacking on options (which are frequently forgotten on the initial commit to
the backend VACUUM command) ? That has the advantage that vacuumdb could use
new options even when connecting to a new server version than client. I think
it would be safe as long as it avoided characters like ')' and ';'. Maybe
all that's needed is isdigit() || isalpha() || isspace() || c=='_'

+    MAIN_RELATION_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
+    TOAST_TABLE_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]

Maybe should be called TOAST_RELATION_CLEANUP

See attached.

--
Justin

Attachments:

0001-Add-MAIN_RELATION_CLEANUP-and-SECONDARY_RELATION_CLE.patchtext/x-diff; charset=us-asciiDownload

From 2a4f411b3f2ee0706b1431f006e998503a420e03 Mon Sep 17 00:00:00 2001
From: "Bossart, Nathan" <bossartn@amazon.com>
Date: Sun, 31 May 2020 22:13:39 +0000
Subject: [PATCH 1/3] Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP
 options to VACUUM

Here is a rebased version of the patch.

Nathan

From 27ccbb7af5d1e16da65c819b7fb93955395643d8 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Sun, 31 May 2020 21:29:39 +0000
Subject: [PATCH v4 1/1] Add MAIN_RELATION_CLEANUP and TOAST_TABLE_CLEANUP
 options to VACUUM.
---
 doc/src/sgml/ref/vacuum.sgml         | 28 ++++++++++
 src/backend/commands/vacuum.c        | 76 +++++++++++++++++++++-------
 src/backend/postmaster/autovacuum.c  |  2 +-
 src/bin/psql/tab-complete.c          |  5 +-
 src/include/commands/vacuum.h        |  5 +-
 src/test/regress/expected/vacuum.out | 10 ++++
 src/test/regress/sql/vacuum.sql      | 10 ++++
 7 files changed, 112 insertions(+), 24 deletions(-)

diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index a48f75ad7b..3e15824eaa 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -33,6 +33,8 @@ 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 [ <replaceable class="parameter">boolean</replaceable> ]
+    MAIN_RELATION_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
+    TOAST_TABLE_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
     TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
     PARALLEL <replaceable class="parameter">integer</replaceable>
 
@@ -210,6 +212,32 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>MAIN_RELATION_CLEANUP</literal></term>
+    <listitem>
+     <para>
+      Specifies that <command>VACUUM</command> should attempt to process the
+      main relation.  This is normally the desired behavior and is the default.
+      Setting this option to false may be useful when it is necessary to only
+      vacuum a relation's corresponding <literal>TOAST</literal> table.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>TOAST_TABLE_CLEANUP</literal></term>
+    <listitem>
+     <para>
+      Specifies that <command>VACUUM</command> should attempt to process the
+      corresponding <literal>TOAST</literal> table for each relation, if one
+      exists.  This is normally the desired behavior and is the default.
+      Setting this option to false may be useful when it is necessary to only
+      vacuum the main relation.  This option cannot be disabled when the
+      <literal>FULL</literal> option is specified.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>TRUNCATE</literal></term>
     <listitem>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 576c7e63e9..23131fb516 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -84,7 +84,10 @@ static void vac_truncate_clog(TransactionId frozenXID,
 							  MultiXactId minMulti,
 							  TransactionId lastSaneFrozenXid,
 							  MultiXactId lastSaneMinMulti);
-static bool vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params);
+static bool vacuum_rel(Oid relid,
+					   RangeVar *relation,
+					   VacuumParams *params,
+					   bool processing_toast_table);
 static double compute_parallel_delay(void);
 static VacOptTernaryValue get_vacopt_ternary_value(DefElem *def);
 
@@ -104,6 +107,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 	bool		freeze = false;
 	bool		full = false;
 	bool		disable_page_skipping = false;
+	bool		main_rel_cleanup = true;
+	bool		toast_cleanup = true;
 	ListCell   *lc;
 
 	/* Set default value */
@@ -140,6 +145,10 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 			disable_page_skipping = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "index_cleanup") == 0)
 			params.index_cleanup = get_vacopt_ternary_value(opt);
+		else if (strcmp(opt->defname, "main_relation_cleanup") == 0)
+			main_rel_cleanup = defGetBoolean(opt);
+		else if (strcmp(opt->defname, "toast_table_cleanup") == 0)
+			toast_cleanup = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "truncate") == 0)
 			params.truncate = get_vacopt_ternary_value(opt);
 		else if (strcmp(opt->defname, "parallel") == 0)
@@ -189,13 +198,14 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 		(analyze ? VACOPT_ANALYZE : 0) |
 		(freeze ? VACOPT_FREEZE : 0) |
 		(full ? VACOPT_FULL : 0) |
-		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0);
+		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0) |
+		(main_rel_cleanup ? VACOPT_MAIN_REL_CLEANUP : 0) |
+		(toast_cleanup ? VACOPT_TOAST_CLEANUP : 0);
 
 	/* sanity checks on options */
 	Assert(params.options & (VACOPT_VACUUM | VACOPT_ANALYZE));
 	Assert((params.options & VACOPT_VACUUM) ||
 		   !(params.options & (VACOPT_FULL | VACOPT_FREEZE)));
-	Assert(!(params.options & VACOPT_SKIPTOAST));
 
 	if ((params.options & VACOPT_FULL) && params.nworkers > 0)
 		ereport(ERROR,
@@ -318,6 +328,16 @@ vacuum(List *relations, VacuumParams *params,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("VACUUM option DISABLE_PAGE_SKIPPING cannot be used with FULL")));
 
+	/*
+	 * Sanity check TOAST_TABLE_CLEANUP option.
+	 */
+	if ((params->options & VACOPT_FULL) != 0 &&
+		(params->options & VACOPT_TOAST_CLEANUP) == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("VACUUM option TOAST_TABLE_CLEANUP cannot be "
+						"disabled when FULL is specified")));
+
 	/*
 	 * Send info about dead objects to the statistics collector, unless we are
 	 * in autovacuum --- autovacuum.c does this for itself.
@@ -446,7 +466,7 @@ vacuum(List *relations, VacuumParams *params,
 
 			if (params->options & VACOPT_VACUUM)
 			{
-				if (!vacuum_rel(vrel->oid, vrel->relation, params))
+				if (!vacuum_rel(vrel->oid, vrel->relation, params, false))
 					continue;
 			}
 
@@ -1665,7 +1685,10 @@ vac_truncate_clog(TransactionId frozenXID,
  *		At entry and exit, we are not inside a transaction.
  */
 static bool
-vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
+vacuum_rel(Oid relid,
+		   RangeVar *relation,
+		   VacuumParams *params,
+		   bool processing_toast_table)
 {
 	LOCKMODE	lmode;
 	Relation	onerel;
@@ -1674,6 +1697,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	Oid			save_userid;
 	int			save_sec_context;
 	int			save_nestlevel;
+	bool		process_toast;
 
 	Assert(params != NULL);
 
@@ -1841,9 +1865,16 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	/*
 	 * 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 MAIN_RELATION_CLEANUP is disabled.
 	 */
-	if (!(params->options & VACOPT_SKIPTOAST) && !(params->options & VACOPT_FULL))
+	process_toast = (params->options & VACOPT_TOAST_CLEANUP) != 0;
+
+	if ((params->options & VACOPT_FULL) != 0 &&
+		(params->options & VACOPT_MAIN_REL_CLEANUP) != 0)
+		process_toast = false;
+
+	if (process_toast)
 		toast_relid = onerel->rd_rel->reltoastrelid;
 	else
 		toast_relid = InvalidOid;
@@ -1861,23 +1892,30 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 
 	/*
 	 * Do the actual work --- either FULL or "lazy" vacuum
+	 *
+	 * We skip this part if we're processing the main relation and
+	 * MAIN_RELATION_CLEANUP has been disabled.
 	 */
-	if (params->options & VACOPT_FULL)
+	if ((params->options & VACOPT_MAIN_REL_CLEANUP) != 0 ||
+		processing_toast_table)
 	{
-		int			cluster_options = 0;
+		if (params->options & VACOPT_FULL)
+		{
+			int			cluster_options = 0;
 
-		/* close relation before vacuuming, but hold lock until commit */
-		relation_close(onerel, NoLock);
-		onerel = NULL;
+			/* close relation before vacuuming, but hold lock until commit */
+			relation_close(onerel, NoLock);
+			onerel = NULL;
 
-		if ((params->options & VACOPT_VERBOSE) != 0)
-			cluster_options |= CLUOPT_VERBOSE;
+			if ((params->options & VACOPT_VERBOSE) != 0)
+				cluster_options |= CLUOPT_VERBOSE;
 
-		/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
-		cluster_rel(relid, InvalidOid, cluster_options);
+			/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
+			cluster_rel(relid, InvalidOid, cluster_options);
+		}
+		else
+			table_relation_vacuum(onerel, params, vac_strategy);
 	}
-	else
-		table_relation_vacuum(onerel, params, vac_strategy);
 
 	/* Roll back any GUC changes executed by index functions */
 	AtEOXact_GUC(false, save_nestlevel);
@@ -1903,7 +1941,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	 * totally unimportant for toast relations.
 	 */
 	if (toast_relid != InvalidOid)
-		vacuum_rel(toast_relid, NULL, params);
+		vacuum_rel(toast_relid, NULL, params, true);
 
 	/*
 	 * 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 9c7d4b0c60..463e1f2685 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2882,7 +2882,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 		tab = palloc(sizeof(autovac_table));
 		tab->at_relid = relid;
 		tab->at_sharedrel = classForm->relisshared;
-		tab->at_params.options = VACOPT_SKIPTOAST |
+		tab->at_params.options = VACOPT_MAIN_REL_CLEANUP |
 			(dovacuum ? VACOPT_VACUUM : 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 eb018854a5..e71df26096 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3665,8 +3665,9 @@ 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", "TRUNCATE", "PARALLEL");
-		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE"))
+						  "INDEX_CLEANUP", "TRUNCATE", "PARALLEL",
+						  "MAIN_RELATION_CLEANUP", "TOAST_TABLE_CLEANUP");
+		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE|MAIN_RELATION_CLEANUP|TOAST_TABLE_CLEANUP"))
 			COMPLETE_WITH("ON", "OFF");
 	}
 	else if (HeadMatches("VACUUM") && TailMatches("("))
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index a4cd721400..8a7e5b2aa1 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -182,8 +182,9 @@ typedef enum VacuumOption
 	VACOPT_FREEZE = 1 << 3,		/* FREEZE option */
 	VACOPT_FULL = 1 << 4,		/* FULL (non-concurrent) vacuum */
 	VACOPT_SKIP_LOCKED = 1 << 5,	/* skip if cannot get lock */
-	VACOPT_SKIPTOAST = 1 << 6,	/* don't process the TOAST table, if any */
-	VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7	/* don't skip any pages */
+	VACOPT_TOAST_CLEANUP = 1 << 6,	/* process TOAST table, if any */
+	VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7,	/* don't skip any pages */
+	VACOPT_MAIN_REL_CLEANUP = 1 << 8	/* process main relation */
 } VacuumOption;
 
 /*
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 3fccb183c0..43cb9f25d4 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -252,6 +252,16 @@ RESET default_transaction_isolation;
 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
+-- MAIN_RELATION_CLEANUP and TOAST_TABLE_CLEANUP options
+ALTER TABLE vactst ADD COLUMN t TEXT;
+ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, ANALYZE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE, FULL) vactst;
+ERROR:  VACUUM option TOAST_TABLE_CLEANUP cannot be disabled when FULL is specified
+VACUUM (MAIN_RELATION_CLEANUP FALSE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, FULL) vactst;
 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 c7b5f96f6b..ba046e439b 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -213,6 +213,16 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
 
+-- MAIN_RELATION_CLEANUP and TOAST_TABLE_CLEANUP options
+ALTER TABLE vactst ADD COLUMN t TEXT;
+ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, ANALYZE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE, FULL) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, FULL) vactst;
+
 DROP TABLE vaccluster;
 DROP TABLE vactst;
 DROP TABLE vacparted;
-- 
2.17.0

0002-vacuumdb-support.patchtext/x-diff; charset=us-asciiDownload

From c7ae482c358bb7aef6cc450ae72f11f92a0bd148 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sun, 12 Jul 2020 14:10:37 -0500
Subject: [PATCH 2/3] vacuumdb support

---
 src/bin/scripts/vacuumdb.c | 47 ++++++++++++++++++++++++++++++++++++++
 1 file changed, 47 insertions(+)

diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 6a3c941158..2b838069da 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -39,6 +39,8 @@ typedef struct vacuumingOptions
 									 * parallel degree, otherwise -1 */
 	bool		do_index_cleanup;
 	bool		do_truncate;
+	bool		do_mainrel;
+	bool		do_toastrel;
 } vacuumingOptions;
 
 
@@ -100,6 +102,8 @@ main(int argc, char *argv[])
 		{"min-mxid-age", required_argument, NULL, 7},
 		{"no-index-cleanup", no_argument, NULL, 8},
 		{"no-truncate", no_argument, NULL, 9},
+		{"no-clean-main", no_argument, NULL, 10},
+		{"no-clean-toast", no_argument, NULL, 11},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -126,6 +130,8 @@ main(int argc, char *argv[])
 	vacopts.parallel_workers = -1;
 	vacopts.do_index_cleanup = true;
 	vacopts.do_truncate = true;
+	vacopts.do_mainrel = true;
+	vacopts.do_toastrel = true;
 
 	pg_logging_init(argv[0]);
 	progname = get_progname(argv[0]);
@@ -235,6 +241,13 @@ main(int argc, char *argv[])
 			case 9:
 				vacopts.do_truncate = false;
 				break;
+			case 10:
+				vacopts.do_mainrel = false;
+				break;
+			case 11:
+				vacopts.do_toastrel = false;
+				break;
+
 			default:
 				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 				exit(1);
@@ -452,6 +465,22 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
 		exit(1);
 	}
 
+	if (!vacopts->do_mainrel && PQserverVersion(conn) < 140000)
+	{
+		PQfinish(conn);
+		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+					 "no-mainrel", "14");
+		exit(1);
+	}
+
+	if (!vacopts->do_toastrel && PQserverVersion(conn) < 140000)
+	{
+		PQfinish(conn);
+		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+					 "no-toastrel", "14");
+		exit(1);
+	}
+
 	if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
 	{
 		PQfinish(conn);
@@ -886,6 +915,22 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
 				appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
 				sep = comma;
 			}
+
+			if (!vacopts->do_mainrel)
+			{
+				/* supported since v14 */
+				Assert(serverVersion >= 140000);
+				appendPQExpBuffer(sql, "%sMAIN_RELATION_CLEANUP FALSE", sep);
+				sep = comma;
+			}
+			if (!vacopts->do_toastrel)
+			{
+				/* supported since v14 */
+				Assert(serverVersion >= 140000);
+				appendPQExpBuffer(sql, "%sTOAST_TABLE_CLEANUP FALSE", sep);
+				sep = comma;
+			}
+
 			if (vacopts->skip_locked)
 			{
 				/* SKIP_LOCKED is supported since v12 */
@@ -986,6 +1031,8 @@ help(const char *progname)
 	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-truncate               don't truncate empty pages at the end of the table\n"));
+	printf(_("      --no-clean-main             don't vacuum main relation\n"));
+	printf(_("      --no-clean-toast            don't vacuum TOAST relation\n"));
 	printf(_("  -P, --parallel=PARALLEL_DEGREE  use this many background workers for vacuum, if available\n"));
 	printf(_("  -q, --quiet                     don't write any messages\n"));
 	printf(_("      --skip-locked               skip relations that cannot be immediately locked\n"));
-- 
2.17.0

0003-Full-can-be-specified-just-not-enabled.patchtext/x-diff; charset=us-asciiDownload

From b71fa3f6a8be3631f76d7be176393905e53ba2af Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sun, 12 Jul 2020 14:31:35 -0500
Subject: [PATCH 3/3] Full can be *specified* just not enabled

See also: 24d2d38b1eb86c0b410ad0f07f66566a83c6f05c
---
 src/backend/commands/vacuum.c | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 23131fb516..e8fa3eca7b 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -336,7 +336,7 @@ vacuum(List *relations, VacuumParams *params,
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("VACUUM option TOAST_TABLE_CLEANUP cannot be "
-						"disabled when FULL is specified")));
+						"disabled when FULL is used")));
 
 	/*
 	 * Send info about dead objects to the statistics collector, unless we are
-- 
2.17.0

#11

[0]: https://www.postgresql.org/docs/devel/storage-toast.html
[1]: https://www.postgresql.org/docs/devel/catalog-pg-class.html

bossartn@amazon.com

over 5 years ago

In reply to: Justin Pryzby (#10)

1 attachment(s)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

Hi,

Thanks for taking a look.

On 7/13/20, 11:02 AM, "Justin Pryzby" <pryzby@telsasoft.com> wrote:

Should bin/vacuumdb support this?

Yes, it should. I've added it in v5 of the patch.

Should vacuumdb have a way to pass an arbitrary option to the server, instead
of tacking on options (which are frequently forgotten on the initial commit to
the backend VACUUM command) ? That has the advantage that vacuumdb could use
new options even when connecting to a new server version than client. I think
it would be safe as long as it avoided characters like ')' and ';'. Maybe
all that's needed is isdigit() || isalpha() || isspace() || c=='_'

I like the idea of allowing users to specify arbitrary options so that
they are not constrained to the options in the version of vacuumdb
they are using. I suspect we will still want to keep the vacuumdb
options updated for consistency and ease-of-use, though. IMO this
deserves its own thread.

+    MAIN_RELATION_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
+    TOAST_TABLE_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]

Maybe should be called TOAST_RELATION_CLEANUP

While using "relation" would be more consistent with the
MAIN_RELATION_CLEANUP option, I initially chose "table" for
consistency with most of the documentation [0]https://www.postgresql.org/docs/devel/storage-toast.html. Thinking further, I
believe this is still the right choice. While the term "relation"
refers to any type of object tracked in pg_class [1]https://www.postgresql.org/docs/devel/catalog-pg-class.html, a TOAST table
can only ever be a TOAST table. There are no other special TOAST
relation types (e.g. sequences, materialized views). On the other
hand, it is possible to vacuum other types of "main relations" besides
regular tables (e.g. materialized views), so MAIN_RELATION_CLEANUP
also seems right to me. Thoughts?

Nathan

Attachments:

v5-0001-Add-MAIN_RELATION_CLEANUP-and-TOAST_TABLE_CLEANUP.patchapplication/octet-stream; name=v5-0001-Add-MAIN_RELATION_CLEANUP-and-TOAST_TABLE_CLEANUP.patchDownload

From 431e50e7439fe4b60b7bcefe0a0d7b15242a707c Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Tue, 14 Jul 2020 00:15:05 +0000
Subject: [PATCH v5 1/1] Add MAIN_RELATION_CLEANUP and TOAST_TABLE_CLEANUP
 options to VACUUM.

---
 doc/src/sgml/ref/vacuum.sgml         | 28 +++++++++++++
 doc/src/sgml/ref/vacuumdb.sgml       | 30 ++++++++++++++
 src/backend/commands/vacuum.c        | 76 +++++++++++++++++++++++++++---------
 src/backend/postmaster/autovacuum.c  |  2 +-
 src/bin/psql/tab-complete.c          |  5 ++-
 src/bin/scripts/t/100_vacuumdb.pl    | 16 +++++++-
 src/bin/scripts/vacuumdb.c           | 56 ++++++++++++++++++++++++++
 src/include/commands/vacuum.h        |  5 ++-
 src/test/regress/expected/vacuum.out | 10 +++++
 src/test/regress/sql/vacuum.sql      | 10 +++++
 10 files changed, 213 insertions(+), 25 deletions(-)

diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index a48f75ad7b..8335aad979 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -33,6 +33,8 @@ 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 [ <replaceable class="parameter">boolean</replaceable> ]
+    MAIN_RELATION_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
+    TOAST_TABLE_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
     TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
     PARALLEL <replaceable class="parameter">integer</replaceable>
 
@@ -210,6 +212,32 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>MAIN_RELATION_CLEANUP</literal></term>
+    <listitem>
+     <para>
+      Specifies that <command>VACUUM</command> should attempt to process the
+      main relation.  This is normally the desired behavior and is the default.
+      Setting this option to false may be useful when it is necessary to only
+      vacuum a relation's corresponding <literal>TOAST</literal> table.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>TOAST_TABLE_CLEANUP</literal></term>
+    <listitem>
+     <para>
+      Specifies that <command>VACUUM</command> should attempt to process the
+      corresponding <literal>TOAST</literal> table for each relation, if one
+      exists.  This is normally the desired behavior and is the default.
+      Setting this option to false may be useful when it is necessary to only
+      vacuum the main relation.  This option cannot be disabled when the
+      <literal>FULL</literal> option is used.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>TRUNCATE</literal></term>
     <listitem>
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 95d6894cb0..f2d7828d34 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -241,6 +241,36 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--no-main-relation-cleanup</option></term>
+      <listitem>
+       <para>
+        Do not clean up the main relation.
+       </para>
+       <note>
+        <para>
+         This option is only available for servers running
+         <productname>PostgreSQL</productname> 14 and later.
+        </para>
+       </note>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>--no-toast-table-cleanup</option></term>
+      <listitem>
+       <para>
+        Do not clean up the TOAST table.
+       </para>
+       <note>
+        <para>
+         This option is only available for servers running
+         <productname>PostgreSQL</productname> 14 and later.
+        </para>
+       </note>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--no-truncate</option></term>
       <listitem>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 576c7e63e9..e8fa3eca7b 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -84,7 +84,10 @@ static void vac_truncate_clog(TransactionId frozenXID,
 							  MultiXactId minMulti,
 							  TransactionId lastSaneFrozenXid,
 							  MultiXactId lastSaneMinMulti);
-static bool vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params);
+static bool vacuum_rel(Oid relid,
+					   RangeVar *relation,
+					   VacuumParams *params,
+					   bool processing_toast_table);
 static double compute_parallel_delay(void);
 static VacOptTernaryValue get_vacopt_ternary_value(DefElem *def);
 
@@ -104,6 +107,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 	bool		freeze = false;
 	bool		full = false;
 	bool		disable_page_skipping = false;
+	bool		main_rel_cleanup = true;
+	bool		toast_cleanup = true;
 	ListCell   *lc;
 
 	/* Set default value */
@@ -140,6 +145,10 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 			disable_page_skipping = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "index_cleanup") == 0)
 			params.index_cleanup = get_vacopt_ternary_value(opt);
+		else if (strcmp(opt->defname, "main_relation_cleanup") == 0)
+			main_rel_cleanup = defGetBoolean(opt);
+		else if (strcmp(opt->defname, "toast_table_cleanup") == 0)
+			toast_cleanup = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "truncate") == 0)
 			params.truncate = get_vacopt_ternary_value(opt);
 		else if (strcmp(opt->defname, "parallel") == 0)
@@ -189,13 +198,14 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 		(analyze ? VACOPT_ANALYZE : 0) |
 		(freeze ? VACOPT_FREEZE : 0) |
 		(full ? VACOPT_FULL : 0) |
-		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0);
+		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0) |
+		(main_rel_cleanup ? VACOPT_MAIN_REL_CLEANUP : 0) |
+		(toast_cleanup ? VACOPT_TOAST_CLEANUP : 0);
 
 	/* sanity checks on options */
 	Assert(params.options & (VACOPT_VACUUM | VACOPT_ANALYZE));
 	Assert((params.options & VACOPT_VACUUM) ||
 		   !(params.options & (VACOPT_FULL | VACOPT_FREEZE)));
-	Assert(!(params.options & VACOPT_SKIPTOAST));
 
 	if ((params.options & VACOPT_FULL) && params.nworkers > 0)
 		ereport(ERROR,
@@ -318,6 +328,16 @@ vacuum(List *relations, VacuumParams *params,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("VACUUM option DISABLE_PAGE_SKIPPING cannot be used with FULL")));
 
+	/*
+	 * Sanity check TOAST_TABLE_CLEANUP option.
+	 */
+	if ((params->options & VACOPT_FULL) != 0 &&
+		(params->options & VACOPT_TOAST_CLEANUP) == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("VACUUM option TOAST_TABLE_CLEANUP cannot be "
+						"disabled when FULL is used")));
+
 	/*
 	 * Send info about dead objects to the statistics collector, unless we are
 	 * in autovacuum --- autovacuum.c does this for itself.
@@ -446,7 +466,7 @@ vacuum(List *relations, VacuumParams *params,
 
 			if (params->options & VACOPT_VACUUM)
 			{
-				if (!vacuum_rel(vrel->oid, vrel->relation, params))
+				if (!vacuum_rel(vrel->oid, vrel->relation, params, false))
 					continue;
 			}
 
@@ -1665,7 +1685,10 @@ vac_truncate_clog(TransactionId frozenXID,
  *		At entry and exit, we are not inside a transaction.
  */
 static bool
-vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
+vacuum_rel(Oid relid,
+		   RangeVar *relation,
+		   VacuumParams *params,
+		   bool processing_toast_table)
 {
 	LOCKMODE	lmode;
 	Relation	onerel;
@@ -1674,6 +1697,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	Oid			save_userid;
 	int			save_sec_context;
 	int			save_nestlevel;
+	bool		process_toast;
 
 	Assert(params != NULL);
 
@@ -1841,9 +1865,16 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	/*
 	 * 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 MAIN_RELATION_CLEANUP is disabled.
 	 */
-	if (!(params->options & VACOPT_SKIPTOAST) && !(params->options & VACOPT_FULL))
+	process_toast = (params->options & VACOPT_TOAST_CLEANUP) != 0;
+
+	if ((params->options & VACOPT_FULL) != 0 &&
+		(params->options & VACOPT_MAIN_REL_CLEANUP) != 0)
+		process_toast = false;
+
+	if (process_toast)
 		toast_relid = onerel->rd_rel->reltoastrelid;
 	else
 		toast_relid = InvalidOid;
@@ -1861,23 +1892,30 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 
 	/*
 	 * Do the actual work --- either FULL or "lazy" vacuum
+	 *
+	 * We skip this part if we're processing the main relation and
+	 * MAIN_RELATION_CLEANUP has been disabled.
 	 */
-	if (params->options & VACOPT_FULL)
+	if ((params->options & VACOPT_MAIN_REL_CLEANUP) != 0 ||
+		processing_toast_table)
 	{
-		int			cluster_options = 0;
+		if (params->options & VACOPT_FULL)
+		{
+			int			cluster_options = 0;
 
-		/* close relation before vacuuming, but hold lock until commit */
-		relation_close(onerel, NoLock);
-		onerel = NULL;
+			/* close relation before vacuuming, but hold lock until commit */
+			relation_close(onerel, NoLock);
+			onerel = NULL;
 
-		if ((params->options & VACOPT_VERBOSE) != 0)
-			cluster_options |= CLUOPT_VERBOSE;
+			if ((params->options & VACOPT_VERBOSE) != 0)
+				cluster_options |= CLUOPT_VERBOSE;
 
-		/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
-		cluster_rel(relid, InvalidOid, cluster_options);
+			/* VACUUM FULL is now a variant of CLUSTER; see cluster.c */
+			cluster_rel(relid, InvalidOid, cluster_options);
+		}
+		else
+			table_relation_vacuum(onerel, params, vac_strategy);
 	}
-	else
-		table_relation_vacuum(onerel, params, vac_strategy);
 
 	/* Roll back any GUC changes executed by index functions */
 	AtEOXact_GUC(false, save_nestlevel);
@@ -1903,7 +1941,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	 * totally unimportant for toast relations.
 	 */
 	if (toast_relid != InvalidOid)
-		vacuum_rel(toast_relid, NULL, params);
+		vacuum_rel(toast_relid, NULL, params, true);
 
 	/*
 	 * 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 9c7d4b0c60..463e1f2685 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2882,7 +2882,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 		tab = palloc(sizeof(autovac_table));
 		tab->at_relid = relid;
 		tab->at_sharedrel = classForm->relisshared;
-		tab->at_params.options = VACOPT_SKIPTOAST |
+		tab->at_params.options = VACOPT_MAIN_REL_CLEANUP |
 			(dovacuum ? VACOPT_VACUUM : 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 eb018854a5..e71df26096 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3665,8 +3665,9 @@ 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", "TRUNCATE", "PARALLEL");
-		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE"))
+						  "INDEX_CLEANUP", "TRUNCATE", "PARALLEL",
+						  "MAIN_RELATION_CLEANUP", "TOAST_TABLE_CLEANUP");
+		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE|MAIN_RELATION_CLEANUP|TOAST_TABLE_CLEANUP"))
 			COMPLETE_WITH("ON", "OFF");
 	}
 	else if (HeadMatches("VACUUM") && TailMatches("("))
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index 9e36b6d2b0..c469a1952c 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -3,7 +3,7 @@ use warnings;
 
 use PostgresNode;
 use TestLib;
-use Test::More tests => 55;
+use Test::More tests => 61;
 
 program_help_ok('vacuumdb');
 program_version_ok('vacuumdb');
@@ -62,6 +62,20 @@ $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-main-relation-cleanup', 'postgres' ],
+    qr/statement: VACUUM \(MAIN_RELATION_CLEANUP FALSE\).*;/,
+    'vacuumdb --no-main-relation-cleanup');
+$node->command_fails(
+    [ 'vacuumdb', '--analyze-only', '--no-main-relation-cleanup', 'postgres' ],
+    '--analyze-only and --no-main-relation-cleanup specified together');
+$node->issues_sql_like(
+    [ 'vacuumdb', '--no-toast-table-cleanup', 'postgres' ],
+    qr/statement: VACUUM \(TOAST_TABLE_CLEANUP FALSE\).*;/,
+    'vacuumdb --no-toast-table-cleanup');
+$node->command_fails(
+    [ 'vacuumdb', '--analyze-only', '--no-toast-table-cleanup', 'postgres' ],
+    '--analyze-only and --no-toast-table-cleanup specified together');
 $node->issues_sql_like(
 	[ 'vacuumdb', '-P', 2, 'postgres' ],
 	qr/statement: VACUUM \(PARALLEL 2\).*;/,
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 6a3c941158..1b974e087c 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -39,6 +39,8 @@ typedef struct vacuumingOptions
 									 * parallel degree, otherwise -1 */
 	bool		do_index_cleanup;
 	bool		do_truncate;
+	bool		do_main_rel_cleanup;
+	bool		do_toast_table_cleanup;
 } vacuumingOptions;
 
 
@@ -100,6 +102,8 @@ main(int argc, char *argv[])
 		{"min-mxid-age", required_argument, NULL, 7},
 		{"no-index-cleanup", no_argument, NULL, 8},
 		{"no-truncate", no_argument, NULL, 9},
+		{"no-main-relation-cleanup", no_argument, NULL, 10},
+		{"no-toast-table-cleanup", no_argument, NULL, 11},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -126,6 +130,8 @@ main(int argc, char *argv[])
 	vacopts.parallel_workers = -1;
 	vacopts.do_index_cleanup = true;
 	vacopts.do_truncate = true;
+	vacopts.do_main_rel_cleanup = true;
+	vacopts.do_toast_table_cleanup = true;
 
 	pg_logging_init(argv[0]);
 	progname = get_progname(argv[0]);
@@ -235,6 +241,12 @@ main(int argc, char *argv[])
 			case 9:
 				vacopts.do_truncate = false;
 				break;
+			case 10:
+				vacopts.do_main_rel_cleanup = false;
+				break;
+			case 11:
+				vacopts.do_toast_table_cleanup = false;
+				break;
 			default:
 				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 				exit(1);
@@ -291,6 +303,18 @@ main(int argc, char *argv[])
 						 "no-truncate");
 			exit(1);
 		}
+		if (!vacopts.do_main_rel_cleanup)
+		{
+			pg_log_error("cannot use the \"%s\" option when performing only analyze",
+						 "no-main-relation-cleanup");
+			exit(1);
+		}
+		if (!vacopts.do_toast_table_cleanup)
+		{
+			pg_log_error("cannot use the \"%s\" option when performing only analyze",
+						 "no-toast-table-cleanup");
+			exit(1);
+		}
 		/* allow 'and_analyze' with 'analyze_only' */
 	}
 
@@ -452,6 +476,22 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
 		exit(1);
 	}
 
+	if (!vacopts->do_main_rel_cleanup && PQserverVersion(conn) < 140000)
+	{
+		PQfinish(conn);
+		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+					 "no-main-relation-cleanup", "14");
+		exit(1);
+	}
+
+	if (!vacopts->do_toast_table_cleanup && PQserverVersion(conn) < 140000)
+	{
+		PQfinish(conn);
+		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+					 "no-toast-table-cleanup", "14");
+		exit(1);
+	}
+
 	if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
 	{
 		PQfinish(conn);
@@ -886,6 +926,20 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
 				appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
 				sep = comma;
 			}
+			if (!vacopts->do_main_rel_cleanup)
+			{
+				/* MAIN_RELATION_CLEANUP is supported since v14 */
+				Assert(serverVersion >= 140000);
+				appendPQExpBuffer(sql, "%sMAIN_RELATION_CLEANUP FALSE", sep);
+				sep = comma;
+			}
+			if (!vacopts->do_toast_table_cleanup)
+			{
+				/* TOAST_TABLE_CLEANUP is supported since v14 */
+				Assert(serverVersion >= 140000);
+				appendPQExpBuffer(sql, "%sTOAST_TABLE_CLEANUP FALSE", sep);
+				sep = comma;
+			}
 			if (vacopts->skip_locked)
 			{
 				/* SKIP_LOCKED is supported since v12 */
@@ -985,6 +1039,8 @@ 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-main-relation-cleanup  don't clean up the main relation\n"));
+	printf(_("      --no-toast-table-cleanup    don't clean up the TOAST table\n"));
 	printf(_("      --no-truncate               don't truncate empty pages at the end of the table\n"));
 	printf(_("  -P, --parallel=PARALLEL_DEGREE  use this many background workers for vacuum, if available\n"));
 	printf(_("  -q, --quiet                     don't write any messages\n"));
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index a4cd721400..8a7e5b2aa1 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -182,8 +182,9 @@ typedef enum VacuumOption
 	VACOPT_FREEZE = 1 << 3,		/* FREEZE option */
 	VACOPT_FULL = 1 << 4,		/* FULL (non-concurrent) vacuum */
 	VACOPT_SKIP_LOCKED = 1 << 5,	/* skip if cannot get lock */
-	VACOPT_SKIPTOAST = 1 << 6,	/* don't process the TOAST table, if any */
-	VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7	/* don't skip any pages */
+	VACOPT_TOAST_CLEANUP = 1 << 6,	/* process TOAST table, if any */
+	VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7,	/* don't skip any pages */
+	VACOPT_MAIN_REL_CLEANUP = 1 << 8	/* process main relation */
 } VacuumOption;
 
 /*
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 3fccb183c0..a1ee9244eb 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -252,6 +252,16 @@ RESET default_transaction_isolation;
 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
+-- MAIN_RELATION_CLEANUP and TOAST_TABLE_CLEANUP options
+ALTER TABLE vactst ADD COLUMN t TEXT;
+ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, ANALYZE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE, FULL) vactst;
+ERROR:  VACUUM option TOAST_TABLE_CLEANUP cannot be disabled when FULL is used
+VACUUM (MAIN_RELATION_CLEANUP FALSE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, FULL) vactst;
 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 c7b5f96f6b..ba046e439b 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -213,6 +213,16 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
 
+-- MAIN_RELATION_CLEANUP and TOAST_TABLE_CLEANUP options
+ALTER TABLE vactst ADD COLUMN t TEXT;
+ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, ANALYZE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE, FULL) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (MAIN_RELATION_CLEANUP FALSE, FULL) vactst;
+
 DROP TABLE vaccluster;
 DROP TABLE vactst;
 DROP TABLE vacparted;
-- 
2.16.6

#12

k.jamison@fujitsu.com

over 5 years ago

In reply to: Bossart, Nathan (#11)

RE: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On Tuesday, July 14, 2020 3:01 AM (GMT+9), Bossart, Nathan wrote:

Hi Nathan,

On 7/13/20, 11:02 AM, "Justin Pryzby" <pryzby(at)telsasoft(dot)com> wrote:

Should bin/vacuumdb support this?

Yes, it should. I've added it in v5 of the patch.

Thank you for the updated patch. I've joined as a reviewer.
I've also noticed that you have incorporated Justin's suggested vacuumdb support
in the recent patch, but in my opinion it'd be better to split them for better readability.
According to the cfbot, patch applies cleanly and passes all the tests.

[Use Case]

The main use case I'm targeting is when the level of bloat or
transaction ages of a relation and its TOAST table have significantly
diverged. In these scenarios, it could be beneficial to be able to
vacuum just one or the other, especially if the tables are large.
...
I reused the existing VACOPT_SKIPTOAST option to implement
SECONDARY_RELATION_CLEANUP. This option is currently only used for
autovacuum.

Perhaps this has not gathered much attention yet because it's not experienced
by many, but I don't see any problem with the additional options on manual
VACUUM on top of existing autovacuum cleanups. And I think this is useful
for the special use case mentioned, especially that toast table access is not
in public as per role limitation.

[Docs]
I also agree with "TOAST_TABLE_CLEANUP" and just name the options after the
respective proposed relation types in the future.

+    <term><literal>MAIN_RELATION_CLEANUP</literal></term>
+    <listitem>
+     <para>
+      Specifies that <command>VACUUM</command> should attempt to process the
+      main relation.  This is normally the desired behavior and is the default.
+      Setting this option to false may be useful when it is necessary to only
+      vacuum a relation's corresponding <literal>TOAST</literal> table.

Perhaps it's just my own opinion, but I think the word "process" is vague for
a beginner in postgres reading the documents. OTOH, I know it's also used
in the source code, so I guess it's just the convention. And "process" is
intuititve as "processing tables". Anyway, just my 2 cents & isn't a strong
opinion.

Also, there's an extra space between the 1st and 2nd sentences.

+    <term><literal>TOAST_TABLE_CLEANUP</literal></term>
+    <listitem>
+     <para>
+      Specifies that <command>VACUUM</command> should attempt to process the
+      corresponding <literal>TOAST</literal> table for each relation, if one
+      exists.  This is normally the desired behavior and is the default.
+      Setting this option to false may be useful when it is necessary to only
+      vacuum the main relation.  This option cannot be disabled when the
+      <literal>FULL</literal> option is used.

Same comments as above, & extra spaces in between the sentences.

@@ -1841,9 +1865,16 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	/*
 	 * 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 MAIN_RELATION_CLEANUP is disabled.

The additional last line is a bit confusing (and may be unnecessary/unrelated).
To clarify this thread on VACUUM FULL and my understanding of revised vacuum_rel below,
we allow MAIN_RELATION_CLEANUP option to be disabled (skip processing main relation)
and TOAST_TABLE_CLEANUP should be disabled because cluster_rel() will process the
toast table anyway.
Is my understanding correct? If yes, then maybe "unless" should be "even if" instead,
or we can just remove the line.

 static bool
-vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
+vacuum_rel(Oid relid,
+		   RangeVar *relation,
+		   VacuumParams *params,
+		   bool processing_toast_table)
{
...
+	bool		process_toast;
...

-	if (!(params->options & VACOPT_SKIPTOAST) && !(params->options & VACOPT_FULL))
+	process_toast = (params->options & VACOPT_TOAST_CLEANUP) != 0;
+
+	if ((params->options & VACOPT_FULL) != 0 &&
+		(params->options & VACOPT_MAIN_REL_CLEANUP) != 0)
+		process_toast = false;
+
+	if (process_toast)
 		toast_relid = onerel->rd_rel->reltoastrelid;
 	else
 		toast_relid = InvalidOid;
...

 	 * Do the actual work --- either FULL or "lazy" vacuum
+	 *
+	 * We skip this part if we're processing the main relation and
+	 * MAIN_RELATION_CLEANUP has been disabled.
 	 */
-	if (params->options & VACOPT_FULL)
+	if ((params->options & VACOPT_MAIN_REL_CLEANUP) != 0 ||
+		processing_toast_table)
...
 	if (toast_relid != InvalidOid)
-		vacuum_rel(toast_relid, NULL, params);
+		vacuum_rel(toast_relid, NULL, params, true);

I've attached v3 of the patch, which removes the restriction on
ANALYZE with MAIN_RELATION_CLEANUP disabled.

I've also confirmed those through regression + tap test in my own env
and they've passed. I'll look into deeply again if I find problems.

I think this follows the similar course of previously added VACUUM and
vacuummdb options (for using and skipping truncate, index cleanup, etc.),
so the patch seems almost plausible enough for me.

Regards,
Kirk

#13

michael@paquier.xyz

over 5 years ago

In reply to: k.jamison@fujitsu.com (#12)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On Tue, Jul 14, 2020 at 05:34:01AM +0000, k.jamison@fujitsu.com wrote:

I've also confirmed those through regression + tap test in my own env
and they've passed. I'll look into deeply again if I find problems.

+   VACOPT_TOAST_CLEANUP = 1 << 6,  /* process TOAST table, if any */
+   VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7,  /* don't skip any pages */
+   VACOPT_MAIN_REL_CLEANUP = 1 << 8    /* process main relation */
 } VacuumOption;

Do we actually need this much complication in the option set? It is
possible to vacuum directly a toast table by passing directly its
relation name, with pg_toast as schema, so you can already vacuum a
toast relation without the main part. And I would guess that users
caring about the toast table specifically would know already how to do
that, even if it requires a simple script and a query on pg_class.
Now there is a second part, where we'd like to vacuum the main
relation but not its toast table. My feeling by looking at this patch
today is that we could just make VACOPT_SKIPTOAST an option available
at user-level, and support all the cases discussed on this thread.
And we have already all the code in place to support that in the
backend for autovacuum as relations are processed individually,
without their toast tables if they have one.

I think this follows the similar course of previously added VACUUM and
vacuummdb options (for using and skipping truncate, index cleanup, etc.),
so the patch seems almost plausible enough for me.

-static bool vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params);
+static bool vacuum_rel(Oid relid,
+                      RangeVar *relation,
+                      VacuumParams *params,
+                      bool processing_toast_table);

Not much a fan of the addition of this parameter on this routine to
track down if the call should process a toast relation or not.
Couldn't you just prevent the call to vacuum_rel() to happen at all?
--
Michael

#14

bossartn@amazon.com

over 5 years ago

In reply to: Michael Paquier (#13)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On 8/2/20, 11:47 PM, "Michael Paquier" <michael@paquier.xyz> wrote:

+   VACOPT_TOAST_CLEANUP = 1 << 6,  /* process TOAST table, if any */
+   VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7,  /* don't skip any pages */
+   VACOPT_MAIN_REL_CLEANUP = 1 << 8    /* process main relation */
} VacuumOption;
Do we actually need this much complication in the option set? It is
possible to vacuum directly a toast table by passing directly its
relation name, with pg_toast as schema, so you can already vacuum a
toast relation without the main part. And I would guess that users
caring about the toast table specifically would know already how to do
that, even if it requires a simple script and a query on pg_class.
Now there is a second part, where we'd like to vacuum the main
relation but not its toast table. My feeling by looking at this patch
today is that we could just make VACOPT_SKIPTOAST an option available
at user-level, and support all the cases discussed on this thread.
And we have already all the code in place to support that in the
backend for autovacuum as relations are processed individually,
without their toast tables if they have one.

My main motive for adding the MAIN_RELATION_CLEANUP option is to allow
table owners to easily vacuum only a relation's TOAST table. Roles do
not have access to the pg_toast schema by default, so they might be
restricted from vacuuming their TOAST tables directly.

-static bool vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params);
+static bool vacuum_rel(Oid relid,
+                      RangeVar *relation,
+                      VacuumParams *params,
+                      bool processing_toast_table);
Not much a fan of the addition of this parameter on this routine to
track down if the call should process a toast relation or not.
Couldn't you just prevent the call to vacuum_rel() to happen at all?

I think it would be possible to skip calling vacuum_rel() from
expand_vacuum_rel()/get_all_vacuum_rels() as appropriate, but when I
looked into that approach originally, I was concerned that it would
add complexity to the lookups and ownership checks (especially the
partition lookup logic). The main tradeoffs of the approach I went
with are that we still create a transaction for the main relation and
that we still lock the main relation.

Nathan

#15

Masahiko Sawada

masahiko.sawada@2ndquadrant.com

over 5 years ago

In reply to: Michael Paquier (#13)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On Mon, 3 Aug 2020 at 15:47, Michael Paquier <michael@paquier.xyz> wrote:

On Tue, Jul 14, 2020 at 05:34:01AM +0000, k.jamison@fujitsu.com wrote:

I've also confirmed those through regression + tap test in my own env
and they've passed. I'll look into deeply again if I find problems.
+   VACOPT_TOAST_CLEANUP = 1 << 6,  /* process TOAST table, if any */
+   VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7,  /* don't skip any pages */
+   VACOPT_MAIN_REL_CLEANUP = 1 << 8    /* process main relation */
} VacuumOption;
Do we actually need this much complication in the option set? It is
possible to vacuum directly a toast table by passing directly its
relation name, with pg_toast as schema, so you can already vacuum a
toast relation without the main part. And I would guess that users
caring about the toast table specifically would know already how to do
that, even if it requires a simple script and a query on pg_class.

Yeah, I also doubt we really need to have this option in the core just
for the purpose of easily specifying toast relation to VACUUM command.
If the user doesn't know how to search the toast relation, I think we
can provide a script or an SQL function executes vacuum() C function
with the toast relation fetched by using the main relation. I
personally think VACUUM option basically should be present to control
the vacuum internal behavior granularly that the user cannot control
from outside, although there are some exceptions: FREEZE and ANALYZE.

Regards,

--
Masahiko Sawada http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

#16

michael@paquier.xyz

over 5 years ago

In reply to: Bossart, Nathan (#14)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On Wed, Aug 05, 2020 at 12:56:48AM +0000, Bossart, Nathan wrote:

My main motive for adding the MAIN_RELATION_CLEANUP option is to allow
table owners to easily vacuum only a relation's TOAST table. Roles do
not have access to the pg_toast schema by default, so they might be
restricted from vacuuming their TOAST tables directly.

True that you need an extra GRANT USAGE ON pg_toast to achieve that
for users with no privileges, but that's not impossible now either. I
am not sure that this use-case justifies a new option and more
complications in the code paths of vacuum though. So let's see first
if others have an opinion to offer.
--
Michael

#17

michael@paquier.xyz

over 5 years ago

In reply to: Michael Paquier (#16)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On Thu, Aug 06, 2020 at 11:50:06AM +0900, Michael Paquier wrote:

True that you need an extra GRANT USAGE ON pg_toast to achieve that
for users with no privileges, but that's not impossible now either. I
am not sure that this use-case justifies a new option and more
complications in the code paths of vacuum though. So let's see first
if others have an opinion to offer.

Seeing nothing happening here, I am marking the CF entry as returned
with feedback. FWIW, I still tend to think that we could call this
stuff a day if we had an option to skip a toast relation when willing
to vacuum the parent relation.
--
Michael

#18

pryzby@telsasoft.com

almost 5 years ago

In reply to: Bossart, Nathan (#4)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On Fri, Jan 24, 2020 at 09:24:45PM +0000, Bossart, Nathan wrote:

On 1/21/20, 1:39 PM, "Vik Fearing" <vik.fearing@2ndquadrant.com> wrote:

On 21/01/2020 22:21, Bossart, Nathan wrote:

I've attached a patch for a couple of new options for VACUUM:
MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP. The motive
behind these options is to allow table owners to easily vacuum only
the TOAST table or only the main relation. This is especially useful
for TOAST tables since roles do not have access to the pg_toast schema
by default and some users may find it difficult to discover the name
of a relation's TOAST table.

Could you explain why one would want to do this? Autovacuum will
already deal with the tables separately as needed, but I don't see when
a manual vacuum would want to make this distinction.

The main use case I'm targeting is when the level of bloat or
transaction ages of a relation and its TOAST table have significantly
diverged. In these scenarios, it could be beneficial to be able to
vacuum just one or the other, especially if the tables are large.

This just came up for me:

I have a daily maintenance script which pro-actively vacuums tables: freezing
historic partitions, vacuuming current tables if the table's relfrozenxid is
old, and to encourage indexonly scan.

I'm checking the greatest(age(toast,main)) and vacuum the table (and implicitly
its toast) whenever either is getting old.

But it'd be more ideal if I could independently vacuum the main table if it's
old, but not the toast table.

--
Justin

#19

bossartn@amazon.com

almost 5 years ago

In reply to: Justin Pryzby (#18)

1 attachment(s)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On 1/27/21, 11:07 AM, "Justin Pryzby" <pryzby@telsasoft.com> wrote:

This just came up for me:

I have a daily maintenance script which pro-actively vacuums tables: freezing
historic partitions, vacuuming current tables if the table's relfrozenxid is
old, and to encourage indexonly scan.

I'm checking the greatest(age(toast,main)) and vacuum the table (and implicitly
its toast) whenever either is getting old.

But it'd be more ideal if I could independently vacuum the main table if it's
old, but not the toast table.

Thanks for chiming in.

It looks like we were leaning towards only adding the
TOAST_TABLE_CLEANUP option, which is already implemented internally
with VACOPT_SKIPTOAST. It's already possible to vacuum a TOAST table
directly, so we can probably do without the MAIN_RELATION_CLEANUP
option.

I've attached a new patch that only adds TOAST_TABLE_CLEANUP.

Nathan

Attachments:

v6-0001-Add-TOAST_TABLE_CLEANUP-option-to-VACUUM.patchapplication/octet-stream; name=v6-0001-Add-TOAST_TABLE_CLEANUP-option-to-VACUUM.patchDownload

From 8c7f0613458f6090021a0d20767c692b98f0654a Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Wed, 27 Jan 2021 23:09:44 +0000
Subject: [PATCH v6 1/1] Add TOAST_TABLE_CLEANUP option to VACUUM.

---
 doc/src/sgml/ref/vacuum.sgml         | 15 +++++++++++++++
 doc/src/sgml/ref/vacuumdb.sgml       | 15 +++++++++++++++
 src/backend/commands/vacuum.c        | 17 +++++++++++++++--
 src/bin/psql/tab-complete.c          |  5 +++--
 src/bin/scripts/t/100_vacuumdb.pl    |  9 ++++++++-
 src/bin/scripts/vacuumdb.c           | 28 ++++++++++++++++++++++++++++
 src/test/regress/expected/vacuum.out |  6 ++++++
 src/test/regress/sql/vacuum.sql      |  6 ++++++
 8 files changed, 96 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 21ab57d880..d4d7935850 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 [ <replaceable class="parameter">boolean</replaceable> ]
+    TOAST_TABLE_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
     TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
     PARALLEL <replaceable class="parameter">integer</replaceable>
 
@@ -210,6 +211,20 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>TOAST_TABLE_CLEANUP</literal></term>
+    <listitem>
+     <para>
+      Specifies that <command>VACUUM</command> should attempt to process the
+      corresponding <literal>TOAST</literal> table for each relation, if one
+      exists.  This is normally the desired behavior and is the default.
+      Setting this option to false may be useful when it is necessary to only
+      vacuum the main relation.  This option cannot be disabled when the
+      <literal>FULL</literal> option is used.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>TRUNCATE</literal></term>
     <listitem>
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index efd1d6c1e6..6acb3de532 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -244,6 +244,21 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--no-toast-table-cleanup</option></term>
+      <listitem>
+       <para>
+        Do not clean up the TOAST table.
+       </para>
+       <note>
+        <para>
+         This option is only available for servers running
+         <productname>PostgreSQL</productname> 14 and later.
+        </para>
+       </note>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--no-truncate</option></term>
       <listitem>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 462f9a0f82..823b51b972 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -104,6 +104,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 	bool		freeze = false;
 	bool		full = false;
 	bool		disable_page_skipping = false;
+	bool		toast_cleanup = true;
 	ListCell   *lc;
 
 	/* Set default value */
@@ -140,6 +141,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 			disable_page_skipping = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "index_cleanup") == 0)
 			params.index_cleanup = get_vacopt_ternary_value(opt);
+		else if (strcmp(opt->defname, "toast_table_cleanup") == 0)
+			toast_cleanup = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "truncate") == 0)
 			params.truncate = get_vacopt_ternary_value(opt);
 		else if (strcmp(opt->defname, "parallel") == 0)
@@ -189,13 +192,13 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 		(analyze ? VACOPT_ANALYZE : 0) |
 		(freeze ? VACOPT_FREEZE : 0) |
 		(full ? VACOPT_FULL : 0) |
-		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0);
+		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0) |
+		(toast_cleanup ? 0 : VACOPT_SKIPTOAST);
 
 	/* sanity checks on options */
 	Assert(params.options & (VACOPT_VACUUM | VACOPT_ANALYZE));
 	Assert((params.options & VACOPT_VACUUM) ||
 		   !(params.options & (VACOPT_FULL | VACOPT_FREEZE)));
-	Assert(!(params.options & VACOPT_SKIPTOAST));
 
 	if ((params.options & VACOPT_FULL) && params.nworkers > 0)
 		ereport(ERROR,
@@ -318,6 +321,16 @@ vacuum(List *relations, VacuumParams *params,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("VACUUM option DISABLE_PAGE_SKIPPING cannot be used with FULL")));
 
+	/*
+	 * Sanity check TOAST_TABLE_CLEANUP option.
+	 */
+	if ((params->options & VACOPT_FULL) != 0 &&
+		(params->options & VACOPT_SKIPTOAST) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("VACUUM option TOAST_TABLE_CLEANUP cannot be "
+						"disabled when FULL is used")));
+
 	/*
 	 * Send info about dead objects to the statistics collector, unless we are
 	 * in autovacuum --- autovacuum.c does this for itself.
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 17f7265038..f40e5e7c68 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3868,8 +3868,9 @@ 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", "TRUNCATE", "PARALLEL");
-		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE"))
+						  "INDEX_CLEANUP", "TRUNCATE", "PARALLEL",
+						  "TOAST_TABLE_CLEANUP");
+		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE|TOAST_TABLE_CLEANUP"))
 			COMPLETE_WITH("ON", "OFF");
 	}
 	else if (HeadMatches("VACUUM") && TailMatches("("))
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index 9e36b6d2b0..613f6fb0d2 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -3,7 +3,7 @@ use warnings;
 
 use PostgresNode;
 use TestLib;
-use Test::More tests => 55;
+use Test::More tests => 58;
 
 program_help_ok('vacuumdb');
 program_version_ok('vacuumdb');
@@ -62,6 +62,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-toast-table-cleanup', 'postgres' ],
+    qr/statement: VACUUM \(TOAST_TABLE_CLEANUP FALSE\).*;/,
+    'vacuumdb --no-toast-table-cleanup');
+$node->command_fails(
+    [ 'vacuumdb', '--analyze-only', '--no-toast-table-cleanup', 'postgres' ],
+    '--analyze-only and --no-toast-table-cleanup specified together');
 $node->issues_sql_like(
 	[ 'vacuumdb', '-P', 2, 'postgres' ],
 	qr/statement: VACUUM \(PARALLEL 2\).*;/,
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 8246327770..a76d637b07 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -39,6 +39,7 @@ typedef struct vacuumingOptions
 									 * parallel degree, otherwise -1 */
 	bool		do_index_cleanup;
 	bool		do_truncate;
+	bool		do_toast_table_cleanup;
 } vacuumingOptions;
 
 
@@ -97,6 +98,7 @@ main(int argc, char *argv[])
 		{"min-mxid-age", required_argument, NULL, 7},
 		{"no-index-cleanup", no_argument, NULL, 8},
 		{"no-truncate", no_argument, NULL, 9},
+		{"no-toast-table-cleanup", no_argument, NULL, 10},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -124,6 +126,7 @@ main(int argc, char *argv[])
 	vacopts.parallel_workers = -1;
 	vacopts.do_index_cleanup = true;
 	vacopts.do_truncate = true;
+	vacopts.do_toast_table_cleanup = true;
 
 	pg_logging_init(argv[0]);
 	progname = get_progname(argv[0]);
@@ -233,6 +236,9 @@ main(int argc, char *argv[])
 			case 9:
 				vacopts.do_truncate = false;
 				break;
+			case 10:
+				vacopts.do_toast_table_cleanup = false;
+				break;
 			default:
 				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 				exit(1);
@@ -289,6 +295,12 @@ main(int argc, char *argv[])
 						 "no-truncate");
 			exit(1);
 		}
+		if (!vacopts.do_toast_table_cleanup)
+		{
+			pg_log_error("cannot use the \"%s\" option when performing only analyze",
+						 "no-toast-table-cleanup");
+			exit(1);
+		}
 		/* allow 'and_analyze' with 'analyze_only' */
 	}
 
@@ -454,6 +466,14 @@ vacuum_one_database(const ConnParams *cparams,
 		exit(1);
 	}
 
+	if (!vacopts->do_toast_table_cleanup && PQserverVersion(conn) < 140000)
+	{
+		PQfinish(conn);
+		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+					 "no-toast-table-cleanup", "14");
+		exit(1);
+	}
+
 	if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
 	{
 		PQfinish(conn);
@@ -869,6 +889,13 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
 				appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
 				sep = comma;
 			}
+			if (!vacopts->do_toast_table_cleanup)
+			{
+				/* TOAST_TABLE_CLEANUP is supported since v14 */
+				Assert(serverVersion >= 140000);
+				appendPQExpBuffer(sql, "%sTOAST_TABLE_CLEANUP FALSE", sep);
+				sep = comma;
+			}
 			if (vacopts->skip_locked)
 			{
 				/* SKIP_LOCKED is supported since v12 */
@@ -968,6 +995,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-toast-table-cleanup    don't clean up the TOAST table\n"));
 	printf(_("      --no-truncate               don't truncate empty pages at the end of the table\n"));
 	printf(_("  -P, --parallel=PARALLEL_DEGREE  use this many background workers for vacuum, if available\n"));
 	printf(_("  -q, --quiet                     don't write any messages\n"));
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 3fccb183c0..6f4736d3df 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -252,6 +252,12 @@ RESET default_transaction_isolation;
 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
+-- TOAST_TABLE_CLEANUP option
+ALTER TABLE vactst ADD COLUMN t TEXT;
+ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+VACUUM (TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE, FULL) vactst;
+ERROR:  VACUUM option TOAST_TABLE_CLEANUP cannot be disabled when FULL is used
 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 c7b5f96f6b..d8516903c0 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -213,6 +213,12 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
 
+-- TOAST_TABLE_CLEANUP option
+ALTER TABLE vactst ADD COLUMN t TEXT;
+ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+VACUUM (TOAST_TABLE_CLEANUP FALSE) vactst;
+VACUUM (TOAST_TABLE_CLEANUP FALSE, FULL) vactst;
+
 DROP TABLE vaccluster;
 DROP TABLE vactst;
 DROP TABLE vacparted;
-- 
2.16.6

#20

pryzby@telsasoft.com

almost 5 years ago

In reply to: Bossart, Nathan (#19)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On Wed, Jan 27, 2021 at 11:16:26PM +0000, Bossart, Nathan wrote:

On 1/27/21, 11:07 AM, "Justin Pryzby" <pryzby@telsasoft.com> wrote:

This just came up for me:

I have a daily maintenance script which pro-actively vacuums tables: freezing
historic partitions, vacuuming current tables if the table's relfrozenxid is
old, and to encourage indexonly scan.

I'm checking the greatest(age(toast,main)) and vacuum the table (and implicitly
its toast) whenever either is getting old.

But it'd be more ideal if I could independently vacuum the main table if it's
old, but not the toast table.

Thanks for chiming in.

It looks like we were leaning towards only adding the
TOAST_TABLE_CLEANUP option, which is already implemented internally
with VACOPT_SKIPTOAST. It's already possible to vacuum a TOAST table
directly, so we can probably do without the MAIN_RELATION_CLEANUP
option.

I've attached a new patch that only adds TOAST_TABLE_CLEANUP.

Thanks, I wrote my message after running into the issue and remembered this
thread. I didn't necessarily mean to send another patch :)

My only comment is on the name: TOAST_TABLE_CLEANUP. "Cleanup" suggests that
the (main or only) purpose is to "clean" dead tuples to avoid bloat. But in my
use case, the main purpose is to avoid XID wraparound (or its warnings).

Okay, my second only comment is that this:

| This option cannot be disabled when the <literal>FULL</literal> option is
| used.

Should it instead be ignored if FULL is also specified ? Currently only
PARALLEL and DISABLE_PAGE_SKIPPING cause an error when used with FULL. That's
documented for PARALLEL, but I think it should also be documented for
DISABLE_PAGE_SKIPPING (which is however an advanced option).

--
Justin

#21

bossartn@amazon.com

almost 5 years ago

In reply to: Justin Pryzby (#20)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On 1/27/21, 5:08 PM, "Justin Pryzby" <pryzby@telsasoft.com> wrote:

Thanks, I wrote my message after running into the issue and remembered this
thread. I didn't necessarily mean to send another patch :)

No worries. I lost track of this thread, but I don't mind picking it
up again.

My only comment is on the name: TOAST_TABLE_CLEANUP. "Cleanup" suggests that
the (main or only) purpose is to "clean" dead tuples to avoid bloat. But in my
use case, the main purpose is to avoid XID wraparound (or its warnings).

I chose TOAST_TABLE_CLEANUP to match the INDEX_CLEANUP option, but I'm
not wedded to that name. What do you think about PROCESS_TOAST_TABLE?

Okay, my second only comment is that this:

| This option cannot be disabled when the <literal>FULL</literal> option is
| used.

Should it instead be ignored if FULL is also specified ? Currently only
PARALLEL and DISABLE_PAGE_SKIPPING cause an error when used with FULL. That's
documented for PARALLEL, but I think it should also be documented for
DISABLE_PAGE_SKIPPING (which is however an advanced option).

IMO we should emit an ERROR in this case. If we ignored it, we'd end
up processing the TOAST table even though the user asked us to skip
it.

Nathan

#22

michael@paquier.xyz

almost 5 years ago

In reply to: Bossart, Nathan (#21)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On Thu, Jan 28, 2021 at 06:16:09PM +0000, Bossart, Nathan wrote:

I chose TOAST_TABLE_CLEANUP to match the INDEX_CLEANUP option, but I'm
not wedded to that name. What do you think about PROCESS_TOAST_TABLE?

Most of the other options use a verb, so using PROCESS, or even SKIP
sounds like a good idea. More ideas: PROCESS_TOAST, SKIP_TOAST. I
don't like much the term CLEANUP here, as it may imply, at least to
me, that the toast relation is getting partially processed.

IMO we should emit an ERROR in this case. If we ignored it, we'd end
up processing the TOAST table even though the user asked us to skip
it.

Issuing an error makes the most sense to me per the argument based on
cluster_rel() and copy_table_data(). Silently ignoring options can be
confusing for the end-user.

+       <para>
+        Do not clean up the TOAST table.
+       </para>
Is that enough?  I would say instead: "Skip the TOAST table associated
to the table to vacuum, if any."
--
Michael

#23

bossartn@amazon.com

almost 5 years ago

In reply to: Michael Paquier (#22)

1 attachment(s)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On 1/28/21, 11:15 PM, "Michael Paquier" <michael@paquier.xyz> wrote:

On Thu, Jan 28, 2021 at 06:16:09PM +0000, Bossart, Nathan wrote:

I chose TOAST_TABLE_CLEANUP to match the INDEX_CLEANUP option, but I'm
not wedded to that name. What do you think about PROCESS_TOAST_TABLE?

Most of the other options use a verb, so using PROCESS, or even SKIP
sounds like a good idea. More ideas: PROCESS_TOAST, SKIP_TOAST. I
don't like much the term CLEANUP here, as it may imply, at least to
me, that the toast relation is getting partially processed.

I changed it to PROCESS_TOAST.

+       <para>
+        Do not clean up the TOAST table.
+       </para>
Is that enough?  I would say instead: "Skip the TOAST table associated
to the table to vacuum, if any."

Done.

Nathan

Attachments:

v7-0001-Add-PROCESS_TOAST-option-to-VACUUM.patchapplication/octet-stream; name=v7-0001-Add-PROCESS_TOAST-option-to-VACUUM.patchDownload

From e72dd0743ffbb63ac7362297027c23ac1987400c Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Fri, 29 Jan 2021 18:20:31 +0000
Subject: [PATCH v7 1/1] Add PROCESS_TOAST option to VACUUM.

---
 doc/src/sgml/ref/vacuum.sgml         | 15 +++++++++++++++
 doc/src/sgml/ref/vacuumdb.sgml       | 15 +++++++++++++++
 src/backend/commands/vacuum.c        | 16 ++++++++++++++--
 src/bin/psql/tab-complete.c          |  5 +++--
 src/bin/scripts/t/100_vacuumdb.pl    |  9 ++++++++-
 src/bin/scripts/vacuumdb.c           | 28 ++++++++++++++++++++++++++++
 src/test/regress/expected/vacuum.out |  6 ++++++
 src/test/regress/sql/vacuum.sql      |  6 ++++++
 8 files changed, 95 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 21ab57d880..f2e84daf1d 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 [ <replaceable class="parameter">boolean</replaceable> ]
+    PROCESS_TOAST [ <replaceable class="parameter">boolean</replaceable> ]
     TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
     PARALLEL <replaceable class="parameter">integer</replaceable>
 
@@ -210,6 +211,20 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>PROCESS_TOAST</literal></term>
+    <listitem>
+     <para>
+      Specifies that <command>VACUUM</command> should attempt to process the
+      corresponding <literal>TOAST</literal> table for each relation, if one
+      exists.  This is normally the desired behavior and is the default.
+      Setting this option to false may be useful when it is necessary to only
+      vacuum the main relation.  This option cannot be disabled when the
+      <literal>FULL</literal> option is used.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>TRUNCATE</literal></term>
     <listitem>
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index efd1d6c1e6..0537fda317 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -244,6 +244,21 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--no-process-toast</option></term>
+      <listitem>
+       <para>
+        Skip the TOAST table associated to the table to vacuum, if any.
+       </para>
+       <note>
+        <para>
+         This option is only available for servers running
+         <productname>PostgreSQL</productname> 14 and later.
+        </para>
+       </note>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--no-truncate</option></term>
       <listitem>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 462f9a0f82..3b80a87a36 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -104,6 +104,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 	bool		freeze = false;
 	bool		full = false;
 	bool		disable_page_skipping = false;
+	bool		process_toast = true;
 	ListCell   *lc;
 
 	/* Set default value */
@@ -140,6 +141,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 			disable_page_skipping = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "index_cleanup") == 0)
 			params.index_cleanup = get_vacopt_ternary_value(opt);
+		else if (strcmp(opt->defname, "process_toast") == 0)
+			process_toast = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "truncate") == 0)
 			params.truncate = get_vacopt_ternary_value(opt);
 		else if (strcmp(opt->defname, "parallel") == 0)
@@ -189,13 +192,13 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 		(analyze ? VACOPT_ANALYZE : 0) |
 		(freeze ? VACOPT_FREEZE : 0) |
 		(full ? VACOPT_FULL : 0) |
-		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0);
+		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0) |
+		(process_toast ? 0 : VACOPT_SKIPTOAST);
 
 	/* sanity checks on options */
 	Assert(params.options & (VACOPT_VACUUM | VACOPT_ANALYZE));
 	Assert((params.options & VACOPT_VACUUM) ||
 		   !(params.options & (VACOPT_FULL | VACOPT_FREEZE)));
-	Assert(!(params.options & VACOPT_SKIPTOAST));
 
 	if ((params.options & VACOPT_FULL) && params.nworkers > 0)
 		ereport(ERROR,
@@ -318,6 +321,15 @@ vacuum(List *relations, VacuumParams *params,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("VACUUM option DISABLE_PAGE_SKIPPING cannot be used with FULL")));
 
+	/*
+	 * Sanity check PROCESS_TOAST option.
+	 */
+	if ((params->options & VACOPT_FULL) != 0 &&
+		(params->options & VACOPT_SKIPTOAST) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("VACUUM option PROCESS_TOAST cannot be disabled when FULL is used")));
+
 	/*
 	 * Send info about dead objects to the statistics collector, unless we are
 	 * in autovacuum --- autovacuum.c does this for itself.
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 17f7265038..73ce775081 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3868,8 +3868,9 @@ 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", "TRUNCATE", "PARALLEL");
-		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE"))
+						  "INDEX_CLEANUP", "TRUNCATE", "PARALLEL",
+						  "PROCESS_TOAST");
+		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE|PROCESS_TOAST"))
 			COMPLETE_WITH("ON", "OFF");
 	}
 	else if (HeadMatches("VACUUM") && TailMatches("("))
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index 9e36b6d2b0..44eaa72a2c 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -3,7 +3,7 @@ use warnings;
 
 use PostgresNode;
 use TestLib;
-use Test::More tests => 55;
+use Test::More tests => 58;
 
 program_help_ok('vacuumdb');
 program_version_ok('vacuumdb');
@@ -62,6 +62,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-toast', 'postgres' ],
+    qr/statement: VACUUM \(PROCESS_TOAST FALSE\).*;/,
+    'vacuumdb --no-process-toast');
+$node->command_fails(
+    [ 'vacuumdb', '--analyze-only', '--no-process-toast', 'postgres' ],
+    '--analyze-only and --no-process-toast specified together');
 $node->issues_sql_like(
 	[ 'vacuumdb', '-P', 2, 'postgres' ],
 	qr/statement: VACUUM \(PARALLEL 2\).*;/,
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 8246327770..100369662f 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -39,6 +39,7 @@ typedef struct vacuumingOptions
 									 * parallel degree, otherwise -1 */
 	bool		do_index_cleanup;
 	bool		do_truncate;
+	bool		process_toast;
 } vacuumingOptions;
 
 
@@ -97,6 +98,7 @@ main(int argc, char *argv[])
 		{"min-mxid-age", required_argument, NULL, 7},
 		{"no-index-cleanup", no_argument, NULL, 8},
 		{"no-truncate", no_argument, NULL, 9},
+		{"no-process-toast", no_argument, NULL, 10},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -124,6 +126,7 @@ main(int argc, char *argv[])
 	vacopts.parallel_workers = -1;
 	vacopts.do_index_cleanup = true;
 	vacopts.do_truncate = true;
+	vacopts.process_toast = true;
 
 	pg_logging_init(argv[0]);
 	progname = get_progname(argv[0]);
@@ -233,6 +236,9 @@ main(int argc, char *argv[])
 			case 9:
 				vacopts.do_truncate = false;
 				break;
+			case 10:
+				vacopts.process_toast = false;
+				break;
 			default:
 				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 				exit(1);
@@ -289,6 +295,12 @@ main(int argc, char *argv[])
 						 "no-truncate");
 			exit(1);
 		}
+		if (!vacopts.process_toast)
+		{
+			pg_log_error("cannot use the \"%s\" option when performing only analyze",
+						 "no-process-toast");
+			exit(1);
+		}
 		/* allow 'and_analyze' with 'analyze_only' */
 	}
 
@@ -454,6 +466,14 @@ vacuum_one_database(const ConnParams *cparams,
 		exit(1);
 	}
 
+	if (!vacopts->process_toast && PQserverVersion(conn) < 140000)
+	{
+		PQfinish(conn);
+		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+					 "no-process-toast", "14");
+		exit(1);
+	}
+
 	if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
 	{
 		PQfinish(conn);
@@ -869,6 +889,13 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
 				appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
 				sep = comma;
 			}
+			if (!vacopts->process_toast)
+			{
+				/* PROCESS_TOAST is supported since v14 */
+				Assert(serverVersion >= 140000);
+				appendPQExpBuffer(sql, "%sPROCESS_TOAST FALSE", sep);
+				sep = comma;
+			}
 			if (vacopts->skip_locked)
 			{
 				/* SKIP_LOCKED is supported since v12 */
@@ -968,6 +995,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-toast          skip the TOAST table associated to the table to vacuum, if any\n"));
 	printf(_("      --no-truncate               don't truncate empty pages at the end of the table\n"));
 	printf(_("  -P, --parallel=PARALLEL_DEGREE  use this many background workers for vacuum, if available\n"));
 	printf(_("  -q, --quiet                     don't write any messages\n"));
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 3fccb183c0..8b88b179a9 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -252,6 +252,12 @@ RESET default_transaction_isolation;
 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
+-- PROCESS_TOAST option
+ALTER TABLE vactst ADD COLUMN t TEXT;
+ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+VACUUM (PROCESS_TOAST FALSE) vactst;
+VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
+ERROR:  VACUUM option PROCESS_TOAST cannot be disabled when FULL is used
 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 c7b5f96f6b..93fd258fc0 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -213,6 +213,12 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
 
+-- PROCESS_TOAST option
+ALTER TABLE vactst ADD COLUMN t TEXT;
+ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+VACUUM (PROCESS_TOAST FALSE) vactst;
+VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
+
 DROP TABLE vaccluster;
 DROP TABLE vactst;
 DROP TABLE vacparted;
-- 
2.16.6

#24

michael@paquier.xyz

almost 5 years ago

In reply to: Bossart, Nathan (#23)

1 attachment(s)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On Fri, Jan 29, 2021 at 06:43:44PM +0000, Bossart, Nathan wrote:

I changed it to PROCESS_TOAST.

Thanks. PROCESS_TOAST sounds good to me at the end for the option
name, so let's just go with that.

Done.

While on it, I could not resist with changing VACOPT_SKIPTOAST to
VACOPT_PROCESS_TOAST on consistency grounds. This is used only in
four places in the code, so that's not invasive.

What do you think?
--
Michael

Attachments:

v8-0001-Add-PROCESS_TOAST-option-to-VACUUM.patchtext/x-diff; charset=us-asciiDownload

From 562704e1b759d63666d03073839819669104bcca Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Mon, 8 Feb 2021 16:30:15 +0900
Subject: [PATCH v8] Add PROCESS_TOAST option to VACUUM.

---
 src/include/commands/vacuum.h        |  2 +-
 src/backend/commands/vacuum.c        | 19 ++++++++++++++++---
 src/backend/postmaster/autovacuum.c  |  5 +++--
 src/bin/psql/tab-complete.c          |  5 +++--
 src/bin/scripts/t/100_vacuumdb.pl    |  9 ++++++++-
 src/bin/scripts/vacuumdb.c           | 28 ++++++++++++++++++++++++++++
 src/test/regress/expected/vacuum.out |  6 ++++++
 src/test/regress/sql/vacuum.sql      |  6 ++++++
 doc/src/sgml/ref/vacuum.sgml         | 15 +++++++++++++++
 doc/src/sgml/ref/vacuumdb.sgml       | 15 +++++++++++++++
 10 files changed, 101 insertions(+), 9 deletions(-)

diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 191cbbd004..d029da5ac0 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -181,7 +181,7 @@ 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_SKIPTOAST 0x40	/* don't process the TOAST table, if any */
+#define VACOPT_PROCESS_TOAST 0x40	/* process the TOAST table, if any */
 #define VACOPT_DISABLE_PAGE_SKIPPING 0x80	/* don't skip any pages */
 
 /*
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 462f9a0f82..5228ed0bdc 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -104,6 +104,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 	bool		freeze = false;
 	bool		full = false;
 	bool		disable_page_skipping = false;
+	bool		process_toast = true;
 	ListCell   *lc;
 
 	/* Set default value */
@@ -140,6 +141,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 			disable_page_skipping = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "index_cleanup") == 0)
 			params.index_cleanup = get_vacopt_ternary_value(opt);
+		else if (strcmp(opt->defname, "process_toast") == 0)
+			process_toast = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "truncate") == 0)
 			params.truncate = get_vacopt_ternary_value(opt);
 		else if (strcmp(opt->defname, "parallel") == 0)
@@ -189,13 +192,13 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 		(analyze ? VACOPT_ANALYZE : 0) |
 		(freeze ? VACOPT_FREEZE : 0) |
 		(full ? VACOPT_FULL : 0) |
-		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0);
+		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0) |
+		(process_toast ? VACOPT_PROCESS_TOAST : 0);
 
 	/* sanity checks on options */
 	Assert(params.options & (VACOPT_VACUUM | VACOPT_ANALYZE));
 	Assert((params.options & VACOPT_VACUUM) ||
 		   !(params.options & (VACOPT_FULL | VACOPT_FREEZE)));
-	Assert(!(params.options & VACOPT_SKIPTOAST));
 
 	if ((params.options & VACOPT_FULL) && params.nworkers > 0)
 		ereport(ERROR,
@@ -318,6 +321,15 @@ vacuum(List *relations, VacuumParams *params,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("VACUUM option DISABLE_PAGE_SKIPPING cannot be used with FULL")));
 
+	/*
+	 * Sanity check PROCESS_TOAST option.
+	 */
+	if ((params->options & VACOPT_FULL) != 0 &&
+		(params->options & VACOPT_PROCESS_TOAST) == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("PROCESS_TOAST required with VACUUM FULL")));
+
 	/*
 	 * Send info about dead objects to the statistics collector, unless we are
 	 * in autovacuum --- autovacuum.c does this for itself.
@@ -1895,7 +1907,8 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	 * us to process it.  In VACUUM FULL, though, the toast table is
 	 * automatically rebuilt by cluster_rel so we shouldn't recurse to it.
 	 */
-	if (!(params->options & VACOPT_SKIPTOAST) && !(params->options & VACOPT_FULL))
+	if ((params->options & VACOPT_PROCESS_TOAST) != 0 &&
+		(params->options & VACOPT_FULL) == 0)
 		toast_relid = onerel->rd_rel->reltoastrelid;
 	else
 		toast_relid = InvalidOid;
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 47e60ca561..5360604933 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2918,8 +2918,9 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 		tab = palloc(sizeof(autovac_table));
 		tab->at_relid = relid;
 		tab->at_sharedrel = classForm->relisshared;
-		tab->at_params.options = VACOPT_SKIPTOAST |
-			(dovacuum ? VACOPT_VACUUM : 0) |
+
+		/* Note that this skips toast relations */
+		tab->at_params.options = (dovacuum ? VACOPT_VACUUM : 0) |
 			(doanalyze ? VACOPT_ANALYZE : 0) |
 			(!wraparound ? VACOPT_SKIP_LOCKED : 0);
 		tab->at_params.index_cleanup = VACOPT_TERNARY_DEFAULT;
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 5f0e775fd3..cac4dc8960 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3870,8 +3870,9 @@ 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", "TRUNCATE", "PARALLEL");
-		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE"))
+						  "INDEX_CLEANUP", "TRUNCATE", "PARALLEL",
+						  "PROCESS_TOAST");
+		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE|PROCESS_TOAST"))
 			COMPLETE_WITH("ON", "OFF");
 	}
 	else if (HeadMatches("VACUUM") && TailMatches("("))
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index 9e36b6d2b0..44eaa72a2c 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -3,7 +3,7 @@ use warnings;
 
 use PostgresNode;
 use TestLib;
-use Test::More tests => 55;
+use Test::More tests => 58;
 
 program_help_ok('vacuumdb');
 program_version_ok('vacuumdb');
@@ -62,6 +62,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-toast', 'postgres' ],
+    qr/statement: VACUUM \(PROCESS_TOAST FALSE\).*;/,
+    'vacuumdb --no-process-toast');
+$node->command_fails(
+    [ 'vacuumdb', '--analyze-only', '--no-process-toast', 'postgres' ],
+    '--analyze-only and --no-process-toast specified together');
 $node->issues_sql_like(
 	[ 'vacuumdb', '-P', 2, 'postgres' ],
 	qr/statement: VACUUM \(PARALLEL 2\).*;/,
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 9dc8aca29f..5e286c805d 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -41,6 +41,7 @@ typedef struct vacuumingOptions
 									 * parallel degree, otherwise -1 */
 	bool		do_index_cleanup;
 	bool		do_truncate;
+	bool		process_toast;
 } vacuumingOptions;
 
 
@@ -99,6 +100,7 @@ main(int argc, char *argv[])
 		{"min-mxid-age", required_argument, NULL, 7},
 		{"no-index-cleanup", no_argument, NULL, 8},
 		{"no-truncate", no_argument, NULL, 9},
+		{"no-process-toast", no_argument, NULL, 10},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -126,6 +128,7 @@ main(int argc, char *argv[])
 	vacopts.parallel_workers = -1;
 	vacopts.do_index_cleanup = true;
 	vacopts.do_truncate = true;
+	vacopts.process_toast = true;
 
 	pg_logging_init(argv[0]);
 	progname = get_progname(argv[0]);
@@ -235,6 +238,9 @@ main(int argc, char *argv[])
 			case 9:
 				vacopts.do_truncate = false;
 				break;
+			case 10:
+				vacopts.process_toast = false;
+				break;
 			default:
 				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 				exit(1);
@@ -291,6 +297,12 @@ main(int argc, char *argv[])
 						 "no-truncate");
 			exit(1);
 		}
+		if (!vacopts.process_toast)
+		{
+			pg_log_error("cannot use the \"%s\" option when performing only analyze",
+						 "no-process-toast");
+			exit(1);
+		}
 		/* allow 'and_analyze' with 'analyze_only' */
 	}
 
@@ -456,6 +468,14 @@ vacuum_one_database(const ConnParams *cparams,
 		exit(1);
 	}
 
+	if (!vacopts->process_toast && PQserverVersion(conn) < 140000)
+	{
+		PQfinish(conn);
+		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+					 "no-process-toast", "14");
+		exit(1);
+	}
+
 	if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
 	{
 		PQfinish(conn);
@@ -872,6 +892,13 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
 				appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
 				sep = comma;
 			}
+			if (!vacopts->process_toast)
+			{
+				/* PROCESS_TOAST is supported since v14 */
+				Assert(serverVersion >= 140000);
+				appendPQExpBuffer(sql, "%sPROCESS_TOAST FALSE", sep);
+				sep = comma;
+			}
 			if (vacopts->skip_locked)
 			{
 				/* SKIP_LOCKED is supported since v12 */
@@ -971,6 +998,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-toast          skip the TOAST table associated to the table to vacuum, if any\n"));
 	printf(_("      --no-truncate               don't truncate empty pages at the end of the table\n"));
 	printf(_("  -P, --parallel=PARALLEL_DEGREE  use this many background workers for vacuum, if available\n"));
 	printf(_("  -q, --quiet                     don't write any messages\n"));
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 3fccb183c0..90cea6caa8 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -252,6 +252,12 @@ RESET default_transaction_isolation;
 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
+-- PROCESS_TOAST option
+ALTER TABLE vactst ADD COLUMN t TEXT;
+ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+VACUUM (PROCESS_TOAST FALSE) vactst;
+VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
+ERROR:  PROCESS_TOAST required with VACUUM FULL
 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 c7b5f96f6b..93fd258fc0 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -213,6 +213,12 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
 
+-- PROCESS_TOAST option
+ALTER TABLE vactst ADD COLUMN t TEXT;
+ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+VACUUM (PROCESS_TOAST FALSE) vactst;
+VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
+
 DROP TABLE vaccluster;
 DROP TABLE vactst;
 DROP TABLE vacparted;
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 21ab57d880..7670a73476 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 [ <replaceable class="parameter">boolean</replaceable> ]
+    PROCESS_TOAST [ <replaceable class="parameter">boolean</replaceable> ]
     TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
     PARALLEL <replaceable class="parameter">integer</replaceable>
 
@@ -210,6 +211,20 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>PROCESS_TOAST</literal></term>
+    <listitem>
+     <para>
+      Specifies that <command>VACUUM</command> should attempt to process the
+      corresponding <literal>TOAST</literal> table for each relation, if one
+      exists. This is normally the desired behavior and is the default.
+      Setting this option to false may be useful when it is necessary to only
+      vacuum the main relation. This option is required when the
+      <literal>FULL</literal> option is used.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>TRUNCATE</literal></term>
     <listitem>
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index efd1d6c1e6..0537fda317 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -244,6 +244,21 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--no-process-toast</option></term>
+      <listitem>
+       <para>
+        Skip the TOAST table associated to the table to vacuum, if any.
+       </para>
+       <note>
+        <para>
+         This option is only available for servers running
+         <productname>PostgreSQL</productname> 14 and later.
+        </para>
+       </note>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--no-truncate</option></term>
       <listitem>
-- 
2.30.0

#25

pryzby@telsasoft.com

almost 5 years ago

In reply to: Michael Paquier (#24)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On Mon, Feb 08, 2021 at 04:35:19PM +0900, Michael Paquier wrote:

On Fri, Jan 29, 2021 at 06:43:44PM +0000, Bossart, Nathan wrote:

I changed it to PROCESS_TOAST.

Thanks. PROCESS_TOAST sounds good to me at the end for the option
name, so let's just go with that.

Done.

While on it, I could not resist with changing VACOPT_SKIPTOAST to
VACOPT_PROCESS_TOAST on consistency grounds. This is used only in
four places in the code, so that's not invasive.

@@ -971,6 +998,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-toast          skip the TOAST table associated to the table to vacuum, if any\n"));

say "associated WITH"

+      corresponding <literal>TOAST</literal> table for each relation, if one
+      exists. This is normally the desired behavior and is the default.
+      Setting this option to false may be useful when it is necessary to only

Maybe it should say "when it is only necessary to"
But what you've written isn't wrong, depending on what you mean.

@@ -244,6 +244,21 @@ PostgreSQL documentation
+        Skip the TOAST table associated to the table to vacuum, if any.

associatd with

--
Justin

#26

bossartn@amazon.com

almost 5 years ago

In reply to: Justin Pryzby (#25)

1 attachment(s)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On 2/8/21, 12:47 AM, "Justin Pryzby" <pryzby@telsasoft.com> wrote:

On Mon, Feb 08, 2021 at 04:35:19PM +0900, Michael Paquier wrote:

On Fri, Jan 29, 2021 at 06:43:44PM +0000, Bossart, Nathan wrote:

I changed it to PROCESS_TOAST.

Thanks. PROCESS_TOAST sounds good to me at the end for the option
name, so let's just go with that.

Done.

While on it, I could not resist with changing VACOPT_SKIPTOAST to
VACOPT_PROCESS_TOAST on consistency grounds. This is used only in
four places in the code, so that's not invasive.

+1

@@ -971,6 +998,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-toast          skip the TOAST table associated to the table to vacuum, if any\n"));

say "associated WITH"

+      corresponding <literal>TOAST</literal> table for each relation, if one
+      exists. This is normally the desired behavior and is the default.
+      Setting this option to false may be useful when it is necessary to only

Maybe it should say "when it is only necessary to"
But what you've written isn't wrong, depending on what you mean.

@@ -244,6 +244,21 @@ PostgreSQL documentation
+        Skip the TOAST table associated to the table to vacuum, if any.

associatd with

These suggestions seem reasonable to me. I've applied them in v9.

Nathan

Attachments:

v9-0001-Add-PROCESS_TOAST-option-to-VACUUM.patchapplication/octet-stream; name=v9-0001-Add-PROCESS_TOAST-option-to-VACUUM.patchDownload

From ffae8b2bc219374c5cf9efcb71719a46e1539d49 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@amazon.com>
Date: Mon, 8 Feb 2021 18:46:01 +0000
Subject: [PATCH v9 1/1] Add PROCESS_TOAST option to VACUUM.

---
 doc/src/sgml/ref/vacuum.sgml         | 15 +++++++++++++++
 doc/src/sgml/ref/vacuumdb.sgml       | 15 +++++++++++++++
 src/backend/commands/vacuum.c        | 19 ++++++++++++++++---
 src/backend/postmaster/autovacuum.c  |  5 +++--
 src/bin/psql/tab-complete.c          |  5 +++--
 src/bin/scripts/t/100_vacuumdb.pl    |  9 ++++++++-
 src/bin/scripts/vacuumdb.c           | 28 ++++++++++++++++++++++++++++
 src/include/commands/vacuum.h        |  2 +-
 src/test/regress/expected/vacuum.out |  6 ++++++
 src/test/regress/sql/vacuum.sql      |  6 ++++++
 10 files changed, 101 insertions(+), 9 deletions(-)

diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 21ab57d880..4bb624979b 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 [ <replaceable class="parameter">boolean</replaceable> ]
+    PROCESS_TOAST [ <replaceable class="parameter">boolean</replaceable> ]
     TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
     PARALLEL <replaceable class="parameter">integer</replaceable>
 
@@ -210,6 +211,20 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>PROCESS_TOAST</literal></term>
+    <listitem>
+     <para>
+      Specifies that <command>VACUUM</command> should attempt to process the
+      corresponding <literal>TOAST</literal> table for each relation, if one
+      exists. This is normally the desired behavior and is the default.
+      Setting this option to false may be useful when it is only necessary to
+      vacuum the main relation. This option is required when the
+      <literal>FULL</literal> option is used.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>TRUNCATE</literal></term>
     <listitem>
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index efd1d6c1e6..843a82e871 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -244,6 +244,21 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--no-process-toast</option></term>
+      <listitem>
+       <para>
+        Skip the TOAST table associated with the table to vacuum, if any.
+       </para>
+       <note>
+        <para>
+         This option is only available for servers running
+         <productname>PostgreSQL</productname> 14 and later.
+        </para>
+       </note>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--no-truncate</option></term>
       <listitem>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 462f9a0f82..5228ed0bdc 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -104,6 +104,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 	bool		freeze = false;
 	bool		full = false;
 	bool		disable_page_skipping = false;
+	bool		process_toast = true;
 	ListCell   *lc;
 
 	/* Set default value */
@@ -140,6 +141,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 			disable_page_skipping = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "index_cleanup") == 0)
 			params.index_cleanup = get_vacopt_ternary_value(opt);
+		else if (strcmp(opt->defname, "process_toast") == 0)
+			process_toast = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "truncate") == 0)
 			params.truncate = get_vacopt_ternary_value(opt);
 		else if (strcmp(opt->defname, "parallel") == 0)
@@ -189,13 +192,13 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 		(analyze ? VACOPT_ANALYZE : 0) |
 		(freeze ? VACOPT_FREEZE : 0) |
 		(full ? VACOPT_FULL : 0) |
-		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0);
+		(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0) |
+		(process_toast ? VACOPT_PROCESS_TOAST : 0);
 
 	/* sanity checks on options */
 	Assert(params.options & (VACOPT_VACUUM | VACOPT_ANALYZE));
 	Assert((params.options & VACOPT_VACUUM) ||
 		   !(params.options & (VACOPT_FULL | VACOPT_FREEZE)));
-	Assert(!(params.options & VACOPT_SKIPTOAST));
 
 	if ((params.options & VACOPT_FULL) && params.nworkers > 0)
 		ereport(ERROR,
@@ -318,6 +321,15 @@ vacuum(List *relations, VacuumParams *params,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("VACUUM option DISABLE_PAGE_SKIPPING cannot be used with FULL")));
 
+	/*
+	 * Sanity check PROCESS_TOAST option.
+	 */
+	if ((params->options & VACOPT_FULL) != 0 &&
+		(params->options & VACOPT_PROCESS_TOAST) == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("PROCESS_TOAST required with VACUUM FULL")));
+
 	/*
 	 * Send info about dead objects to the statistics collector, unless we are
 	 * in autovacuum --- autovacuum.c does this for itself.
@@ -1895,7 +1907,8 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
 	 * us to process it.  In VACUUM FULL, though, the toast table is
 	 * automatically rebuilt by cluster_rel so we shouldn't recurse to it.
 	 */
-	if (!(params->options & VACOPT_SKIPTOAST) && !(params->options & VACOPT_FULL))
+	if ((params->options & VACOPT_PROCESS_TOAST) != 0 &&
+		(params->options & VACOPT_FULL) == 0)
 		toast_relid = onerel->rd_rel->reltoastrelid;
 	else
 		toast_relid = InvalidOid;
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 47e60ca561..5360604933 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2918,8 +2918,9 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 		tab = palloc(sizeof(autovac_table));
 		tab->at_relid = relid;
 		tab->at_sharedrel = classForm->relisshared;
-		tab->at_params.options = VACOPT_SKIPTOAST |
-			(dovacuum ? VACOPT_VACUUM : 0) |
+
+		/* Note that this skips toast relations */
+		tab->at_params.options = (dovacuum ? VACOPT_VACUUM : 0) |
 			(doanalyze ? VACOPT_ANALYZE : 0) |
 			(!wraparound ? VACOPT_SKIP_LOCKED : 0);
 		tab->at_params.index_cleanup = VACOPT_TERNARY_DEFAULT;
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 5f0e775fd3..cac4dc8960 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3870,8 +3870,9 @@ 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", "TRUNCATE", "PARALLEL");
-		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE"))
+						  "INDEX_CLEANUP", "TRUNCATE", "PARALLEL",
+						  "PROCESS_TOAST");
+		else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE|PROCESS_TOAST"))
 			COMPLETE_WITH("ON", "OFF");
 	}
 	else if (HeadMatches("VACUUM") && TailMatches("("))
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index 9e36b6d2b0..44eaa72a2c 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -3,7 +3,7 @@ use warnings;
 
 use PostgresNode;
 use TestLib;
-use Test::More tests => 55;
+use Test::More tests => 58;
 
 program_help_ok('vacuumdb');
 program_version_ok('vacuumdb');
@@ -62,6 +62,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-toast', 'postgres' ],
+    qr/statement: VACUUM \(PROCESS_TOAST FALSE\).*;/,
+    'vacuumdb --no-process-toast');
+$node->command_fails(
+    [ 'vacuumdb', '--analyze-only', '--no-process-toast', 'postgres' ],
+    '--analyze-only and --no-process-toast specified together');
 $node->issues_sql_like(
 	[ 'vacuumdb', '-P', 2, 'postgres' ],
 	qr/statement: VACUUM \(PARALLEL 2\).*;/,
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 9dc8aca29f..b28c788815 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -41,6 +41,7 @@ typedef struct vacuumingOptions
 									 * parallel degree, otherwise -1 */
 	bool		do_index_cleanup;
 	bool		do_truncate;
+	bool		process_toast;
 } vacuumingOptions;
 
 
@@ -99,6 +100,7 @@ main(int argc, char *argv[])
 		{"min-mxid-age", required_argument, NULL, 7},
 		{"no-index-cleanup", no_argument, NULL, 8},
 		{"no-truncate", no_argument, NULL, 9},
+		{"no-process-toast", no_argument, NULL, 10},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -126,6 +128,7 @@ main(int argc, char *argv[])
 	vacopts.parallel_workers = -1;
 	vacopts.do_index_cleanup = true;
 	vacopts.do_truncate = true;
+	vacopts.process_toast = true;
 
 	pg_logging_init(argv[0]);
 	progname = get_progname(argv[0]);
@@ -235,6 +238,9 @@ main(int argc, char *argv[])
 			case 9:
 				vacopts.do_truncate = false;
 				break;
+			case 10:
+				vacopts.process_toast = false;
+				break;
 			default:
 				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 				exit(1);
@@ -291,6 +297,12 @@ main(int argc, char *argv[])
 						 "no-truncate");
 			exit(1);
 		}
+		if (!vacopts.process_toast)
+		{
+			pg_log_error("cannot use the \"%s\" option when performing only analyze",
+						 "no-process-toast");
+			exit(1);
+		}
 		/* allow 'and_analyze' with 'analyze_only' */
 	}
 
@@ -456,6 +468,14 @@ vacuum_one_database(const ConnParams *cparams,
 		exit(1);
 	}
 
+	if (!vacopts->process_toast && PQserverVersion(conn) < 140000)
+	{
+		PQfinish(conn);
+		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+					 "no-process-toast", "14");
+		exit(1);
+	}
+
 	if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
 	{
 		PQfinish(conn);
@@ -872,6 +892,13 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
 				appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
 				sep = comma;
 			}
+			if (!vacopts->process_toast)
+			{
+				/* PROCESS_TOAST is supported since v14 */
+				Assert(serverVersion >= 140000);
+				appendPQExpBuffer(sql, "%sPROCESS_TOAST FALSE", sep);
+				sep = comma;
+			}
 			if (vacopts->skip_locked)
 			{
 				/* SKIP_LOCKED is supported since v12 */
@@ -971,6 +998,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-toast          skip the TOAST table associated with the table to vacuum, if any\n"));
 	printf(_("      --no-truncate               don't truncate empty pages at the end of the table\n"));
 	printf(_("  -P, --parallel=PARALLEL_DEGREE  use this many background workers for vacuum, if available\n"));
 	printf(_("  -q, --quiet                     don't write any messages\n"));
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 191cbbd004..d029da5ac0 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -181,7 +181,7 @@ 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_SKIPTOAST 0x40	/* don't process the TOAST table, if any */
+#define VACOPT_PROCESS_TOAST 0x40	/* process the TOAST table, if any */
 #define VACOPT_DISABLE_PAGE_SKIPPING 0x80	/* don't skip any pages */
 
 /*
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 3fccb183c0..90cea6caa8 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -252,6 +252,12 @@ RESET default_transaction_isolation;
 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
+-- PROCESS_TOAST option
+ALTER TABLE vactst ADD COLUMN t TEXT;
+ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+VACUUM (PROCESS_TOAST FALSE) vactst;
+VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
+ERROR:  PROCESS_TOAST required with VACUUM FULL
 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 c7b5f96f6b..93fd258fc0 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -213,6 +213,12 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 ANALYZE vactst;
 COMMIT;
 
+-- PROCESS_TOAST option
+ALTER TABLE vactst ADD COLUMN t TEXT;
+ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
+VACUUM (PROCESS_TOAST FALSE) vactst;
+VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
+
 DROP TABLE vaccluster;
 DROP TABLE vactst;
 DROP TABLE vacparted;
-- 
2.16.6

#27

michael@paquier.xyz

almost 5 years ago

In reply to: Bossart, Nathan (#26)

Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On Mon, Feb 08, 2021 at 06:59:45PM +0000, Bossart, Nathan wrote:

These suggestions seem reasonable to me. I've applied them in v9.

Sounds good to me, so applied.
--
Michael

#28