On Tue, Dec 21, 2021 at 4:56 PM Peter Geoghegan <p...@bowt.ie> wrote:
> But if we're going to add a new option to the VACUUM command (or
> something of similar scope), then we might as well add a new behavior
> that is reasonably exact -- something that (say) only *starts* a
> VACUUM for those tables whose relfrozenxid age currently exceeds half
> the autovacuum_freeze_max_age for the table (usually taken from the
> GUC, sometimes taken from the reloption), which also forces the
> failsafe. And with similar handling for
> relminmxid/autovacuum_multixact_freeze_max_age.
> This new command/facility should probably not be a new flag to the
> VACUUM command, as such. Rather, I think that it should either be an
> SQL-callable function, or a dedicated top-level command (that doesn't
> accept any tables). The only reason to have this is for scenarios
> where the user is already in a tough spot with wraparound failure,
> like that client of yours. Nobody wants to force the failsafe for one
> specific table. It's not general purpose, at all, and shouldn't claim
> to be.
I've attached a PoC *untested* patch to show what it would look like
as a top-level statement. If the "shape" is uncontroversial, I'll put
work into testing it and fleshing it out.
For the PoC I wanted to try re-using existing keywords. I went with
"VACUUM LIMIT" since LIMIT is already a keyword that cannot be used as
a table name. It also brings "wraparound limit" to mind. We could add
a single-use unreserved keyword (such as VACUUM_MINIMAL or
VACUUM_FAST), but that doesn't seem great.
> In other words, while triggering the failsafe is important, simply *not
> starting* VACUUM for relations where there is really no need for it is
> at least as important. We shouldn't even think about pruning or
> freezing with these tables. (ISTM that the only thing that might be a
> bit controversial about any of this is my definition of "safe", which
> seems like about the right trade-off to me.)
I'm not sure what the right trade-off is, but as written I used 95% of
max age. It might be undesirable to end up so close to kicking off
uninterruptible vacuums, but the point is to get out of single-user
mode and back to streaming WAL as quickly as possible. It might also
be worth overriding the min ages as well, but haven't done so here.
It can be executed in normal mode (although it's not expected to be),
which makes testing easier and allows for a future possibility of not
requiring shutdown at all, by e.g. terminating non-superuser
connections.
--
John Naylor
EDB: http://www.enterprisedb.com
src/backend/commands/vacuum.c | 133 +++++++++++++++++++++++++++++++++++++----
src/backend/nodes/copyfuncs.c | 3 +
src/backend/nodes/equalfuncs.c | 3 +
src/backend/parser/gram.y | 10 +++-
src/backend/tcop/utility.c | 13 ++++
src/include/commands/vacuum.h | 1 +
src/include/nodes/nodes.h | 1 +
src/include/nodes/parsenodes.h | 12 ++++
8 files changed, 165 insertions(+), 11 deletions(-)
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 287098e4d0..d1c59a78e9 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -52,6 +52,7 @@
#include "storage/proc.h"
#include "storage/procarray.h"
#include "utils/acl.h"
+#include "utils/builtins.h"
#include "utils/fmgroids.h"
#include "utils/guc.h"
#include "utils/memutils.h"
@@ -271,10 +272,132 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
/* user-invoked vacuum never uses this parameter */
params.log_min_duration = -1;
+ /*
+ * Create special memory context for cross-transaction storage.
+ *
+ * Since it is a child of PortalContext, it will go away eventually even
+ * if we suffer an error; there's no need for special abort cleanup logic.
+ */
+ vac_context = AllocSetContextCreate(PortalContext,
+ "Vacuum",
+ ALLOCSET_DEFAULT_SIZES);
+
/* Now go through the common routine */
vacuum(vacstmt->rels, ¶ms, NULL, isTopLevel);
}
+/*
+ * Like ExecVacuum, but specialized for recovering quickly from anti-wraparound
+ * shutdown.
+ */
+void
+ExecVacuumMinimal(VacuumMinimalStmt *fmstmt, bool isTopLevel)
+{
+ VacuumParams params;
+ List *vacrels = NIL;
+ Relation pgclass;
+ TableScanDesc scan;
+ HeapTuple tuple;
+ int32 table_xid_age;
+ int32 table_mxid_age;
+ int32 save_VacuumCostDelay;
+
+ /* use defaults */
+ // WIP: It might be worth trying to do less work here
+ params.freeze_min_age = -1;
+ params.multixact_freeze_min_age = -1;
+
+ /* it's unlikely any table we choose will not be eligible for aggressive vacuum, but make sure */
+ params.freeze_table_age = 0;
+ params.multixact_freeze_table_age = 0;
+
+ /* skip unnecessary work, as in failsafe mode */
+ params.index_cleanup = VACOPTVALUE_DISABLED;
+ params.truncate = VACOPTVALUE_DISABLED;
+
+ /* user-invoked vacuum is never "for wraparound" */
+ params.is_wraparound = false;
+
+ /* user-invoked vacuum never uses this parameter */
+ params.log_min_duration = -1;
+
+ /* we only expect this to run in single-user mode anyway */
+ params.nworkers = -1;
+
+ /* we don't need the toast relation since we select them separately */
+ params.options = VACOPT_VACUUM;
+
+ vac_context = AllocSetContextCreate(PortalContext,
+ "Vacuum",
+ ALLOCSET_DEFAULT_SIZES);
+
+ /* select relations closest to the wraparound limit */
+
+ pgclass = table_open(RelationRelationId, AccessShareLock);
+
+ scan = table_beginscan_catalog(pgclass, 0, NULL);
+
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
+ MemoryContext oldcontext;
+ Oid relid = classForm->oid;
+
+ /* check permissions of relation */
+ if (!vacuum_is_relation_owner(relid, classForm, params.options))
+ {
+ Assert(IsUnderPostmaster);
+ continue;
+ }
+
+ /*
+ * Only consider relations able to hold unfrozen XIDs (anything else
+ * should have InvalidTransactionId in relfrozenxid anyway).
+ */
+ if (classForm->relkind != RELKIND_RELATION &&
+ classForm->relkind != RELKIND_MATVIEW &&
+ classForm->relkind != RELKIND_TOASTVALUE)
+ {
+ Assert(!TransactionIdIsValid(classForm->relfrozenxid));
+ Assert(!MultiXactIdIsValid(classForm->relminmxid));
+ continue;
+ }
+
+ table_xid_age = DirectFunctionCall1(xid_age, classForm->relfrozenxid);
+ table_mxid_age = DirectFunctionCall1(mxid_age, classForm->relminmxid);
+
+ // FIXME: also check reloption
+ // WIP: 95% is a starting point for discussion
+ if ((table_xid_age < autovacuum_freeze_max_age * 0.95) ||
+ (table_mxid_age < autovacuum_multixact_freeze_max_age * 0.95))
+ continue;
+
+ /*
+ * Build VacuumRelation(s) specifying the table OIDs to be processed.
+ * We omit a RangeVar since it wouldn't be appropriate to complain
+ * about failure to open one of these relations later.
+ */
+ oldcontext = MemoryContextSwitchTo(vac_context);
+ vacrels = lappend(vacrels, makeVacuumRelation(NULL,
+ relid,
+ NIL));
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ table_endscan(scan);
+ table_close(pgclass, AccessShareLock);
+
+ /* turn off cost delay */
+ save_VacuumCostDelay = VacuumCostDelay;
+ VacuumCostDelay = 0;
+
+ /* Now go through the common routine */
+ vacuum(vacrels, ¶ms, NULL, isTopLevel);
+
+ /* restore cost delay, just in case we're not in single-user mode */
+ VacuumCostDelay = save_VacuumCostDelay;
+}
+
/*
* Internal entry point for VACUUM and ANALYZE commands.
*
@@ -358,16 +481,6 @@ vacuum(List *relations, VacuumParams *params,
if ((params->options & VACOPT_VACUUM) && !IsAutoVacuumWorkerProcess())
pgstat_vacuum_stat();
- /*
- * Create special memory context for cross-transaction storage.
- *
- * Since it is a child of PortalContext, it will go away eventually even
- * if we suffer an error; there's no need for special abort cleanup logic.
- */
- vac_context = AllocSetContextCreate(PortalContext,
- "Vacuum",
- ALLOCSET_DEFAULT_SIZES);
-
/*
* If caller didn't give us a buffer strategy object, make one in the
* cross-transaction memory context.
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 456d563f34..1b2a5fc640 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -5578,6 +5578,9 @@ copyObjectImpl(const void *from)
case T_VacuumStmt:
retval = _copyVacuumStmt(from);
break;
+ case T_VacuumMinimalStmt:
+ retval = (void *) makeNode(VacuumMinimalStmt);
+ break;
case T_VacuumRelation:
retval = _copyVacuumRelation(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 53beef1488..9ba9601058 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -3580,6 +3580,9 @@ equal(const void *a, const void *b)
case T_VacuumStmt:
retval = _equalVacuumStmt(a, b);
break;
+ case T_VacuumMinimalStmt:
+ retval = true;
+ break;
case T_VacuumRelation:
retval = _equalVacuumRelation(a, b);
break;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 879018377b..3ec299cd19 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -312,7 +312,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
RemoveFuncStmt RemoveOperStmt RenameStmt ReturnStmt RevokeStmt RevokeRoleStmt
RuleActionStmt RuleActionStmtOrEmpty RuleStmt
SecLabelStmt SelectStmt TransactionStmt TransactionStmtLegacy TruncateStmt
- UnlistenStmt UpdateStmt VacuumStmt
+ UnlistenStmt UpdateStmt VacuumStmt VacuumMinimalStmt
VariableResetStmt VariableSetStmt VariableShowStmt
ViewStmt CheckPointStmt CreateConversionStmt
DeallocateStmt PrepareStmt ExecuteStmt
@@ -1043,6 +1043,7 @@ stmt:
| UnlistenStmt
| UpdateStmt
| VacuumStmt
+ | VacuumMinimalStmt
| VariableResetStmt
| VariableSetStmt
| VariableShowStmt
@@ -10866,6 +10867,13 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose opt_analyze opt_vacuum_relati
}
;
+VacuumMinimalStmt: VACUUM LIMIT
+ {
+ VacuumMinimalStmt *n = makeNode(VacuumMinimalStmt);
+ $$ = (Node *) n;
+ }
+ ;
+
AnalyzeStmt: analyze_keyword opt_verbose opt_vacuum_relation_list
{
VacuumStmt *n = makeNode(VacuumStmt);
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 83e4e37c78..5907b29110 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -285,6 +285,7 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree)
case T_ClusterStmt:
case T_ReindexStmt:
case T_VacuumStmt:
+ case T_VacuumMinimalStmt:
{
/*
* These commands write WAL, so they're not strictly
@@ -859,6 +860,10 @@ standard_ProcessUtility(PlannedStmt *pstmt,
ExecVacuum(pstate, (VacuumStmt *) parsetree, isTopLevel);
break;
+ case T_VacuumMinimalStmt:
+ ExecVacuumMinimal((VacuumMinimalStmt *) parsetree, isTopLevel);
+ break;
+
case T_ExplainStmt:
ExplainQuery(pstate, (ExplainStmt *) parsetree, params, dest);
break;
@@ -2843,6 +2848,10 @@ CreateCommandTag(Node *parsetree)
tag = CMDTAG_ANALYZE;
break;
+ case T_VacuumMinimalStmt:
+ tag = CMDTAG_VACUUM;
+ break;
+
case T_ExplainStmt:
tag = CMDTAG_EXPLAIN;
break;
@@ -3483,6 +3492,10 @@ GetCommandLogLevel(Node *parsetree)
lev = LOGSTMT_ALL;
break;
+ case T_VacuumMinimalStmt:
+ lev = LOGSTMT_ALL;
+ break;
+
case T_ExplainStmt:
{
ExplainStmt *stmt = (ExplainStmt *) parsetree;
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index f8a7b3664a..637afad45c 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -267,6 +267,7 @@ extern int VacuumCostBalanceLocal;
/* in commands/vacuum.c */
extern void ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel);
+extern void ExecVacuumMinimal(VacuumMinimalStmt *fmstmt, bool isTopLevel);
extern void vacuum(List *relations, VacuumParams *params,
BufferAccessStrategy bstrategy, bool isTopLevel);
extern void vac_open_indexes(Relation relation, LOCKMODE lockmode,
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 28cf5aefca..75074f6de0 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -351,6 +351,7 @@ typedef enum NodeTag
T_CreatedbStmt,
T_DropdbStmt,
T_VacuumStmt,
+ T_VacuumMinimalStmt,
T_ExplainStmt,
T_CreateTableAsStmt,
T_CreateSeqStmt,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 413e7c85a1..e6cad1baa4 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3361,6 +3361,18 @@ typedef struct VacuumStmt
bool is_vacuumcmd; /* true for VACUUM, false for ANALYZE */
} VacuumStmt;
+/* ----------------------
+ * VacuumMinimal Statement
+ *
+ * Although this statement executes a type of VACUUM, it takes no options
+ * or relations.
+ * ----------------------
+ */
+typedef struct VacuumMinimalStmt
+{
+ NodeTag type;
+} VacuumMinimalStmt;
+
/*
* Info about a single target table of VACUUM/ANALYZE.
*
