From 431e50e7439fe4b60b7bcefe0a0d7b15242a707c Mon Sep 17 00:00:00 2001 From: Nathan Bossart 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 ] [ boolean ] SKIP_LOCKED [ boolean ] INDEX_CLEANUP [ boolean ] + MAIN_RELATION_CLEANUP [ boolean ] + TOAST_TABLE_CLEANUP [ boolean ] TRUNCATE [ boolean ] PARALLEL integer @@ -210,6 +212,32 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ 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