From b07fb2cfb89c8c0a29051a956c4693ae89b0212e Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Tue, 21 Jan 2020 21:15:51 +0000 Subject: [PATCH v1] Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM --- doc/src/sgml/ref/vacuum.sgml | 31 +++++++++++++ src/backend/commands/vacuum.c | 90 ++++++++++++++++++++++++++++-------- src/bin/psql/tab-complete.c | 6 ++- src/include/commands/vacuum.h | 1 + src/test/regress/expected/vacuum.out | 12 +++++ src/test/regress/sql/vacuum.sql | 10 ++++ 6 files changed, 129 insertions(+), 21 deletions(-) diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index 846056a353..ddbf21163d 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 ] + SECONDARY_RELATION_CLEANUP [ boolean ] TRUNCATE [ boolean ] PARALLEL integer @@ -210,6 +212,35 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ defname, "main_relation_cleanup") == 0) + params.main_rel_cleanup = get_vacopt_ternary_value(opt); + else if (strcmp(opt->defname, "secondary_relation_cleanup") == 0) + skip_toast = (get_vacopt_ternary_value(opt) == VACOPT_TERNARY_DISABLED); else if (strcmp(opt->defname, "truncate") == 0) params.truncate = get_vacopt_ternary_value(opt); else if (strcmp(opt->defname, "parallel") == 0) @@ -191,13 +200,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) | + (skip_toast ? VACOPT_SKIPTOAST : 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 +329,30 @@ vacuum(List *relations, VacuumParams *params, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("VACUUM option DISABLE_PAGE_SKIPPING cannot be used with FULL"))); + /* + * Sanity check *_RELATION_CLEANUP options. + */ + if ((params->options & VACOPT_SKIPTOAST) != 0 && + params->main_rel_cleanup == VACOPT_TERNARY_DISABLED) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("VACUUM options MAIN_RELATION_CLEANUP and " + "SECONDARY_RELATION_CLEANUP cannot both be disabled"))); + + if ((params->options & VACOPT_FULL) != 0 && + (params->options & VACOPT_SKIPTOAST) != 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("VACUUM option SECONDARY_RELATION_CLEANUP cannot be " + "disabled when FULL is specified"))); + + if ((params->options & VACOPT_ANALYZE) != 0 && + params->main_rel_cleanup == VACOPT_TERNARY_DISABLED) + 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 +481,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 +1700,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_secondary_rel) { LOCKMODE lmode; Relation onerel; @@ -1676,6 +1712,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) Oid save_userid; int save_sec_context; int save_nestlevel; + bool process_toast = true; Assert(params != NULL); @@ -1843,9 +1880,17 @@ 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)) + if (params->options & VACOPT_SKIPTOAST) + process_toast = false; + + if (params->options & VACOPT_FULL && + params->main_rel_cleanup == VACOPT_TERNARY_ENABLED) + process_toast = false; + + if (process_toast) toast_relid = onerel->rd_rel->reltoastrelid; else toast_relid = InvalidOid; @@ -1863,23 +1908,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->main_rel_cleanup == VACOPT_TERNARY_ENABLED || + processing_secondary_rel) { - 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 +1957,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/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 052d98b5c0..fb5e52b09a 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -3597,8 +3597,10 @@ psql_completion(const char *text, int start, int end) if (ends_with(prev_wd, '(') || ends_with(prev_wd, ',')) COMPLETE_WITH("FULL", "FREEZE", "ANALYZE", "VERBOSE", "DISABLE_PAGE_SKIPPING", "SKIP_LOCKED", - "INDEX_CLEANUP", "TRUNCATE", "PARALLEL"); - else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE")) + "INDEX_CLEANUP", "TRUNCATE", "PARALLEL", + "MAIN_RELATION_CLEANUP", + "SECONDARY_RELATION_CLEANUP"); + else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|INDEX_CLEANUP|TRUNCATE|MAIN_RELATION_CLEANUP|SECONDARY_RELATION_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..baedacac1a 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -222,6 +222,7 @@ typedef struct VacuumParams * default value depends on reloptions */ VacOptTernaryValue truncate; /* Truncate empty pages at the end, * default value depends on reloptions */ + VacOptTernaryValue main_rel_cleanup; /* process the main relation */ /* * The number of parallel vacuum workers. 0 by default which means choose diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out index f4250a433a..621da7dc05 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -250,6 +250,18 @@ RESET default_transaction_isolation; BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; ANALYZE vactst; COMMIT; +-- *_RELATION_CLEANUP options +ALTER TABLE vactst ADD COLUMN t TEXT; +ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL; +VACUUM (MAIN_RELATION_CLEANUP FALSE, SECONDARY_RELATION_CLEANUP FALSE) vactst; +ERROR: VACUUM options MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP cannot both be disabled +VACUUM (MAIN_RELATION_CLEANUP FALSE, ANALYZE) vactst; +ERROR: VACUUM option MAIN_RELATION_CLEANUP cannot be disabled when ANALYZE is specified +VACUUM (SECONDARY_RELATION_CLEANUP FALSE, FULL) vactst; +ERROR: VACUUM option SECONDARY_RELATION_CLEANUP cannot be disabled when FULL is specified +VACUUM (MAIN_RELATION_CLEANUP FALSE) vactst; +VACUUM (SECONDARY_RELATION_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..3be0f35bed 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; +-- *_RELATION_CLEANUP options +ALTER TABLE vactst ADD COLUMN t TEXT; +ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL; +VACUUM (MAIN_RELATION_CLEANUP FALSE, SECONDARY_RELATION_CLEANUP FALSE) vactst; +VACUUM (MAIN_RELATION_CLEANUP FALSE, ANALYZE) vactst; +VACUUM (SECONDARY_RELATION_CLEANUP FALSE, FULL) vactst; +VACUUM (MAIN_RELATION_CLEANUP FALSE) vactst; +VACUUM (SECONDARY_RELATION_CLEANUP FALSE) vactst; +VACUUM (MAIN_RELATION_CLEANUP FALSE, FULL) vactst; + DROP TABLE vaccluster; DROP TABLE vactst; DROP TABLE vacparted; -- 2.16.5