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
From 562704e1b759d63666d03073839819669104bcca Mon Sep 17 00:00:00 2001
From: Michael Paquier <mich...@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
signature.asc
Description: PGP signature
