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
>From 2a4f411b3f2ee0706b1431f006e998503a420e03 Mon Sep 17 00:00:00 2001
From: "Bossart, Nathan" <bossa...@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 <bossa...@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
>From c7ae482c358bb7aef6cc450ae72f11f92a0bd148 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@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
>From b71fa3f6a8be3631f76d7be176393905e53ba2af Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@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
