On Wed, Jan 19, 2022 at 5:26 PM Michael Paquier <mich...@paquier.xyz> wrote: > > Could you avoid introducing a new grammar pattern in VACUUM? Any new > option had better be within the parenthesized part as it is extensible > at will with its set of DefElems.
This new behavior is not an option that one can sensibly mix with other options as the user sees fit, but rather hard-codes the parameters for its single purpose. That said, I do understand your objection. [*thinks*] How about the attached patch (and test script)? It still needs polish, but it could work. It allows "verbose" to coexist, although that's really only for testing normal mode. While testing in single-user mode, I was sad to find out that it not only doesn't emit messages (not a client), but also doesn't log. That would have been a decent way to monitor progress... In this form, I'm no longer a fan of calling the option "wraparound", because it's too close to the "is_wraparound" param member. Internally, at least, we can use "emergency" or "minimal". (In fact the bit symbol is VACOPT_MINIMAL for this draft). That can be worked out later. On Fri, Jan 21, 2022 at 12:59 AM Masahiko Sawada <sawada.m...@gmail.com> wrote: > > The purpose of this thread is to provide a way for users to run vacuum > only very old tables (while skipping index cleanup, etc.), Ah, thank you Sawada-san, now I understand why we have been talking past each other. The purpose is actually: - to have a simple, easy to type, command - intended for single-user mode, but not limited to it (so it's easy to test) - to get out of single user mode as quickly as possible -- John Naylor EDB: http://www.enterprisedb.com
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 283ffaea77..6183f412d3 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"
@@ -114,6 +115,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
bool full = false;
bool disable_page_skipping = false;
bool process_toast = true;
+ bool wraparound = false;
ListCell *lc;
/* index_cleanup and truncate values unspecified for now */
@@ -200,6 +202,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
params.nworkers = nworkers;
}
}
+ else if (strcmp(opt->defname, "wraparound") == 0)
+ wraparound = defGetBoolean(opt);
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -246,17 +250,51 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
}
}
+ if (wraparound)
+ {
+ /* exclude incompatible options */
+ foreach(lc, vacstmt->options)
+ {
+ DefElem *opt = (DefElem *) lfirst(lc);
+
+ // WIP is there a better way?
+ if (strcmp(opt->defname, "wraparound") != 0 &&
+ strcmp(opt->defname, "verbose") != 0 &&
+ defGetBoolean(opt))
+
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("option \"%s\" is incompatible with WRAPAROUND", opt->defname),
+ parser_errposition(pstate, opt->location)));
+ }
+
+ /* skip unnecessary work, as in failsafe mode */
+ params.index_cleanup = VACOPTVALUE_DISABLED;
+ params.truncate = VACOPTVALUE_DISABLED;
+ }
+
/*
- * All freeze ages are zero if the FREEZE option is given; otherwise pass
- * them as -1 which means to use the default values.
+ * Set freeze ages to zero where appropriate; otherwise pass
+ * them as -1 which means to use the configured values.
*/
if (params.options & VACOPT_FREEZE)
{
+ /* All freeze ages are zero if the FREEZE option is given */
params.freeze_min_age = 0;
params.freeze_table_age = 0;
params.multixact_freeze_min_age = 0;
params.multixact_freeze_table_age = 0;
}
+ else if (params.options & VACOPT_MINIMAL)
+ {
+ /* it's unlikely any selected table will not be eligible for aggressive vacuum, but make sure */
+ params.freeze_table_age = 0;
+ params.multixact_freeze_table_age = 0;
+
+ // WIP: It might be worth trying to do less work here, or at least hard-coding the default values
+ params.freeze_min_age = -1;
+ params.multixact_freeze_min_age = -1;
+ }
else
{
params.freeze_min_age = -1;
@@ -894,6 +932,8 @@ get_all_vacuum_rels(int options)
Relation pgclass;
TableScanDesc scan;
HeapTuple tuple;
+ int32 table_xid_age,
+ table_mxid_age;
pgclass = table_open(RelationRelationId, AccessShareLock);
@@ -909,12 +949,42 @@ get_all_vacuum_rels(int options)
if (!vacuum_is_relation_owner(relid, classForm, options))
continue;
+ if (options | VACOPT_MINIMAL)
+ {
+ /*
+ * 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);
+
+ /* Hard-code 1 billion for the thresholds to avoid making assumptions
+ * about the configuration. This leaves some headroom for when the user
+ * returns to normal mode while also minimizing work.
+ * WIP: consider passing these constants via the params struct
+ */
+ // FIXME to speed up testing
+ // if ((table_xid_age < 1000 * 1000 * 1000) &&
+ // (table_mxid_age < 1000 * 1000 * 1000))
+ if ((table_xid_age < 1000 * 1000) &&
+ (table_mxid_age < 1000 * 1000))
+ continue;
+ }
/*
* We include partitioned tables here; depending on which operation is
* to be performed, caller will decide whether to process or ignore
* them.
*/
- if (classForm->relkind != RELKIND_RELATION &&
+ else if (classForm->relkind != RELKIND_RELATION &&
classForm->relkind != RELKIND_MATVIEW &&
classForm->relkind != RELKIND_PARTITIONED_TABLE)
continue;
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 5d0bdfa427..3d8b8fbbb1 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -188,6 +188,7 @@ typedef struct VacAttrStats
#define VACOPT_SKIP_LOCKED 0x20 /* skip if cannot get lock */
#define VACOPT_PROCESS_TOAST 0x40 /* process the TOAST table, if any */
#define VACOPT_DISABLE_PAGE_SKIPPING 0x80 /* don't skip any pages */
+#define VACOPT_MINIMAL 0x100 /* do minimal freezing work to prevent or get out of shutdown */
/*
* Values used by index_cleanup and truncate params.
emergency-test.sql
Description: application/sql
