enhance wraparound warnings
Started by Nathan Bossartabout 2 months ago4 messages
varsup.c has the following comment:
/*
* We'll start complaining loudly when we get within 40M transactions of
* data loss. This is kind of arbitrary, but if you let your gas gauge
* get down to 2% of full, would you be looking for the next gas station?
* We need to be fairly liberal about this number because there are lots
* of scenarios where most transactions are done by automatic clients that
* won't pay attention to warnings. (No, we're not gonna make this
* configurable. If you know enough to configure it, you know enough to
* not get in this kind of trouble in the first place.)
*/
I don't know about you, but I start getting antsy around a quarter tank.
In any case, I'm told that even 40M transactions aren't enough time to
react these days. Attached are a few patches to enhance the wraparound
warnings.
* 0001 adds a "percent remaining" detail message to the existing WARNING.
The idea is that "1.86% of transaction IDs" is both easier to understand
and better indicates urgency than "39985967 transactions".
* 0002 bumps the warning limit from 40M to 100M to give folks some more
time to react.
* 0003 adds an early warning system for when fewer than 500M transactions
remain. This system sends a LOG only to the server log every 1M
transactions. The hope is that this gets someone's attention sooner
without flooding the application and server log.
Thoughts?
--
nathan
Attachments:
v1-0001-Add-percentage-of-transaction-IDs-that-are-availa.patchtext/plain; charset=us-asciiDownload
From 4fd750a67e98cb7b86970ecca5ff3a26fd1f7690 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 14 Nov 2025 09:59:15 -0600
Subject: [PATCH v1 1/3] Add percentage of transaction IDs that are available
to wraparound warnings.
---
doc/src/sgml/maintenance.sgml | 1 +
src/backend/access/transam/multixact.c | 10 ++++++++++
src/backend/access/transam/varsup.c | 8 ++++++++
3 files changed, 19 insertions(+)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 120bac8875f..b89278ef032 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -674,6 +674,7 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
<programlisting>
WARNING: database "mydb" must be vacuumed within 39985967 transactions
+DETAIL: Approximately 1.86% of transactions IDs are available for use.
HINT: To avoid XID assignment failures, execute a database-wide VACUUM in that database.
</programlisting>
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 9d5f130af7e..63eb2548da1 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -1118,6 +1118,8 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
multiWrapLimit - result,
oldest_datname,
multiWrapLimit - result),
+ errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
+ (double) (multiWrapLimit - result) / PG_INT32_MAX * 100),
errhint("Execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
else
@@ -1127,6 +1129,8 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
multiWrapLimit - result,
oldest_datoid,
multiWrapLimit - result),
+ errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
+ (double) (multiWrapLimit - result) / PG_INT32_MAX * 100),
errhint("Execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
}
@@ -1225,6 +1229,8 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
MultiXactState->offsetStopLimit - nextOffset + nmembers,
MultiXactState->oldestMultiXactDB,
MultiXactState->offsetStopLimit - nextOffset + nmembers),
+ errdetail("Approximately %.2f%% of multixact members are available for use.",
+ (double) (MultiXactState->offsetStopLimit - nextOffset + nmembers) / PG_INT32_MAX * 100),
errhint("Execute a database-wide VACUUM in that database with reduced \"vacuum_multixact_freeze_min_age\" and \"vacuum_multixact_freeze_table_age\" settings.")));
ExtendMultiXactMember(nextOffset, nmembers);
@@ -2414,6 +2420,8 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
multiWrapLimit - curMulti,
oldest_datname,
multiWrapLimit - curMulti),
+ errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
+ (double) (multiWrapLimit - curMulti) / PG_INT32_MAX * 100),
errhint("To avoid MultiXactId assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
else
@@ -2423,6 +2431,8 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
multiWrapLimit - curMulti,
oldest_datoid,
multiWrapLimit - curMulti),
+ errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
+ (double) (multiWrapLimit - curMulti) / PG_INT32_MAX * 100),
errhint("To avoid MultiXactId assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
}
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index f8c4dada7c9..962396bae10 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -175,6 +175,8 @@ GetNewTransactionId(bool isSubXact)
(errmsg("database \"%s\" must be vacuumed within %u transactions",
oldest_datname,
xidWrapLimit - xid),
+ errdetail("Approximately %.2f%% of transaction IDs are available for use.",
+ (double) (xidWrapLimit - xid) / PG_INT32_MAX * 100),
errhint("To avoid transaction ID assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
else
@@ -182,6 +184,8 @@ GetNewTransactionId(bool isSubXact)
(errmsg("database with OID %u must be vacuumed within %u transactions",
oldest_datoid,
xidWrapLimit - xid),
+ errdetail("Approximately %.2f%% of transaction IDs are available for use.",
+ (double) (xidWrapLimit - xid) / PG_INT32_MAX * 100),
errhint("To avoid XID assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
}
@@ -490,6 +494,8 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
(errmsg("database \"%s\" must be vacuumed within %u transactions",
oldest_datname,
xidWrapLimit - curXid),
+ errdetail("Approximately %.2f%% of transaction IDs are available for use.",
+ (double) (xidWrapLimit - curXid) / PG_INT32_MAX * 100),
errhint("To avoid XID assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
else
@@ -497,6 +503,8 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
(errmsg("database with OID %u must be vacuumed within %u transactions",
oldest_datoid,
xidWrapLimit - curXid),
+ errdetail("Approximately %.2f%% of transaction IDs are available for use.",
+ (double) (xidWrapLimit - curXid) / PG_INT32_MAX * 100),
errhint("To avoid XID assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
}
--
2.39.5 (Apple Git-154)
v1-0002-Bump-transaction-ID-limit-to-warn-at-100M.patchtext/plain; charset=us-asciiDownload
From 6555ccd8910420f365a253f03327e0067bc0ca66 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 14 Nov 2025 10:28:52 -0600
Subject: [PATCH v1 2/3] Bump transaction ID limit to warn at 100M.
---
doc/src/sgml/maintenance.sgml | 4 ++--
src/backend/access/transam/multixact.c | 6 +++---
src/backend/access/transam/varsup.c | 6 +++---
3 files changed, 8 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index b89278ef032..8a12bd5f65a 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -670,7 +670,7 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
<para>
If for some reason autovacuum fails to clear old XIDs from a table, the
system will begin to emit warning messages like this when the database's
- oldest XIDs reach forty million transactions from the wraparound point:
+ oldest XIDs reach one hundred million transactions from the wraparound point:
<programlisting>
WARNING: database "mydb" must be vacuumed within 39985967 transactions
@@ -824,7 +824,7 @@ HINT: Execute a database-wide VACUUM in that database.
<para>
Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
- system will begin to emit warning messages when the database's oldest MXIDs reach forty
+ system will begin to emit warning messages when the database's oldest MXIDs reach one hundred
million transactions from the wraparound point. And, just as in the XID case, if these
warnings are ignored, the system will refuse to generate new MXIDs once there are fewer
than three million left until wraparound.
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 63eb2548da1..67810ea489a 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2327,16 +2327,16 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
multiStopLimit -= FirstMultiXactId;
/*
- * We'll start complaining loudly when we get within 40M multis of data
+ * We'll start complaining loudly when we get within 100M multis of data
* loss. This is kind of arbitrary, but if you let your gas gauge get
- * down to 2% of full, would you be looking for the next gas station? We
+ * down to 5% of full, would you be looking for the next gas station? We
* need to be fairly liberal about this number because there are lots of
* scenarios where most transactions are done by automatic clients that
* won't pay attention to warnings. (No, we're not gonna make this
* configurable. If you know enough to configure it, you know enough to
* not get in this kind of trouble in the first place.)
*/
- multiWarnLimit = multiWrapLimit - 40000000;
+ multiWarnLimit = multiWrapLimit - 100000000;
if (multiWarnLimit < FirstMultiXactId)
multiWarnLimit -= FirstMultiXactId;
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 962396bae10..5585381bc8c 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -411,16 +411,16 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
xidStopLimit -= FirstNormalTransactionId;
/*
- * We'll start complaining loudly when we get within 40M transactions of
+ * We'll start complaining loudly when we get within 100M transactions of
* data loss. This is kind of arbitrary, but if you let your gas gauge
- * get down to 2% of full, would you be looking for the next gas station?
+ * get down to 5% of full, would you be looking for the next gas station?
* We need to be fairly liberal about this number because there are lots
* of scenarios where most transactions are done by automatic clients that
* won't pay attention to warnings. (No, we're not gonna make this
* configurable. If you know enough to configure it, you know enough to
* not get in this kind of trouble in the first place.)
*/
- xidWarnLimit = xidWrapLimit - 40000000;
+ xidWarnLimit = xidWrapLimit - 100000000;
if (xidWarnLimit < FirstNormalTransactionId)
xidWarnLimit -= FirstNormalTransactionId;
--
2.39.5 (Apple Git-154)
v1-0003-Perodically-emit-server-logs-when-fewer-than-500M.patchtext/plain; charset=us-asciiDownload
From 65c2776462f6023108b1586afc9b9b17927a5bd9 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 14 Nov 2025 10:48:35 -0600
Subject: [PATCH v1 3/3] Perodically emit server logs when fewer than 500M
remaining transaction IDs.
---
src/backend/access/transam/multixact.c | 40 +++++++++++++++++++++++---
src/backend/access/transam/varsup.c | 40 +++++++++++++++++++++++---
src/include/access/transam.h | 5 ++--
3 files changed, 75 insertions(+), 10 deletions(-)
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 67810ea489a..159ae5efb80 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -264,6 +264,7 @@ typedef struct MultiXactStateData
/* support for anti-wraparound measures */
MultiXactId multiVacLimit;
+ MultiXactId multiLogLimit;
MultiXactId multiWarnLimit;
MultiXactId multiStopLimit;
MultiXactId multiWrapLimit;
@@ -1048,6 +1049,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
* If we're past multiVacLimit or the safe threshold for member storage
* space, or we don't know what the safe threshold for member storage is,
* start trying to force autovacuum cycles.
+ * If we're past multiLogLimit, start issuing logs periodically.
* If we're past multiWarnLimit, start issuing warnings.
* If we're past multiStopLimit, refuse to create new MultiXactIds.
*
@@ -1063,6 +1065,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
* possibility of deadlock while doing get_database_name(). First,
* copy all the shared values we'll need in this path.
*/
+ MultiXactId multiLogLimit = MultiXactState->multiLogLimit;
MultiXactId multiWarnLimit = MultiXactState->multiWarnLimit;
MultiXactId multiStopLimit = MultiXactState->multiStopLimit;
MultiXactId multiWrapLimit = MultiXactState->multiWrapLimit;
@@ -1106,13 +1109,27 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
if (IsUnderPostmaster && (result % 65536) == 0)
SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_LAUNCHER);
- if (!MultiXactIdPrecedes(result, multiWarnLimit))
+ if (!MultiXactIdPrecedes(result, multiWarnLimit) ||
+ (!MultiXactIdPrecedes(result, multiLogLimit) &&
+ result % 1000000 == 0))
{
char *oldest_datname = get_database_name(oldest_datoid);
+ int elevel;
+
+ /*
+ * We only send the periodic warnings to the server log in an
+ * attempt to avoid confusion from clients (since the WARNING will
+ * disappear for 1M multis at a time). Once the warning limit is
+ * reached, we emit a proper WARNING every time.
+ */
+ if (!MultiXactIdPrecedes(result, multiWarnLimit))
+ elevel = WARNING;
+ else
+ elevel = LOG_SERVER_ONLY;
/* complain even if that DB has disappeared */
if (oldest_datname)
- ereport(WARNING,
+ ereport(elevel,
(errmsg_plural("database \"%s\" must be vacuumed before %u more MultiXactId is used",
"database \"%s\" must be vacuumed before %u more MultiXactIds are used",
multiWrapLimit - result,
@@ -1123,7 +1140,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
errhint("Execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
else
- ereport(WARNING,
+ ereport(elevel,
(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
"database with OID %u must be vacuumed before %u more MultiXactIds are used",
multiWrapLimit - result,
@@ -2299,6 +2316,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
bool is_startup)
{
MultiXactId multiVacLimit;
+ MultiXactId multiLogLimit;
MultiXactId multiWarnLimit;
MultiXactId multiStopLimit;
MultiXactId multiWrapLimit;
@@ -2340,6 +2358,15 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
if (multiWarnLimit < FirstMultiXactId)
multiWarnLimit -= FirstMultiXactId;
+ /*
+ * We'll start complaining every 1M multis when we get within 500M multis
+ * of data loss. The idea is to provide an early warning system that is
+ * less noisy than multiWarnLimit but provides ample time to react.
+ */
+ multiLogLimit = multiWrapLimit - 500000000;
+ if (multiLogLimit < FirstMultiXactId)
+ multiLogLimit -= FirstMultiXactId;
+
/*
* We'll start trying to force autovacuums when oldest_datminmxid gets to
* be more than autovacuum_multixact_freeze_max_age mxids old.
@@ -2357,6 +2384,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
MultiXactState->oldestMultiXactId = oldest_datminmxid;
MultiXactState->oldestMultiXactDB = oldest_datoid;
MultiXactState->multiVacLimit = multiVacLimit;
+ MultiXactState->multiLogLimit = multiLogLimit;
MultiXactState->multiWarnLimit = multiWarnLimit;
MultiXactState->multiStopLimit = multiStopLimit;
MultiXactState->multiWrapLimit = multiWrapLimit;
@@ -2394,7 +2422,11 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
needs_offset_vacuum) && IsUnderPostmaster)
SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_LAUNCHER);
- /* Give an immediate warning if past the wrap warn point */
+ /*
+ * Give an immediate warning if past the wrap warn point. We don't bother
+ * with multiLogLimit here, as it's unlikely to apply. We leave that part
+ * to GetNewMultiXactId() instead.
+ */
if (MultiXactIdPrecedes(multiWarnLimit, curMulti))
{
char *oldest_datname;
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 5585381bc8c..74ba958eb7a 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -112,6 +112,7 @@ GetNewTransactionId(bool isSubXact)
* catastrophic data loss due to XID wraparound. The basic rules are:
*
* If we're past xidVacLimit, start trying to force autovacuum cycles.
+ * If we're past xidLogLimit, start issuing logs periodically.
* If we're past xidWarnLimit, start issuing warnings.
* If we're past xidStopLimit, refuse to execute transactions, unless
* we are running in single-user mode (which gives an escape hatch
@@ -129,6 +130,7 @@ GetNewTransactionId(bool isSubXact)
* possibility of deadlock while doing get_database_name(). First,
* copy all the shared values we'll need in this path.
*/
+ TransactionId xidLogLimit = TransamVariables->xidLogLimit;
TransactionId xidWarnLimit = TransamVariables->xidWarnLimit;
TransactionId xidStopLimit = TransamVariables->xidStopLimit;
TransactionId xidWrapLimit = TransamVariables->xidWrapLimit;
@@ -165,13 +167,27 @@ GetNewTransactionId(bool isSubXact)
errhint("Execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
}
- else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
+ else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit) ||
+ (TransactionIdFollowsOrEquals(xid, xidLogLimit) &&
+ xid % 1000000 == 0))
{
char *oldest_datname = get_database_name(oldest_datoid);
+ int elevel;
+
+ /*
+ * We only send the periodic warnings to the server log in an
+ * attempt to avoid confusion from clients (since the WARNING will
+ * disappear for 1M transactions at a time). Once the warning
+ * limit is reached, we emit a proper WARNING every time.
+ */
+ if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
+ elevel = WARNING;
+ else
+ elevel = LOG_SERVER_ONLY;
/* complain even if that DB has disappeared */
if (oldest_datname)
- ereport(WARNING,
+ ereport(elevel,
(errmsg("database \"%s\" must be vacuumed within %u transactions",
oldest_datname,
xidWrapLimit - xid),
@@ -180,7 +196,7 @@ GetNewTransactionId(bool isSubXact)
errhint("To avoid transaction ID assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
else
- ereport(WARNING,
+ ereport(elevel,
(errmsg("database with OID %u must be vacuumed within %u transactions",
oldest_datoid,
xidWrapLimit - xid),
@@ -376,6 +392,7 @@ void
SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
{
TransactionId xidVacLimit;
+ TransactionId xidLogLimit;
TransactionId xidWarnLimit;
TransactionId xidStopLimit;
TransactionId xidWrapLimit;
@@ -424,6 +441,16 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
if (xidWarnLimit < FirstNormalTransactionId)
xidWarnLimit -= FirstNormalTransactionId;
+ /*
+ * We'll start complaining every 1M transactions when we get within 500M
+ * transactions of data loss. The idea is to provide an early warning
+ * system that is less noisy than xidWarnLimit but provides ample time to
+ * react.
+ */
+ xidLogLimit = xidWrapLimit - 500000000;
+ if (xidLogLimit < FirstNormalTransactionId)
+ xidLogLimit -= FirstNormalTransactionId;
+
/*
* We'll start trying to force autovacuums when oldest_datfrozenxid gets
* to be more than autovacuum_freeze_max_age transactions old.
@@ -447,6 +474,7 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
LWLockAcquire(XidGenLock, LW_EXCLUSIVE);
TransamVariables->oldestXid = oldest_datfrozenxid;
TransamVariables->xidVacLimit = xidVacLimit;
+ TransamVariables->xidLogLimit = xidLogLimit;
TransamVariables->xidWarnLimit = xidWarnLimit;
TransamVariables->xidStopLimit = xidStopLimit;
TransamVariables->xidWrapLimit = xidWrapLimit;
@@ -470,7 +498,11 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
IsUnderPostmaster && !InRecovery)
SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_LAUNCHER);
- /* Give an immediate warning if past the wrap warn point */
+ /*
+ * Give an immediate warning if past the wrap warn point. We don't bother
+ * with xidLogLimit here, as it's unlikely to apply. We leave that part
+ * to GetNewTransactionId() instead.
+ */
if (TransactionIdFollowsOrEquals(curXid, xidWarnLimit) && !InRecovery)
{
char *oldest_datname;
diff --git a/src/include/access/transam.h b/src/include/access/transam.h
index c9e20418275..a1bd4259f86 100644
--- a/src/include/access/transam.h
+++ b/src/include/access/transam.h
@@ -203,8 +203,8 @@ FullTransactionIdAdvance(FullTransactionId *dest)
* LWLocks.
*
* Note: xidWrapLimit and oldestXidDB are not "active" values, but are
- * used just to generate useful messages when xidWarnLimit or xidStopLimit
- * are exceeded.
+ * used just to generate useful messages when xidLogLimit, xidWarnLimit, or
+ * xidStopLimit are exceeded.
*/
typedef struct TransamVariablesData
{
@@ -221,6 +221,7 @@ typedef struct TransamVariablesData
TransactionId oldestXid; /* cluster-wide minimum datfrozenxid */
TransactionId xidVacLimit; /* start forcing autovacuums here */
+ TransactionId xidLogLimit; /* start logging periodically here */
TransactionId xidWarnLimit; /* start complaining here */
TransactionId xidStopLimit; /* refuse to advance nextXid beyond here */
TransactionId xidWrapLimit; /* where the world ends */
--
2.39.5 (Apple Git-154)
Re: enhance wraparound warnings
rebased
--
nathan
Attachments:
v2-0001-Add-percentage-of-transaction-IDs-that-are-availa.patchtext/plain; charset=us-asciiDownload
From 407a7e91f09b657f048f4af7cff52f1b2f6cc29b Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 14 Nov 2025 09:59:15 -0600
Subject: [PATCH v2 1/3] Add percentage of transaction IDs that are available
to wraparound warnings.
---
doc/src/sgml/maintenance.sgml | 1 +
src/backend/access/transam/multixact.c | 8 ++++++++
src/backend/access/transam/varsup.c | 8 ++++++++
3 files changed, 17 insertions(+)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 08e6489afb8..c8ba94303f1 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -674,6 +674,7 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
<programlisting>
WARNING: database "mydb" must be vacuumed within 39985967 transactions
+DETAIL: Approximately 1.86% of transactions IDs are available for use.
HINT: To avoid XID assignment failures, execute a database-wide VACUUM in that database.
</programlisting>
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 8ba2f4529dc..e1ac4bf4c0b 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -1040,6 +1040,8 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
multiWrapLimit - result,
oldest_datname,
multiWrapLimit - result),
+ errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
+ (double) (multiWrapLimit - result) / PG_INT32_MAX * 100),
errhint("Execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
else
@@ -1049,6 +1051,8 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
multiWrapLimit - result,
oldest_datoid,
multiWrapLimit - result),
+ errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
+ (double) (multiWrapLimit - result) / PG_INT32_MAX * 100),
errhint("Execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
}
@@ -2166,6 +2170,8 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
multiWrapLimit - curMulti,
oldest_datname,
multiWrapLimit - curMulti),
+ errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
+ (double) (multiWrapLimit - curMulti) / PG_INT32_MAX * 100),
errhint("To avoid MultiXactId assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
else
@@ -2175,6 +2181,8 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
multiWrapLimit - curMulti,
oldest_datoid,
multiWrapLimit - curMulti),
+ errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
+ (double) (multiWrapLimit - curMulti) / PG_INT32_MAX * 100),
errhint("To avoid MultiXactId assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
}
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index f8c4dada7c9..962396bae10 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -175,6 +175,8 @@ GetNewTransactionId(bool isSubXact)
(errmsg("database \"%s\" must be vacuumed within %u transactions",
oldest_datname,
xidWrapLimit - xid),
+ errdetail("Approximately %.2f%% of transaction IDs are available for use.",
+ (double) (xidWrapLimit - xid) / PG_INT32_MAX * 100),
errhint("To avoid transaction ID assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
else
@@ -182,6 +184,8 @@ GetNewTransactionId(bool isSubXact)
(errmsg("database with OID %u must be vacuumed within %u transactions",
oldest_datoid,
xidWrapLimit - xid),
+ errdetail("Approximately %.2f%% of transaction IDs are available for use.",
+ (double) (xidWrapLimit - xid) / PG_INT32_MAX * 100),
errhint("To avoid XID assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
}
@@ -490,6 +494,8 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
(errmsg("database \"%s\" must be vacuumed within %u transactions",
oldest_datname,
xidWrapLimit - curXid),
+ errdetail("Approximately %.2f%% of transaction IDs are available for use.",
+ (double) (xidWrapLimit - curXid) / PG_INT32_MAX * 100),
errhint("To avoid XID assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
else
@@ -497,6 +503,8 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
(errmsg("database with OID %u must be vacuumed within %u transactions",
oldest_datoid,
xidWrapLimit - curXid),
+ errdetail("Approximately %.2f%% of transaction IDs are available for use.",
+ (double) (xidWrapLimit - curXid) / PG_INT32_MAX * 100),
errhint("To avoid XID assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
}
--
2.39.5 (Apple Git-154)
v2-0002-Bump-transaction-ID-limit-to-warn-at-100M.patchtext/plain; charset=us-asciiDownload
From 0c87a24dba1730cf5a8e3d6d8bee8446863cff07 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 14 Nov 2025 10:28:52 -0600
Subject: [PATCH v2 2/3] Bump transaction ID limit to warn at 100M.
---
doc/src/sgml/maintenance.sgml | 4 ++--
src/backend/access/transam/multixact.c | 6 +++---
src/backend/access/transam/varsup.c | 6 +++---
3 files changed, 8 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index c8ba94303f1..c23e0a6e260 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -670,7 +670,7 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
<para>
If for some reason autovacuum fails to clear old XIDs from a table, the
system will begin to emit warning messages like this when the database's
- oldest XIDs reach forty million transactions from the wraparound point:
+ oldest XIDs reach one hundred million transactions from the wraparound point:
<programlisting>
WARNING: database "mydb" must be vacuumed within 39985967 transactions
@@ -824,7 +824,7 @@ HINT: Execute a database-wide VACUUM in that database.
<para>
Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
- system will begin to emit warning messages when the database's oldest MXIDs reach forty
+ system will begin to emit warning messages when the database's oldest MXIDs reach one hundred
million transactions from the wraparound point. And, just as in the XID case, if these
warnings are ignored, the system will refuse to generate new MXIDs once there are fewer
than three million left until wraparound.
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index e1ac4bf4c0b..42bce35c887 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2072,16 +2072,16 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
multiStopLimit -= FirstMultiXactId;
/*
- * We'll start complaining loudly when we get within 40M multis of data
+ * We'll start complaining loudly when we get within 100M multis of data
* loss. This is kind of arbitrary, but if you let your gas gauge get
- * down to 2% of full, would you be looking for the next gas station? We
+ * down to 5% of full, would you be looking for the next gas station? We
* need to be fairly liberal about this number because there are lots of
* scenarios where most transactions are done by automatic clients that
* won't pay attention to warnings. (No, we're not gonna make this
* configurable. If you know enough to configure it, you know enough to
* not get in this kind of trouble in the first place.)
*/
- multiWarnLimit = multiWrapLimit - 40000000;
+ multiWarnLimit = multiWrapLimit - 100000000;
if (multiWarnLimit < FirstMultiXactId)
multiWarnLimit -= FirstMultiXactId;
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 962396bae10..5585381bc8c 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -411,16 +411,16 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
xidStopLimit -= FirstNormalTransactionId;
/*
- * We'll start complaining loudly when we get within 40M transactions of
+ * We'll start complaining loudly when we get within 100M transactions of
* data loss. This is kind of arbitrary, but if you let your gas gauge
- * get down to 2% of full, would you be looking for the next gas station?
+ * get down to 5% of full, would you be looking for the next gas station?
* We need to be fairly liberal about this number because there are lots
* of scenarios where most transactions are done by automatic clients that
* won't pay attention to warnings. (No, we're not gonna make this
* configurable. If you know enough to configure it, you know enough to
* not get in this kind of trouble in the first place.)
*/
- xidWarnLimit = xidWrapLimit - 40000000;
+ xidWarnLimit = xidWrapLimit - 100000000;
if (xidWarnLimit < FirstNormalTransactionId)
xidWarnLimit -= FirstNormalTransactionId;
--
2.39.5 (Apple Git-154)
v2-0003-Perodically-emit-server-logs-when-fewer-than-500M.patchtext/plain; charset=us-asciiDownload
From 0a00eb8bd7d45e915aa2accb279e12f3b925bffa Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 14 Nov 2025 10:48:35 -0600
Subject: [PATCH v2 3/3] Perodically emit server logs when fewer than 500M
remaining transaction IDs.
---
src/backend/access/transam/multixact.c | 40 +++++++++++++++++++++++---
src/backend/access/transam/varsup.c | 40 +++++++++++++++++++++++---
src/include/access/transam.h | 5 ++--
3 files changed, 75 insertions(+), 10 deletions(-)
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 42bce35c887..1b53a4b222b 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -147,6 +147,7 @@ typedef struct MultiXactStateData
/* support for anti-wraparound measures */
MultiXactId multiVacLimit;
+ MultiXactId multiLogLimit;
MultiXactId multiWarnLimit;
MultiXactId multiStopLimit;
MultiXactId multiWrapLimit;
@@ -970,6 +971,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
* If we're past multiVacLimit or the safe threshold for member storage
* space, or we don't know what the safe threshold for member storage is,
* start trying to force autovacuum cycles.
+ * If we're past multiLogLimit, start issuing logs periodically.
* If we're past multiWarnLimit, start issuing warnings.
* If we're past multiStopLimit, refuse to create new MultiXactIds.
*
@@ -985,6 +987,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
* possibility of deadlock while doing get_database_name(). First,
* copy all the shared values we'll need in this path.
*/
+ MultiXactId multiLogLimit = MultiXactState->multiLogLimit;
MultiXactId multiWarnLimit = MultiXactState->multiWarnLimit;
MultiXactId multiStopLimit = MultiXactState->multiStopLimit;
MultiXactId multiWrapLimit = MultiXactState->multiWrapLimit;
@@ -1028,13 +1031,27 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
if (IsUnderPostmaster && (result % 65536) == 0)
SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_LAUNCHER);
- if (!MultiXactIdPrecedes(result, multiWarnLimit))
+ if (!MultiXactIdPrecedes(result, multiWarnLimit) ||
+ (!MultiXactIdPrecedes(result, multiLogLimit) &&
+ result % 1000000 == 0))
{
char *oldest_datname = get_database_name(oldest_datoid);
+ int elevel;
+
+ /*
+ * We only send the periodic warnings to the server log in an
+ * attempt to avoid confusion from clients (since the WARNING will
+ * disappear for 1M multis at a time). Once the warning limit is
+ * reached, we emit a proper WARNING every time.
+ */
+ if (!MultiXactIdPrecedes(result, multiWarnLimit))
+ elevel = WARNING;
+ else
+ elevel = LOG_SERVER_ONLY;
/* complain even if that DB has disappeared */
if (oldest_datname)
- ereport(WARNING,
+ ereport(elevel,
(errmsg_plural("database \"%s\" must be vacuumed before %u more MultiXactId is used",
"database \"%s\" must be vacuumed before %u more MultiXactIds are used",
multiWrapLimit - result,
@@ -1045,7 +1062,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
errhint("Execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
else
- ereport(WARNING,
+ ereport(elevel,
(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
"database with OID %u must be vacuumed before %u more MultiXactIds are used",
multiWrapLimit - result,
@@ -2047,6 +2064,7 @@ void
SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
{
MultiXactId multiVacLimit;
+ MultiXactId multiLogLimit;
MultiXactId multiWarnLimit;
MultiXactId multiStopLimit;
MultiXactId multiWrapLimit;
@@ -2085,6 +2103,15 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
if (multiWarnLimit < FirstMultiXactId)
multiWarnLimit -= FirstMultiXactId;
+ /*
+ * We'll start complaining every 1M multis when we get within 500M multis
+ * of data loss. The idea is to provide an early warning system that is
+ * less noisy than multiWarnLimit but provides ample time to react.
+ */
+ multiLogLimit = multiWrapLimit - 500000000;
+ if (multiLogLimit < FirstMultiXactId)
+ multiLogLimit -= FirstMultiXactId;
+
/*
* We'll start trying to force autovacuums when oldest_datminmxid gets to
* be more than autovacuum_multixact_freeze_max_age mxids old.
@@ -2102,6 +2129,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
MultiXactState->oldestMultiXactId = oldest_datminmxid;
MultiXactState->oldestMultiXactDB = oldest_datoid;
MultiXactState->multiVacLimit = multiVacLimit;
+ MultiXactState->multiLogLimit = multiLogLimit;
MultiXactState->multiWarnLimit = multiWarnLimit;
MultiXactState->multiStopLimit = multiStopLimit;
MultiXactState->multiWrapLimit = multiWrapLimit;
@@ -2144,7 +2172,11 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
if (MultiXactIdPrecedes(multiVacLimit, curMulti) && IsUnderPostmaster)
SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_LAUNCHER);
- /* Give an immediate warning if past the wrap warn point */
+ /*
+ * Give an immediate warning if past the wrap warn point. We don't bother
+ * with multiLogLimit here, as it's unlikely to apply. We leave that part
+ * to GetNewMultiXactId() instead.
+ */
if (MultiXactIdPrecedes(multiWarnLimit, curMulti))
{
char *oldest_datname;
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 5585381bc8c..74ba958eb7a 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -112,6 +112,7 @@ GetNewTransactionId(bool isSubXact)
* catastrophic data loss due to XID wraparound. The basic rules are:
*
* If we're past xidVacLimit, start trying to force autovacuum cycles.
+ * If we're past xidLogLimit, start issuing logs periodically.
* If we're past xidWarnLimit, start issuing warnings.
* If we're past xidStopLimit, refuse to execute transactions, unless
* we are running in single-user mode (which gives an escape hatch
@@ -129,6 +130,7 @@ GetNewTransactionId(bool isSubXact)
* possibility of deadlock while doing get_database_name(). First,
* copy all the shared values we'll need in this path.
*/
+ TransactionId xidLogLimit = TransamVariables->xidLogLimit;
TransactionId xidWarnLimit = TransamVariables->xidWarnLimit;
TransactionId xidStopLimit = TransamVariables->xidStopLimit;
TransactionId xidWrapLimit = TransamVariables->xidWrapLimit;
@@ -165,13 +167,27 @@ GetNewTransactionId(bool isSubXact)
errhint("Execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
}
- else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
+ else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit) ||
+ (TransactionIdFollowsOrEquals(xid, xidLogLimit) &&
+ xid % 1000000 == 0))
{
char *oldest_datname = get_database_name(oldest_datoid);
+ int elevel;
+
+ /*
+ * We only send the periodic warnings to the server log in an
+ * attempt to avoid confusion from clients (since the WARNING will
+ * disappear for 1M transactions at a time). Once the warning
+ * limit is reached, we emit a proper WARNING every time.
+ */
+ if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
+ elevel = WARNING;
+ else
+ elevel = LOG_SERVER_ONLY;
/* complain even if that DB has disappeared */
if (oldest_datname)
- ereport(WARNING,
+ ereport(elevel,
(errmsg("database \"%s\" must be vacuumed within %u transactions",
oldest_datname,
xidWrapLimit - xid),
@@ -180,7 +196,7 @@ GetNewTransactionId(bool isSubXact)
errhint("To avoid transaction ID assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
else
- ereport(WARNING,
+ ereport(elevel,
(errmsg("database with OID %u must be vacuumed within %u transactions",
oldest_datoid,
xidWrapLimit - xid),
@@ -376,6 +392,7 @@ void
SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
{
TransactionId xidVacLimit;
+ TransactionId xidLogLimit;
TransactionId xidWarnLimit;
TransactionId xidStopLimit;
TransactionId xidWrapLimit;
@@ -424,6 +441,16 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
if (xidWarnLimit < FirstNormalTransactionId)
xidWarnLimit -= FirstNormalTransactionId;
+ /*
+ * We'll start complaining every 1M transactions when we get within 500M
+ * transactions of data loss. The idea is to provide an early warning
+ * system that is less noisy than xidWarnLimit but provides ample time to
+ * react.
+ */
+ xidLogLimit = xidWrapLimit - 500000000;
+ if (xidLogLimit < FirstNormalTransactionId)
+ xidLogLimit -= FirstNormalTransactionId;
+
/*
* We'll start trying to force autovacuums when oldest_datfrozenxid gets
* to be more than autovacuum_freeze_max_age transactions old.
@@ -447,6 +474,7 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
LWLockAcquire(XidGenLock, LW_EXCLUSIVE);
TransamVariables->oldestXid = oldest_datfrozenxid;
TransamVariables->xidVacLimit = xidVacLimit;
+ TransamVariables->xidLogLimit = xidLogLimit;
TransamVariables->xidWarnLimit = xidWarnLimit;
TransamVariables->xidStopLimit = xidStopLimit;
TransamVariables->xidWrapLimit = xidWrapLimit;
@@ -470,7 +498,11 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
IsUnderPostmaster && !InRecovery)
SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_LAUNCHER);
- /* Give an immediate warning if past the wrap warn point */
+ /*
+ * Give an immediate warning if past the wrap warn point. We don't bother
+ * with xidLogLimit here, as it's unlikely to apply. We leave that part
+ * to GetNewTransactionId() instead.
+ */
if (TransactionIdFollowsOrEquals(curXid, xidWarnLimit) && !InRecovery)
{
char *oldest_datname;
diff --git a/src/include/access/transam.h b/src/include/access/transam.h
index c9e20418275..a1bd4259f86 100644
--- a/src/include/access/transam.h
+++ b/src/include/access/transam.h
@@ -203,8 +203,8 @@ FullTransactionIdAdvance(FullTransactionId *dest)
* LWLocks.
*
* Note: xidWrapLimit and oldestXidDB are not "active" values, but are
- * used just to generate useful messages when xidWarnLimit or xidStopLimit
- * are exceeded.
+ * used just to generate useful messages when xidLogLimit, xidWarnLimit, or
+ * xidStopLimit are exceeded.
*/
typedef struct TransamVariablesData
{
@@ -221,6 +221,7 @@ typedef struct TransamVariablesData
TransactionId oldestXid; /* cluster-wide minimum datfrozenxid */
TransactionId xidVacLimit; /* start forcing autovacuums here */
+ TransactionId xidLogLimit; /* start logging periodically here */
TransactionId xidWarnLimit; /* start complaining here */
TransactionId xidStopLimit; /* refuse to advance nextXid beyond here */
TransactionId xidWrapLimit; /* where the world ends */
--
2.39.5 (Apple Git-154)
Re: enhance wraparound warnings
Hi Nathan,
I just reviewed the patch. My comments are mainly in 0001, and a few nits on 0003. For 0002, the code change is quite straightforward, I am not sure the value bumping to has been discussed.
On Dec 12, 2025, at 04:28, Nathan Bossart <nathandbossart@gmail.com> wrote:
rebased
--
nathan
<v2-0001-Add-percentage-of-transaction-IDs-that-are-availa.patch><v2-0002-Bump-transaction-ID-limit-to-warn-at-100M.patch><v2-0003-Perodically-emit-server-logs-when-fewer-than-500M.patch>
1 - 0001
```
+ (double) (multiWrapLimit - result) / PG_INT32_MAX * 100),
```
I don’t feel good with using PG_INT32_MAX as denominator, though the value is correct.
Looking at the code of how xidWrapLimit is calculated:
```
/*
* The place where we actually get into deep trouble is halfway around
* from the oldest potentially-existing XID. (This calculation is
* probably off by one or two counts, 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.)
*/
xidWrapLimit = oldest_datfrozenxid + (MaxTransactionId >> 1);
if (xidWrapLimit < FirstNormalTransactionId)
xidWrapLimit += FirstNormalTransactionId;
```
Where "(MaxTransactionId >> 1)” has the same value as PG_INT32_MAX. But if one day xid is changed to 64 bits, that code doesn’t need to updated, while these patched code will need to be updated.
So, can we define a const in transom.h like:
```
#define MaxTransactionId ((TransactionId) 0xFFFFFFFF)
#define WrapAroundWindow (MaxTransactionId>>1)
```
And use WrapAroundWindow in all places.
2 - 0001
```
+ errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
```
“%.2f%%” shows only 2 digits after dot. xidWrapLimit is roughly 2B, when remaining goes down to 107374, it will shows “0.00%”. IMO, when remaining is a large number, percentage makes more sense, while an exact number is clearer when the number is relatively small. So, can we show both percentage and exact number? Or shows the exact number when percentage is 0.00%?
3 - 0001
```
<programlisting>
WARNING: database "mydb" must be vacuumed within 39985967 transactions
+DETAIL: Approximately 1.86% of transactions IDs are available for use.
```
Typo: " transactions IDs” => " transaction IDs"
4 - 0003
```
Subject: [PATCH v2 3/3] Perodically emit server logs when fewer than 500M
```
Typo: Perodically => Periodically
5 - 0003
```
+ xidLogLimit = xidWrapLimit - 500000000;
```
Instead of hardcode 500M, do we want to consider autovacuum_freeze_max_age? If a deployment sets autovacuum_freeze_max_age > 500M, then vacuum would be triggered first, then this log can get kinda non-intuitive. But if a vacuum cannot freeze anything tuple, then this log will still make sense. I am not sure. Maybe not a real problem.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Re: enhance wraparound warnings
On Fri, Dec 12, 2025 at 10:59:53AM +0800, Chao Li wrote:
I just reviewed the patch. My comments are mainly in 0001, and a few nits
on 0003. For 0002, the code change is quite straightforward, I am not
sure the value bumping to has been discussed.
Thanks!
Where "(MaxTransactionId >> 1)” has the same value as PG_INT32_MAX. But
if one day xid is changed to 64 bits, that code doesn’t need to updated,
while these patched code will need to be updated.So, can we define a const in transom.h like:
```
#define MaxTransactionId ((TransactionId) 0xFFFFFFFF)
#define WrapAroundWindow (MaxTransactionId>>1)
```And use WrapAroundWindow in all places.
I think I'd rather just open-code the (MaxTransactionId / 2) here. I'm not
too concerned about 64-bit transaction IDs (there's a lot more than this to
change for that), but it does seem like a good idea to be consistent with
nearby code.
```
+ errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
```“%.2f%%” shows only 2 digits after dot. xidWrapLimit is roughly 2B, when
remaining goes down to 107374, it will shows “0.00%”. IMO, when remaining
is a large number, percentage makes more sense, while an exact number is
clearer when the number is relatively small. So, can we show both
percentage and exact number? Or shows the exact number when percentage is
0.00%?
The errmsg part should already show the exact number of IDs remaining.
```
+ xidLogLimit = xidWrapLimit - 500000000;
```Instead of hardcode 500M, do we want to consider
autovacuum_freeze_max_age? If a deployment sets autovacuum_freeze_max_age500M, then vacuum would be triggered first, then this log can get kinda
non-intuitive. But if a vacuum cannot freeze anything tuple, then this
log will still make sense. I am not sure. Maybe not a real problem.
IMHO we should still emit warnings about imminent wraparound even if
autovacuum_freeze_max_age is set to totally-inadvisable values. I think
the behavior you are describing only happens if users set it to north of
1.6B.
--
nathan
Attachments:
v3-0001-Add-percentage-of-transaction-IDs-that-are-availa.patchtext/plain; charset=us-asciiDownload
From c1cb82c3fe80978c668ecc7c57654f62610e0e07 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 14 Nov 2025 09:59:15 -0600
Subject: [PATCH v3 1/3] Add percentage of transaction IDs that are available
to wraparound warnings.
---
doc/src/sgml/maintenance.sgml | 1 +
src/backend/access/transam/multixact.c | 8 ++++++++
src/backend/access/transam/varsup.c | 8 ++++++++
3 files changed, 17 insertions(+)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 08e6489afb8..257c6a5435d 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -674,6 +674,7 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
<programlisting>
WARNING: database "mydb" must be vacuumed within 39985967 transactions
+DETAIL: Approximately 1.86% of transaction IDs are available for use.
HINT: To avoid XID assignment failures, execute a database-wide VACUUM in that database.
</programlisting>
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index f4fab7edfee..39e5b691573 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -1017,6 +1017,8 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
multiWrapLimit - result,
oldest_datname,
multiWrapLimit - result),
+ errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
+ (double) (multiWrapLimit - result) / (MaxMultiXactId / 2) * 100),
errhint("Execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
else
@@ -1026,6 +1028,8 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
multiWrapLimit - result,
oldest_datoid,
multiWrapLimit - result),
+ errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
+ (double) (multiWrapLimit - result) / (MaxMultiXactId / 2) * 100),
errhint("Execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
}
@@ -2134,6 +2138,8 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
multiWrapLimit - curMulti,
oldest_datname,
multiWrapLimit - curMulti),
+ errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
+ (double) (multiWrapLimit - curMulti) / (MaxMultiXactId / 2) * 100),
errhint("To avoid MultiXactId assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
else
@@ -2143,6 +2149,8 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
multiWrapLimit - curMulti,
oldest_datoid,
multiWrapLimit - curMulti),
+ errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
+ (double) (multiWrapLimit - curMulti) / (MaxMultiXactId / 2) * 100),
errhint("To avoid MultiXactId assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
}
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index f8c4dada7c9..32961b9acab 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -175,6 +175,8 @@ GetNewTransactionId(bool isSubXact)
(errmsg("database \"%s\" must be vacuumed within %u transactions",
oldest_datname,
xidWrapLimit - xid),
+ errdetail("Approximately %.2f%% of transaction IDs are available for use.",
+ (double) (xidWrapLimit - xid) / (MaxTransactionId / 2) * 100),
errhint("To avoid transaction ID assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
else
@@ -182,6 +184,8 @@ GetNewTransactionId(bool isSubXact)
(errmsg("database with OID %u must be vacuumed within %u transactions",
oldest_datoid,
xidWrapLimit - xid),
+ errdetail("Approximately %.2f%% of transaction IDs are available for use.",
+ (double) (xidWrapLimit - xid) / (MaxTransactionId / 2) * 100),
errhint("To avoid XID assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
}
@@ -490,6 +494,8 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
(errmsg("database \"%s\" must be vacuumed within %u transactions",
oldest_datname,
xidWrapLimit - curXid),
+ errdetail("Approximately %.2f%% of transaction IDs are available for use.",
+ (double) (xidWrapLimit - curXid) / (MaxTransactionId / 2) * 100),
errhint("To avoid XID assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
else
@@ -497,6 +503,8 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
(errmsg("database with OID %u must be vacuumed within %u transactions",
oldest_datoid,
xidWrapLimit - curXid),
+ errdetail("Approximately %.2f%% of transaction IDs are available for use.",
+ (double) (xidWrapLimit - curXid) / (MaxTransactionId / 2) * 100),
errhint("To avoid XID assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
}
--
2.39.5 (Apple Git-154)
v3-0002-Bump-transaction-ID-limit-to-warn-at-100M.patchtext/plain; charset=us-asciiDownload
From 1bbbb093e409323b4e63feac79a0e8a805ab6c37 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 12 Dec 2025 13:10:05 -0600
Subject: [PATCH v3 2/3] Bump transaction ID limit to warn at 100M.
---
doc/src/sgml/maintenance.sgml | 4 ++--
src/backend/access/transam/multixact.c | 6 +++---
src/backend/access/transam/varsup.c | 6 +++---
3 files changed, 8 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 257c6a5435d..3960a23ad71 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -670,7 +670,7 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
<para>
If for some reason autovacuum fails to clear old XIDs from a table, the
system will begin to emit warning messages like this when the database's
- oldest XIDs reach forty million transactions from the wraparound point:
+ oldest XIDs reach one hundred million transactions from the wraparound point:
<programlisting>
WARNING: database "mydb" must be vacuumed within 39985967 transactions
@@ -824,7 +824,7 @@ HINT: Execute a database-wide VACUUM in that database.
<para>
Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
- system will begin to emit warning messages when the database's oldest MXIDs reach forty
+ system will begin to emit warning messages when the database's oldest MXIDs reach one hundred
million transactions from the wraparound point. And, just as in the XID case, if these
warnings are ignored, the system will refuse to generate new MXIDs once there are fewer
than three million left until wraparound.
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 39e5b691573..27a0baab8c7 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2040,16 +2040,16 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
multiStopLimit -= FirstMultiXactId;
/*
- * We'll start complaining loudly when we get within 40M multis of data
+ * We'll start complaining loudly when we get within 100M multis of data
* loss. This is kind of arbitrary, but if you let your gas gauge get
- * down to 2% of full, would you be looking for the next gas station? We
+ * down to 5% of full, would you be looking for the next gas station? We
* need to be fairly liberal about this number because there are lots of
* scenarios where most transactions are done by automatic clients that
* won't pay attention to warnings. (No, we're not gonna make this
* configurable. If you know enough to configure it, you know enough to
* not get in this kind of trouble in the first place.)
*/
- multiWarnLimit = multiWrapLimit - 40000000;
+ multiWarnLimit = multiWrapLimit - 100000000;
if (multiWarnLimit < FirstMultiXactId)
multiWarnLimit -= FirstMultiXactId;
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 32961b9acab..98aeea96e8a 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -411,16 +411,16 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
xidStopLimit -= FirstNormalTransactionId;
/*
- * We'll start complaining loudly when we get within 40M transactions of
+ * We'll start complaining loudly when we get within 100M transactions of
* data loss. This is kind of arbitrary, but if you let your gas gauge
- * get down to 2% of full, would you be looking for the next gas station?
+ * get down to 5% of full, would you be looking for the next gas station?
* We need to be fairly liberal about this number because there are lots
* of scenarios where most transactions are done by automatic clients that
* won't pay attention to warnings. (No, we're not gonna make this
* configurable. If you know enough to configure it, you know enough to
* not get in this kind of trouble in the first place.)
*/
- xidWarnLimit = xidWrapLimit - 40000000;
+ xidWarnLimit = xidWrapLimit - 100000000;
if (xidWarnLimit < FirstNormalTransactionId)
xidWarnLimit -= FirstNormalTransactionId;
--
2.39.5 (Apple Git-154)
v3-0003-Periodically-emit-server-logs-when-fewer-than-500.patchtext/plain; charset=us-asciiDownload
From 2ca1da92e081f767f756f93b3c091cdf6d084a50 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 12 Dec 2025 13:13:55 -0600
Subject: [PATCH v3 3/3] Periodically emit server logs when fewer than 500M
remaining transaction IDs.
---
src/backend/access/transam/multixact.c | 40 +++++++++++++++++++++++---
src/backend/access/transam/varsup.c | 40 +++++++++++++++++++++++---
src/include/access/transam.h | 5 ++--
3 files changed, 75 insertions(+), 10 deletions(-)
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 27a0baab8c7..abd89c3a73d 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -153,6 +153,7 @@ typedef struct MultiXactStateData
/* support for anti-wraparound measures */
MultiXactId multiVacLimit;
+ MultiXactId multiLogLimit;
MultiXactId multiWarnLimit;
MultiXactId multiStopLimit;
MultiXactId multiWrapLimit;
@@ -947,6 +948,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
* If we're past multiVacLimit or the safe threshold for member storage
* space, or we don't know what the safe threshold for member storage is,
* start trying to force autovacuum cycles.
+ * If we're past multiLogLimit, start issuing logs periodically.
* If we're past multiWarnLimit, start issuing warnings.
* If we're past multiStopLimit, refuse to create new MultiXactIds.
*
@@ -962,6 +964,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
* possibility of deadlock while doing get_database_name(). First,
* copy all the shared values we'll need in this path.
*/
+ MultiXactId multiLogLimit = MultiXactState->multiLogLimit;
MultiXactId multiWarnLimit = MultiXactState->multiWarnLimit;
MultiXactId multiStopLimit = MultiXactState->multiStopLimit;
MultiXactId multiWrapLimit = MultiXactState->multiWrapLimit;
@@ -1005,13 +1008,27 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
if (IsUnderPostmaster && ((result % 65536) == 0 || result == FirstMultiXactId))
SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_LAUNCHER);
- if (!MultiXactIdPrecedes(result, multiWarnLimit))
+ if (!MultiXactIdPrecedes(result, multiWarnLimit) ||
+ (!MultiXactIdPrecedes(result, multiLogLimit) &&
+ result % 1000000 == 0))
{
char *oldest_datname = get_database_name(oldest_datoid);
+ int elevel;
+
+ /*
+ * We only send the periodic warnings to the server log in an
+ * attempt to avoid confusion from clients (since the WARNING will
+ * disappear for 1M multis at a time). Once the warning limit is
+ * reached, we emit a proper WARNING every time.
+ */
+ if (!MultiXactIdPrecedes(result, multiWarnLimit))
+ elevel = WARNING;
+ else
+ elevel = LOG_SERVER_ONLY;
/* complain even if that DB has disappeared */
if (oldest_datname)
- ereport(WARNING,
+ ereport(elevel,
(errmsg_plural("database \"%s\" must be vacuumed before %u more MultiXactId is used",
"database \"%s\" must be vacuumed before %u more MultiXactIds are used",
multiWrapLimit - result,
@@ -1022,7 +1039,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
errhint("Execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
else
- ereport(WARNING,
+ ereport(elevel,
(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
"database with OID %u must be vacuumed before %u more MultiXactIds are used",
multiWrapLimit - result,
@@ -2015,6 +2032,7 @@ void
SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
{
MultiXactId multiVacLimit;
+ MultiXactId multiLogLimit;
MultiXactId multiWarnLimit;
MultiXactId multiStopLimit;
MultiXactId multiWrapLimit;
@@ -2053,6 +2071,15 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
if (multiWarnLimit < FirstMultiXactId)
multiWarnLimit -= FirstMultiXactId;
+ /*
+ * We'll start complaining every 1M multis when we get within 500M multis
+ * of data loss. The idea is to provide an early warning system that is
+ * less noisy than multiWarnLimit but provides ample time to react.
+ */
+ multiLogLimit = multiWrapLimit - 500000000;
+ if (multiLogLimit < FirstMultiXactId)
+ multiLogLimit -= FirstMultiXactId;
+
/*
* We'll start trying to force autovacuums when oldest_datminmxid gets to
* be more than autovacuum_multixact_freeze_max_age mxids old.
@@ -2070,6 +2097,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
MultiXactState->oldestMultiXactId = oldest_datminmxid;
MultiXactState->oldestMultiXactDB = oldest_datoid;
MultiXactState->multiVacLimit = multiVacLimit;
+ MultiXactState->multiLogLimit = multiLogLimit;
MultiXactState->multiWarnLimit = multiWarnLimit;
MultiXactState->multiStopLimit = multiStopLimit;
MultiXactState->multiWrapLimit = multiWrapLimit;
@@ -2112,7 +2140,11 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
if (MultiXactIdPrecedes(multiVacLimit, curMulti) && IsUnderPostmaster)
SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_LAUNCHER);
- /* Give an immediate warning if past the wrap warn point */
+ /*
+ * Give an immediate warning if past the wrap warn point. We don't bother
+ * with multiLogLimit here, as it's unlikely to apply. We leave that part
+ * to GetNewMultiXactId() instead.
+ */
if (MultiXactIdPrecedes(multiWarnLimit, curMulti))
{
char *oldest_datname;
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 98aeea96e8a..0f633cc0e14 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -112,6 +112,7 @@ GetNewTransactionId(bool isSubXact)
* catastrophic data loss due to XID wraparound. The basic rules are:
*
* If we're past xidVacLimit, start trying to force autovacuum cycles.
+ * If we're past xidLogLimit, start issuing logs periodically.
* If we're past xidWarnLimit, start issuing warnings.
* If we're past xidStopLimit, refuse to execute transactions, unless
* we are running in single-user mode (which gives an escape hatch
@@ -129,6 +130,7 @@ GetNewTransactionId(bool isSubXact)
* possibility of deadlock while doing get_database_name(). First,
* copy all the shared values we'll need in this path.
*/
+ TransactionId xidLogLimit = TransamVariables->xidLogLimit;
TransactionId xidWarnLimit = TransamVariables->xidWarnLimit;
TransactionId xidStopLimit = TransamVariables->xidStopLimit;
TransactionId xidWrapLimit = TransamVariables->xidWrapLimit;
@@ -165,13 +167,27 @@ GetNewTransactionId(bool isSubXact)
errhint("Execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
}
- else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
+ else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit) ||
+ (TransactionIdFollowsOrEquals(xid, xidLogLimit) &&
+ xid % 1000000 == 0))
{
char *oldest_datname = get_database_name(oldest_datoid);
+ int elevel;
+
+ /*
+ * We only send the periodic warnings to the server log in an
+ * attempt to avoid confusion from clients (since the WARNING will
+ * disappear for 1M transactions at a time). Once the warning
+ * limit is reached, we emit a proper WARNING every time.
+ */
+ if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
+ elevel = WARNING;
+ else
+ elevel = LOG_SERVER_ONLY;
/* complain even if that DB has disappeared */
if (oldest_datname)
- ereport(WARNING,
+ ereport(elevel,
(errmsg("database \"%s\" must be vacuumed within %u transactions",
oldest_datname,
xidWrapLimit - xid),
@@ -180,7 +196,7 @@ GetNewTransactionId(bool isSubXact)
errhint("To avoid transaction ID assignment failures, execute a database-wide VACUUM in that database.\n"
"You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
else
- ereport(WARNING,
+ ereport(elevel,
(errmsg("database with OID %u must be vacuumed within %u transactions",
oldest_datoid,
xidWrapLimit - xid),
@@ -376,6 +392,7 @@ void
SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
{
TransactionId xidVacLimit;
+ TransactionId xidLogLimit;
TransactionId xidWarnLimit;
TransactionId xidStopLimit;
TransactionId xidWrapLimit;
@@ -424,6 +441,16 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
if (xidWarnLimit < FirstNormalTransactionId)
xidWarnLimit -= FirstNormalTransactionId;
+ /*
+ * We'll start complaining every 1M transactions when we get within 500M
+ * transactions of data loss. The idea is to provide an early warning
+ * system that is less noisy than xidWarnLimit but provides ample time to
+ * react.
+ */
+ xidLogLimit = xidWrapLimit - 500000000;
+ if (xidLogLimit < FirstNormalTransactionId)
+ xidLogLimit -= FirstNormalTransactionId;
+
/*
* We'll start trying to force autovacuums when oldest_datfrozenxid gets
* to be more than autovacuum_freeze_max_age transactions old.
@@ -447,6 +474,7 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
LWLockAcquire(XidGenLock, LW_EXCLUSIVE);
TransamVariables->oldestXid = oldest_datfrozenxid;
TransamVariables->xidVacLimit = xidVacLimit;
+ TransamVariables->xidLogLimit = xidLogLimit;
TransamVariables->xidWarnLimit = xidWarnLimit;
TransamVariables->xidStopLimit = xidStopLimit;
TransamVariables->xidWrapLimit = xidWrapLimit;
@@ -470,7 +498,11 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
IsUnderPostmaster && !InRecovery)
SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_LAUNCHER);
- /* Give an immediate warning if past the wrap warn point */
+ /*
+ * Give an immediate warning if past the wrap warn point. We don't bother
+ * with xidLogLimit here, as it's unlikely to apply. We leave that part
+ * to GetNewTransactionId() instead.
+ */
if (TransactionIdFollowsOrEquals(curXid, xidWarnLimit) && !InRecovery)
{
char *oldest_datname;
diff --git a/src/include/access/transam.h b/src/include/access/transam.h
index c9e20418275..a1bd4259f86 100644
--- a/src/include/access/transam.h
+++ b/src/include/access/transam.h
@@ -203,8 +203,8 @@ FullTransactionIdAdvance(FullTransactionId *dest)
* LWLocks.
*
* Note: xidWrapLimit and oldestXidDB are not "active" values, but are
- * used just to generate useful messages when xidWarnLimit or xidStopLimit
- * are exceeded.
+ * used just to generate useful messages when xidLogLimit, xidWarnLimit, or
+ * xidStopLimit are exceeded.
*/
typedef struct TransamVariablesData
{
@@ -221,6 +221,7 @@ typedef struct TransamVariablesData
TransactionId oldestXid; /* cluster-wide minimum datfrozenxid */
TransactionId xidVacLimit; /* start forcing autovacuums here */
+ TransactionId xidLogLimit; /* start logging periodically here */
TransactionId xidWarnLimit; /* start complaining here */
TransactionId xidStopLimit; /* refuse to advance nextXid beyond here */
TransactionId xidWrapLimit; /* where the world ends */
--
2.39.5 (Apple Git-154)