Multixacts and Wraparound

*** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *************** *** 4730,4735 **** COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; --- 4730,4762 ---- + + autovacuum_multixact_freeze_max_age (integer) + + autovacuum_multixact_freeze_max_age configuration parameter + + + + Specifies the maximum age (in multixacts) that a table's + pg_class.relminmxid field can + attain before a VACUUM operation is forced to + prevent multixact ID wraparound within the table. + Note that the system will launch autovacuum processes to + prevent wraparound even when autovacuum is otherwise disabled. + + + + Vacuuming multixacts also allows removal of old files from the + pg_multixact/members and pg_multixact/offsets + subdirectories, which is why the default is a relatively low + 400 million multixacts. + This parameter can only be set at server start, but the setting + can be reduced for individual tables by changing storage parameters. + For more information see . + + + + autovacuum_vacuum_cost_delay (integer) *************** *** 5138,5144 **** COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; pg_class.relfrozenxid field has reached the age specified by this setting. The default is 150 million transactions. Although users can set this value anywhere from zero to ! one billion, VACUUM will silently limit the effective value to 95% of , so that a periodical manual VACUUM has a chance to run before an anti-wraparound autovacuum is launched for the table. For more --- 5165,5171 ---- pg_class.relfrozenxid field has reached the age specified by this setting. The default is 150 million transactions. Although users can set this value anywhere from zero to ! two billions, VACUUM will silently limit the effective value to 95% of , so that a periodical manual VACUUM has a chance to run before an anti-wraparound autovacuum is launched for the table. For more *************** *** 5169,5174 **** COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; --- 5196,5242 ---- + + vacuum_multixact_freeze_table_age (integer) + + vacuum_multixact_freeze_table_age configuration parameter + + + + VACUUM performs a whole-table scan if the table's + pg_class.relminmxid field has reached + the age specified by this setting. The default is 150 million multixacts. + Although users can set this value anywhere from zero to two billions, + VACUUM will silently limit the effective value to 95% of + , so that a + periodical manual VACUUM has a chance to run before an + anti-wraparound is launched for the table. + For more information see . + + + + + + vacuum_multixact_freeze_min_age (integer) + + vacuum_multixact_freeze_min_age configuration parameter + + + + Specifies the cutoff age (in multixacts) that VACUUM + should use to decide whether to replace multixact IDs with a newer + transaction ID or multixact ID while scanning a table. The default + is 5 million multixacts. + Although users can set this value anywhere from zero to one billion, + VACUUM will silently limit the effective value to half + the value of , + so that there is not an unreasonably short time between forced + autovacuums. + For more information see . + + + + bytea_output (enum) *** a/doc/src/sgml/maintenance.sgml --- b/doc/src/sgml/maintenance.sgml *************** *** 108,114 **** To protect against loss of very old data due to ! transaction ID wraparound. --- 108,115 ---- To protect against loss of very old data due to ! transaction ID wraparound or ! multixact ID wraparound. *************** *** 379,384 **** --- 380,390 ---- wraparound + + wraparound + of transaction IDs + + PostgreSQL's MVCC transaction semantics depend on being able to compare transaction ID (XID) *************** *** 599,604 **** HINT: Stop the postmaster and use a standalone backend to VACUUM in "mydb". --- 605,658 ---- page for details about using a single-user backend. + + Multixacts and Wraparound + + + MultiXactId + + + + wraparound + of multixact IDs + + + + Multixacts are used to implement row locking by + multiple transactions: since there is limited space in the tuple + header to store lock information, that information is stored as a + multixact separately in the pg_multixact subdirectory, + and only its ID is in the xmax field + in the tuple header. + Similar to transaction IDs, multixact IDs are implemented as a + 32-bit counter and corresponding storage, all of which requires + careful aging management, storage cleanup, and wraparound handling. + + + + During a VACUUM table scan, either partial or of the whole + table, any multixact ID older than + + is replaced by a different value, which can be the zero value, a single + transaction ID, or a newer multixact ID. For each table, + pg_class.relminmxid stores the oldest + possible value still stored in any tuple of that table. Every time this + value is older than + , a whole-table + scan is forced. Whole-table VACUUM scans, regardless of + what causes them, enable advancing the value for that table. + Eventually, as all tables in all databases are scanned and their + oldest multixact values are advanced, on-disk storage for older + multixacts can be removed. + + + + As a safety device, a whole-table vacuum scan will occur for any table + whose multixact-age is greater than + . + This will occur even if autovacuum is nominally disabled. + + *** a/doc/src/sgml/ref/create_table.sgml --- b/doc/src/sgml/ref/create_table.sgml *************** *** 981,987 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI Custom parameter. Note that autovacuum will ignore attempts to set a per-table ! autovacuum_freeze_min_age larger than the half system-wide setting. --- 981,987 ---- Custom parameter. Note that autovacuum will ignore attempts to set a per-table ! autovacuum_freeze_min_age larger than half the system-wide setting. *************** *** 1010,1015 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI --- 1010,1052 ---- + + autovacuum_multixact_freeze_min_age, toast.autovacuum_multixact_freeze_min_age (integer) + + + Custom parameter. + Note that autovacuum will ignore attempts to set a per-table + autovacuum_multixact_freeze_min_age larger than half the + system-wide + setting. + + + + + + autovacuum_multixact_freeze_max_age, toast.autovacuum_multixact_freeze_max_age (integer) + + + Custom parameter. Note + that autovacuum will ignore attempts to set a per-table + autovacuum_multixact_freeze_max_age larger than the + system-wide setting (it can only be set smaller). Note that while you + can set autovacuum_multixact_freeze_max_age very small, + or even zero, this is usually unwise since it will force frequent + vacuuming. + + + + + + autovacuum_multixact_freeze_table_age, toast.autovacuum_multixact_freeze_table_age (integer) + + + Custom parameter. + + + + *** a/src/backend/access/common/reloptions.c --- b/src/backend/access/common/reloptions.c *************** *** 164,169 **** static relopt_int intRelOpts[] = --- 164,177 ---- }, { { + "autovacuum_multixact_freeze_min_age", + "Minimum MultiXact age at which VACUUM should freeze a row MultiXact's, for autovacuum", + RELOPT_KIND_HEAP | RELOPT_KIND_TOAST + }, + -1, 0, 1000000000 + }, + { + { "autovacuum_freeze_max_age", "Age at which to autovacuum a table to prevent transaction ID wraparound", RELOPT_KIND_HEAP | RELOPT_KIND_TOAST *************** *** 172,182 **** static relopt_int intRelOpts[] = --- 180,205 ---- }, { { + "autovacuum_multixact_freeze_max_age", + "MultiXact age at which to autovacuum a table to prevent MultiXact wraparound", + RELOPT_KIND_HEAP | RELOPT_KIND_TOAST + }, + -1, 100000000, 2000000000 + }, + { + { "autovacuum_freeze_table_age", "Age at which VACUUM should perform a full table sweep to replace old Xid values with FrozenXID", RELOPT_KIND_HEAP | RELOPT_KIND_TOAST }, -1, 0, 2000000000 }, + { + { + "autovacuum_multixact_freeze_table_age", + "Age of MultiXact at which VACUUM should perform a full table sweep to replace old MultiXact values with newer ones", + RELOPT_KIND_HEAP | RELOPT_KIND_TOAST + }, -1, 0, 2000000000 + }, /* list terminator */ {{NULL}} }; *** a/src/backend/access/transam/multixact.c --- b/src/backend/access/transam/multixact.c *************** *** 2055,2065 **** SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid) Assert(MultiXactIdIsValid(oldest_datminmxid)); /* ! * The place where we actually get into deep trouble is halfway around ! * from the oldest potentially-existing XID/multi. (This calculation is ! * probably off by one or two counts for Xids, because the special XIDs ! * reduce the size of the loop a little bit. But we throw in plenty of ! * slop below, so it doesn't matter.) */ multiWrapLimit = oldest_datminmxid + (MaxMultiXactId >> 1); if (multiWrapLimit < FirstMultiXactId) --- 2055,2067 ---- Assert(MultiXactIdIsValid(oldest_datminmxid)); /* ! * Since multixacts wrap differently from transaction IDs, this logic is ! * not entirely correct: in some scenarios we could go for longer than 2 ! * billion multixacts without seeing any data loss, and in some others we ! * could get in trouble before that if the new pg_multixact/members data ! * stomps on the previous cycle's data. For lack of a better mechanism we ! * use the same logic as for transaction IDs, that is, start taking action ! * halfway around the oldest potentially-existing multixact. */ multiWrapLimit = oldest_datminmxid + (MaxMultiXactId >> 1); if (multiWrapLimit < FirstMultiXactId) *************** *** 2093,2104 **** SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid) /* * We'll start trying to force autovacuums when oldest_datminmxid gets to ! * be more than autovacuum_freeze_max_age mxids old. * ! * It's a bit ugly to just reuse limits for xids that way, but it doesn't ! * seem worth adding separate GUCs for that purpose. */ ! multiVacLimit = oldest_datminmxid + autovacuum_freeze_max_age; if (multiVacLimit < FirstMultiXactId) multiVacLimit += FirstMultiXactId; --- 2095,2107 ---- /* * We'll start trying to force autovacuums when oldest_datminmxid gets to ! * be more than autovacuum_multixact_freeze_max_age mxids old. * ! * Note: autovacuum_multixact_freeze_max_age is a PGC_POSTMASTER parameter ! * so that we don't have to worry about dealing with on-the-fly changes ! * in its value. See SetTransactionIdLimit. */ ! multiVacLimit = oldest_datminmxid + autovacuum_multixact_freeze_max_age; if (multiVacLimit < FirstMultiXactId) multiVacLimit += FirstMultiXactId; *** a/src/backend/access/transam/varsup.c --- b/src/backend/access/transam/varsup.c *************** *** 313,319 **** SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid) * value. It doesn't look practical to update shared state from a GUC * assign hook (too many processes would try to execute the hook, * resulting in race conditions as well as crashes of those not connected ! * to shared memory). Perhaps this can be improved someday. */ xidVacLimit = oldest_datfrozenxid + autovacuum_freeze_max_age; if (xidVacLimit < FirstNormalTransactionId) --- 313,320 ---- * value. It doesn't look practical to update shared state from a GUC * assign hook (too many processes would try to execute the hook, * resulting in race conditions as well as crashes of those not connected ! * to shared memory). Perhaps this can be improved someday. See also ! * SetMultiXactIdLimit. */ xidVacLimit = oldest_datfrozenxid + autovacuum_freeze_max_age; if (xidVacLimit < FirstNormalTransactionId) *** a/src/backend/commands/cluster.c --- b/src/backend/commands/cluster.c *************** *** 64,72 **** typedef struct static void rebuild_relation(Relation OldHeap, Oid indexOid, ! int freeze_min_age, int freeze_table_age, bool verbose); static void copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, ! int freeze_min_age, int freeze_table_age, bool verbose, bool *pSwapToastByContent, TransactionId *pFreezeXid, MultiXactId *pCutoffMulti); static List *get_tables_to_cluster(MemoryContext cluster_context); --- 64,76 ---- static void rebuild_relation(Relation OldHeap, Oid indexOid, ! int freeze_min_age, int freeze_table_age, ! int multixact_freeze_min_age, int multixact_freeze_table_age, ! bool verbose); static void copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, ! int freeze_min_age, int freeze_table_age, ! int multixact_freeze_min_age, int multixact_freeze_table_age, ! bool verbose, bool *pSwapToastByContent, TransactionId *pFreezeXid, MultiXactId *pCutoffMulti); static List *get_tables_to_cluster(MemoryContext cluster_context); *************** *** 179,185 **** cluster(ClusterStmt *stmt, bool isTopLevel) * Do the job. We use a -1 freeze_min_age to avoid having CLUSTER * freeze tuples earlier than a plain VACUUM would. */ ! cluster_rel(tableOid, indexOid, false, stmt->verbose, -1, -1); } else { --- 183,189 ---- * Do the job. We use a -1 freeze_min_age to avoid having CLUSTER * freeze tuples earlier than a plain VACUUM would. */ ! cluster_rel(tableOid, indexOid, false, stmt->verbose, -1, -1, -1, -1); } else { *************** *** 230,236 **** cluster(ClusterStmt *stmt, bool isTopLevel) PushActiveSnapshot(GetTransactionSnapshot()); /* Do the job. As above, use a -1 freeze_min_age. */ cluster_rel(rvtc->tableOid, rvtc->indexOid, true, stmt->verbose, ! -1, -1); PopActiveSnapshot(); CommitTransactionCommand(); } --- 234,240 ---- PushActiveSnapshot(GetTransactionSnapshot()); /* Do the job. As above, use a -1 freeze_min_age. */ cluster_rel(rvtc->tableOid, rvtc->indexOid, true, stmt->verbose, ! -1, -1, -1, -1); PopActiveSnapshot(); CommitTransactionCommand(); } *************** *** 262,268 **** cluster(ClusterStmt *stmt, bool isTopLevel) */ void cluster_rel(Oid tableOid, Oid indexOid, bool recheck, bool verbose, ! int freeze_min_age, int freeze_table_age) { Relation OldHeap; --- 266,273 ---- */ void cluster_rel(Oid tableOid, Oid indexOid, bool recheck, bool verbose, ! int freeze_min_age, int freeze_table_age, ! int multixact_freeze_min_age, int multixact_freeze_table_age) { Relation OldHeap; *************** *** 407,412 **** cluster_rel(Oid tableOid, Oid indexOid, bool recheck, bool verbose, --- 412,418 ---- /* rebuild_relation does all the dirty work */ rebuild_relation(OldHeap, indexOid, freeze_min_age, freeze_table_age, + multixact_freeze_min_age, multixact_freeze_table_age, verbose); /* NB: rebuild_relation does heap_close() on OldHeap */ *************** *** 566,572 **** mark_index_clustered(Relation rel, Oid indexOid, bool is_internal) */ static void rebuild_relation(Relation OldHeap, Oid indexOid, ! int freeze_min_age, int freeze_table_age, bool verbose) { Oid tableOid = RelationGetRelid(OldHeap); Oid tableSpace = OldHeap->rd_rel->reltablespace; --- 572,580 ---- */ static void rebuild_relation(Relation OldHeap, Oid indexOid, ! int freeze_min_age, int freeze_table_age, ! int multixact_freeze_min_age, int multixact_freeze_table_age, ! bool verbose) { Oid tableOid = RelationGetRelid(OldHeap); Oid tableSpace = OldHeap->rd_rel->reltablespace; *************** *** 591,597 **** rebuild_relation(Relation OldHeap, Oid indexOid, /* Copy the heap data into the new table in the desired order */ copy_heap_data(OIDNewHeap, tableOid, indexOid, ! freeze_min_age, freeze_table_age, verbose, &swap_toast_by_content, &frozenXid, &cutoffMulti); /* --- 599,607 ---- /* Copy the heap data into the new table in the desired order */ copy_heap_data(OIDNewHeap, tableOid, indexOid, ! freeze_min_age, freeze_table_age, ! multixact_freeze_min_age, multixact_freeze_table_age, ! verbose, &swap_toast_by_content, &frozenXid, &cutoffMulti); /* *************** *** 733,739 **** make_new_heap(Oid OIDOldHeap, Oid NewTableSpace) */ static void copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, ! int freeze_min_age, int freeze_table_age, bool verbose, bool *pSwapToastByContent, TransactionId *pFreezeXid, MultiXactId *pCutoffMulti) { --- 743,751 ---- */ static void copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, ! int freeze_min_age, int freeze_table_age, ! int multixact_freeze_min_age, int multixact_freeze_table_age, ! bool verbose, bool *pSwapToastByContent, TransactionId *pFreezeXid, MultiXactId *pCutoffMulti) { *************** *** 849,854 **** copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, --- 861,867 ---- * compute xids used to freeze and weed out dead tuples. */ vacuum_set_xid_limits(freeze_min_age, freeze_table_age, + multixact_freeze_min_age, multixact_freeze_table_age, OldHeap->rd_rel->relisshared, &OldestXmin, &FreezeXid, NULL, &MultiXactCutoff, NULL); *** a/src/backend/commands/vacuum.c --- b/src/backend/commands/vacuum.c *************** *** 55,60 **** --- 55,62 ---- */ int vacuum_freeze_min_age; int vacuum_freeze_table_age; + int vacuum_multixact_freeze_min_age; + int vacuum_multixact_freeze_table_age; /* A few variables that don't seem worth passing around as parameters */ *************** *** 398,403 **** get_rel_oids(Oid relid, const RangeVar *vacrel) --- 400,407 ---- void vacuum_set_xid_limits(int freeze_min_age, int freeze_table_age, + int multixact_freeze_min_age, + int multixact_freeze_table_age, bool sharedRel, TransactionId *oldestXmin, TransactionId *freezeLimit, *************** *** 406,414 **** vacuum_set_xid_limits(int freeze_min_age, --- 410,420 ---- MultiXactId *mxactFullScanLimit) { int freezemin; + int mxid_freezemin; TransactionId limit; TransactionId safeLimit; MultiXactId mxactLimit; + MultiXactId safeMxactLimit; /* * We can always ignore processes running lazy vacuum. This is because we *************** *** 462,474 **** vacuum_set_xid_limits(int freeze_min_age, *freezeLimit = limit; /* ! * simplistic MultiXactId removal limit: use the same policy as for ! * freezing Xids (except we use the oldest known mxact instead of the ! * current next value). */ ! mxactLimit = GetOldestMultiXactId() - freezemin; if (mxactLimit < FirstMultiXactId) mxactLimit = FirstMultiXactId; *multiXactCutoff = mxactLimit; if (xidFullScanLimit != NULL) --- 468,503 ---- *freezeLimit = limit; /* ! * Determine the minimum multixact freeze age to use: as specified by ! * caller, or vacuum_multixact_freeze_min_age, but in any case not more ! * than half autovacuum_multixact_freeze_max_age, so that autovacuums to ! * prevent MultiXact wraparound won't occur too frequently. */ ! mxid_freezemin = multixact_freeze_min_age; ! if (mxid_freezemin < 0) ! mxid_freezemin = vacuum_multixact_freeze_min_age; ! mxid_freezemin = Min(mxid_freezemin, ! autovacuum_multixact_freeze_max_age / 2); ! Assert(mxid_freezemin >= 0); ! ! /* compute the cutoff multi, being careful to generate a valid value */ ! mxactLimit = GetOldestMultiXactId() - mxid_freezemin; if (mxactLimit < FirstMultiXactId) mxactLimit = FirstMultiXactId; + + safeMxactLimit = + ReadNextMultiXactId() - autovacuum_multixact_freeze_max_age; + if (safeMxactLimit < FirstMultiXactId) + safeMxactLimit = FirstMultiXactId; + + if (MultiXactIdPrecedes(mxactLimit, safeMxactLimit)) + { + ereport(WARNING, + (errmsg("oldest MultiXact is far in the past"), + errhint("Close open transactions with MultiXacts soon to avoid wraparound problems."))); + mxactLimit = safeMxactLimit; + } + *multiXactCutoff = mxactLimit; if (xidFullScanLimit != NULL) *************** *** 503,509 **** vacuum_set_xid_limits(int freeze_min_age, /* * Compute MultiXactId limit to cause a full-table vacuum, being * careful not to generate an invalid multi. We just copy the logic ! * (and limits) from plain XIDs here. */ mxactLimit = ReadNextMultiXactId() - freezetable; if (mxactLimit < FirstMultiXactId) --- 532,548 ---- /* * Compute MultiXactId limit to cause a full-table vacuum, being * careful not to generate an invalid multi. We just copy the logic ! * from plain XIDs here. ! */ ! freezetable = multixact_freeze_table_age; ! if (freezetable < 0) ! freezetable = vacuum_multixact_freeze_table_age; ! freezetable = Min(freezetable, ! autovacuum_multixact_freeze_max_age * 0.95); ! Assert(freezetable >= 0); ! ! /* Compute MultiXact limit causing a full-table vacuum, being careful ! * to generate a valid MultiXact value. */ mxactLimit = ReadNextMultiXactId() - freezetable; if (mxactLimit < FirstMultiXactId) *************** *** 511,516 **** vacuum_set_xid_limits(int freeze_min_age, --- 550,559 ---- *mxactFullScanLimit = mxactLimit; } + else + { + Assert(mxactFullScanLimit == NULL); + } } /* *************** *** 1150,1156 **** vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast, bool for_wraparound) /* VACUUM FULL is now a variant of CLUSTER; see cluster.c */ cluster_rel(relid, InvalidOid, false, (vacstmt->options & VACOPT_VERBOSE) != 0, ! vacstmt->freeze_min_age, vacstmt->freeze_table_age); } else lazy_vacuum_rel(onerel, vacstmt, vac_strategy); --- 1193,1201 ---- /* VACUUM FULL is now a variant of CLUSTER; see cluster.c */ cluster_rel(relid, InvalidOid, false, (vacstmt->options & VACOPT_VERBOSE) != 0, ! vacstmt->freeze_min_age, vacstmt->freeze_table_age, ! vacstmt->multixact_freeze_min_age, ! vacstmt->multixact_freeze_table_age); } else lazy_vacuum_rel(onerel, vacstmt, vac_strategy); *** a/src/backend/commands/vacuumlazy.c --- b/src/backend/commands/vacuumlazy.c *************** *** 203,208 **** lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt, --- 203,210 ---- vac_strategy = bstrategy; vacuum_set_xid_limits(vacstmt->freeze_min_age, vacstmt->freeze_table_age, + vacstmt->multixact_freeze_min_age, + vacstmt->multixact_freeze_table_age, onerel->rd_rel->relisshared, &OldestXmin, &FreezeLimit, &xidFullScanLimit, &MultiXactCutoff, &mxactFullScanLimit); *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** *** 3206,3211 **** _copyVacuumStmt(const VacuumStmt *from) --- 3206,3213 ---- COPY_SCALAR_FIELD(options); COPY_SCALAR_FIELD(freeze_min_age); COPY_SCALAR_FIELD(freeze_table_age); + COPY_SCALAR_FIELD(multixact_freeze_min_age); + COPY_SCALAR_FIELD(multixact_freeze_table_age); COPY_NODE_FIELD(relation); COPY_NODE_FIELD(va_cols); *** a/src/backend/nodes/equalfuncs.c --- b/src/backend/nodes/equalfuncs.c *************** *** 1496,1501 **** _equalVacuumStmt(const VacuumStmt *a, const VacuumStmt *b) --- 1496,1503 ---- COMPARE_SCALAR_FIELD(options); COMPARE_SCALAR_FIELD(freeze_min_age); COMPARE_SCALAR_FIELD(freeze_table_age); + COMPARE_SCALAR_FIELD(multixact_freeze_min_age); + COMPARE_SCALAR_FIELD(multixact_freeze_table_age); COMPARE_NODE_FIELD(relation); COMPARE_NODE_FIELD(va_cols); *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** *** 8474,8479 **** VacuumStmt: VACUUM opt_full opt_freeze opt_verbose --- 8474,8481 ---- n->options |= VACOPT_VERBOSE; n->freeze_min_age = $3 ? 0 : -1; n->freeze_table_age = $3 ? 0 : -1; + n->multixact_freeze_min_age = $3 ? 0 : -1; + n->multixact_freeze_table_age = $3 ? 0 : -1; n->relation = NULL; n->va_cols = NIL; $$ = (Node *)n; *************** *** 8488,8493 **** VacuumStmt: VACUUM opt_full opt_freeze opt_verbose --- 8490,8497 ---- n->options |= VACOPT_VERBOSE; n->freeze_min_age = $3 ? 0 : -1; n->freeze_table_age = $3 ? 0 : -1; + n->multixact_freeze_min_age = $3 ? 0 : -1; + n->multixact_freeze_table_age = $3 ? 0 : -1; n->relation = $5; n->va_cols = NIL; $$ = (Node *)n; *************** *** 8502,8507 **** VacuumStmt: VACUUM opt_full opt_freeze opt_verbose --- 8506,8513 ---- n->options |= VACOPT_VERBOSE; n->freeze_min_age = $3 ? 0 : -1; n->freeze_table_age = $3 ? 0 : -1; + n->multixact_freeze_min_age = $3 ? 0 : -1; + n->multixact_freeze_table_age = $3 ? 0 : -1; $$ = (Node *)n; } | VACUUM '(' vacuum_option_list ')' *************** *** 8509,8517 **** VacuumStmt: VACUUM opt_full opt_freeze opt_verbose --- 8515,8531 ---- VacuumStmt *n = makeNode(VacuumStmt); n->options = VACOPT_VACUUM | $3; if (n->options & VACOPT_FREEZE) + { n->freeze_min_age = n->freeze_table_age = 0; + n->multixact_freeze_min_age = 0; + n->multixact_freeze_table_age = 0; + } else + { n->freeze_min_age = n->freeze_table_age = -1; + n->multixact_freeze_min_age = -1; + n->multixact_freeze_table_age = -1; + } n->relation = NULL; n->va_cols = NIL; $$ = (Node *) n; *************** *** 8521,8529 **** VacuumStmt: VACUUM opt_full opt_freeze opt_verbose --- 8535,8551 ---- VacuumStmt *n = makeNode(VacuumStmt); n->options = VACOPT_VACUUM | $3; if (n->options & VACOPT_FREEZE) + { n->freeze_min_age = n->freeze_table_age = 0; + n->multixact_freeze_min_age = 0; + n->multixact_freeze_table_age = 0; + } else + { n->freeze_min_age = n->freeze_table_age = -1; + n->multixact_freeze_min_age = -1; + n->multixact_freeze_table_age = -1; + } n->relation = $5; n->va_cols = $6; if (n->va_cols != NIL) /* implies analyze */ *************** *** 8553,8558 **** AnalyzeStmt: --- 8575,8582 ---- n->options |= VACOPT_VERBOSE; n->freeze_min_age = -1; n->freeze_table_age = -1; + n->multixact_freeze_min_age = -1; + n->multixact_freeze_table_age = -1; n->relation = NULL; n->va_cols = NIL; $$ = (Node *)n; *************** *** 8565,8570 **** AnalyzeStmt: --- 8589,8596 ---- n->options |= VACOPT_VERBOSE; n->freeze_min_age = -1; n->freeze_table_age = -1; + n->multixact_freeze_min_age = -1; + n->multixact_freeze_table_age = -1; n->relation = $3; n->va_cols = $4; $$ = (Node *)n; *** a/src/backend/postmaster/autovacuum.c --- b/src/backend/postmaster/autovacuum.c *************** *** 116,121 **** double autovacuum_vac_scale; --- 116,122 ---- int autovacuum_anl_thresh; double autovacuum_anl_scale; int autovacuum_freeze_max_age; + int autovacuum_multixact_freeze_max_age; int autovacuum_vac_cost_delay; int autovacuum_vac_cost_limit; *************** *** 144,149 **** static MultiXactId recentMulti; --- 145,152 ---- /* Default freeze ages to use for autovacuum (varies by database) */ static int default_freeze_min_age; static int default_freeze_table_age; + static int default_multixact_freeze_min_age; + static int default_multixact_freeze_table_age; /* Memory context for long-lived data */ static MemoryContext AutovacMemCxt; *************** *** 185,190 **** typedef struct autovac_table --- 188,195 ---- bool at_doanalyze; int at_freeze_min_age; int at_freeze_table_age; + int at_multixact_freeze_min_age; + int at_multixact_freeze_table_age; int at_vacuum_cost_delay; int at_vacuum_cost_limit; bool at_wraparound; *************** *** 1129,1135 **** do_start_worker(void) /* Also determine the oldest datminmxid we will consider. */ recentMulti = ReadNextMultiXactId(); ! multiForceLimit = recentMulti - autovacuum_freeze_max_age; if (multiForceLimit < FirstMultiXactId) multiForceLimit -= FirstMultiXactId; --- 1134,1140 ---- /* Also determine the oldest datminmxid we will consider. */ recentMulti = ReadNextMultiXactId(); ! multiForceLimit = recentMulti - autovacuum_multixact_freeze_max_age; if (multiForceLimit < FirstMultiXactId) multiForceLimit -= FirstMultiXactId; *************** *** 1955,1965 **** do_autovacuum(void) --- 1960,1974 ---- { default_freeze_min_age = 0; default_freeze_table_age = 0; + default_multixact_freeze_min_age = 0; + default_multixact_freeze_table_age = 0; } else { default_freeze_min_age = vacuum_freeze_min_age; default_freeze_table_age = vacuum_freeze_table_age; + default_multixact_freeze_min_age = vacuum_multixact_freeze_min_age; + default_multixact_freeze_table_age = vacuum_multixact_freeze_table_age; } ReleaseSysCache(tuple); *************** *** 2510,2515 **** table_recheck_autovac(Oid relid, HTAB *table_toast_map, --- 2519,2526 ---- { int freeze_min_age; int freeze_table_age; + int multixact_freeze_min_age; + int multixact_freeze_table_age; int vac_cost_limit; int vac_cost_delay; *************** *** 2543,2554 **** table_recheck_autovac(Oid relid, HTAB *table_toast_map, --- 2554,2577 ---- ? avopts->freeze_table_age : default_freeze_table_age; + multixact_freeze_min_age = (avopts && + avopts->multixact_freeze_min_age >= 0) + ? avopts->multixact_freeze_min_age + : default_multixact_freeze_min_age; + + multixact_freeze_table_age = (avopts && + avopts->multixact_freeze_table_age >= 0) + ? avopts->multixact_freeze_table_age + : default_multixact_freeze_table_age; + tab = palloc(sizeof(autovac_table)); tab->at_relid = relid; tab->at_dovacuum = dovacuum; tab->at_doanalyze = doanalyze; tab->at_freeze_min_age = freeze_min_age; tab->at_freeze_table_age = freeze_table_age; + tab->at_multixact_freeze_min_age = multixact_freeze_min_age; + tab->at_multixact_freeze_table_age = multixact_freeze_table_age; tab->at_vacuum_cost_limit = vac_cost_limit; tab->at_vacuum_cost_delay = vac_cost_delay; tab->at_wraparound = wraparound; *************** *** 2754,2759 **** autovacuum_do_vac_analyze(autovac_table *tab, --- 2777,2784 ---- vacstmt.options |= VACOPT_ANALYZE; vacstmt.freeze_min_age = tab->at_freeze_min_age; vacstmt.freeze_table_age = tab->at_freeze_table_age; + vacstmt.multixact_freeze_min_age = tab->at_multixact_freeze_min_age; + vacstmt.multixact_freeze_table_age = tab->at_multixact_freeze_table_age; /* we pass the OID, but might need this anyway for an error message */ vacstmt.relation = &rangevar; vacstmt.va_cols = NIL; *** a/src/backend/utils/misc/guc.c --- b/src/backend/utils/misc/guc.c *************** *** 1907,1912 **** static struct config_int ConfigureNamesInt[] = --- 1907,1932 ---- }, { + {"vacuum_multixact_freeze_min_age", PGC_USERSET, CLIENT_CONN_STATEMENT, + gettext_noop("Minimum age at which VACUUM should freeze a MultiXactId in a table row."), + NULL + }, + &vacuum_multixact_freeze_min_age, + 5000000, 0, 1000000000, + NULL, NULL, NULL + }, + + { + {"vacuum_multixact_freeze_table_age", PGC_USERSET, CLIENT_CONN_STATEMENT, + gettext_noop("Multixact age at which VACUUM should scan whole table to freeze tuples."), + NULL + }, + &vacuum_multixact_freeze_table_age, + 150000000, 0, 2000000000, + NULL, NULL, NULL + }, + + { {"vacuum_defer_cleanup_age", PGC_SIGHUP, REPLICATION_MASTER, gettext_noop("Number of transactions by which VACUUM and HOT cleanup should be deferred, if any."), NULL *************** *** 2296,2301 **** static struct config_int ConfigureNamesInt[] = --- 2316,2331 ---- NULL, NULL, NULL }, { + /* see varsup.c for why this is PGC_POSTMASTER not PGC_SIGHUP */ + {"autovacuum_multixact_freeze_max_age", PGC_POSTMASTER, AUTOVACUUM, + gettext_noop("Multixact age at which to autovacuum a table to prevent multixact wraparound."), + NULL + }, + &autovacuum_multixact_freeze_max_age, + 400000000, 10000000, 2000000000, + NULL, NULL, NULL + }, + { /* see max_connections */ {"autovacuum_max_workers", PGC_POSTMASTER, AUTOVACUUM, gettext_noop("Sets the maximum number of simultaneously running autovacuum worker processes."), *** a/src/backend/utils/misc/postgresql.conf.sample --- b/src/backend/utils/misc/postgresql.conf.sample *************** *** 362,373 **** # panic #log_min_error_statement = error # values in order of decreasing detail: ! # debug5 # debug4 # debug3 # debug2 # debug1 ! # info # notice # warning # error --- 362,373 ---- # panic #log_min_error_statement = error # values in order of decreasing detail: ! # debug5 # debug4 # debug3 # debug2 # debug1 ! # info # notice # warning # error *************** *** 431,437 **** #track_counts = on #track_io_timing = off #track_functions = none # none, pl, all ! #track_activity_query_size = 1024 # (change requires restart) #update_process_title = on #stats_temp_directory = 'pg_stat_tmp' --- 431,437 ---- #track_counts = on #track_io_timing = off #track_functions = none # none, pl, all ! #track_activity_query_size = 1024 # (change requires restart) #update_process_title = on #stats_temp_directory = 'pg_stat_tmp' *************** *** 465,470 **** --- 465,473 ---- #autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze #autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum # (change requires restart) + #autovacuum_multixact_freeze_max_age = 400000000 # maximum Multixact age + # before forced vacuum + # (change requires restart) #autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for # autovacuum, in milliseconds; # -1 means use vacuum_cost_delay *************** *** 492,497 **** --- 495,502 ---- #lock_timeout = 0 # in milliseconds, 0 is disabled #vacuum_freeze_min_age = 50000000 #vacuum_freeze_table_age = 150000000 + #vacuum_multixact_freeze_min_age = 5000000 + #vacuum_multixact_freeze_table_age = 150000000 #bytea_output = 'hex' # hex, escape #xmlbinary = 'base64' #xmloption = 'content' *** a/src/include/commands/cluster.h --- b/src/include/commands/cluster.h *************** *** 20,26 **** extern void cluster(ClusterStmt *stmt, bool isTopLevel); extern void cluster_rel(Oid tableOid, Oid indexOid, bool recheck, ! bool verbose, int freeze_min_age, int freeze_table_age); extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid, bool recheck, LOCKMODE lockmode); extern void mark_index_clustered(Relation rel, Oid indexOid, bool is_internal); --- 20,27 ---- extern void cluster(ClusterStmt *stmt, bool isTopLevel); extern void cluster_rel(Oid tableOid, Oid indexOid, bool recheck, ! bool verbose, int freeze_min_age, int freeze_table_age, ! int multixact_freeze_min_age, int multixact_freeze_table_age); extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid, bool recheck, LOCKMODE lockmode); extern void mark_index_clustered(Relation rel, Oid indexOid, bool is_internal); *** a/src/include/commands/vacuum.h --- b/src/include/commands/vacuum.h *************** *** 136,141 **** extern PGDLLIMPORT int default_statistics_target; /* PGDLLIMPORT for --- 136,143 ---- * PostGIS */ extern int vacuum_freeze_min_age; extern int vacuum_freeze_table_age; + extern int vacuum_multixact_freeze_min_age; + extern int vacuum_multixact_freeze_table_age; /* in commands/vacuum.c */ *************** *** 156,161 **** extern void vac_update_relstats(Relation relation, --- 158,165 ---- TransactionId frozenxid, MultiXactId minmulti); extern void vacuum_set_xid_limits(int freeze_min_age, int freeze_table_age, + int multixact_freeze_min_age, + int multixact_freeze_table_age, bool sharedRel, TransactionId *oldestXmin, TransactionId *freezeLimit, *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** *** 2430,2435 **** typedef struct VacuumStmt --- 2430,2437 ---- int options; /* OR of VacuumOption flags */ int freeze_min_age; /* min freeze age, or -1 to use default */ int freeze_table_age; /* age at which to scan whole table */ + int multixact_freeze_min_age; /* min multixact freeze age, or -1 to use default */ + int multixact_freeze_table_age; /* multixact age at which to scan whole table */ RangeVar *relation; /* single table to process, or NULL */ List *va_cols; /* list of column names, or NIL for all */ } VacuumStmt; *** a/src/include/postmaster/autovacuum.h --- b/src/include/postmaster/autovacuum.h *************** *** 24,29 **** extern double autovacuum_vac_scale; --- 24,30 ---- extern int autovacuum_anl_thresh; extern double autovacuum_anl_scale; extern int autovacuum_freeze_max_age; + extern int autovacuum_multixact_freeze_max_age; extern int autovacuum_vac_cost_delay; extern int autovacuum_vac_cost_limit; *** a/src/include/utils/rel.h --- b/src/include/utils/rel.h *************** *** 198,203 **** typedef struct AutoVacOpts --- 198,206 ---- int freeze_min_age; int freeze_max_age; int freeze_table_age; + int multixact_freeze_min_age; + int multixact_freeze_max_age; + int multixact_freeze_table_age; float8 vacuum_scale_factor; float8 analyze_scale_factor; } AutoVacOpts;