public inbox for [email protected]help / color / mirror / Atom feed
BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate 7+ messages / 4 participants [nested] [flat]
* BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate @ 2025-04-04 03:05 PG Bug reporting form <[email protected]> 2026-06-11 03:31 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate Fujii Masao <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: PG Bug reporting form @ 2025-04-04 03:05 UTC (permalink / raw) To: [email protected]; +Cc: [email protected] The following bug has been logged on the website: Bug reference: 18876 Logged by: TAKATSUKA Haruka Email address: [email protected] PostgreSQL version: 17.4 Operating system: any Description: In src/backend/access/transam/multixact.c, there are the following hint messages: "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." "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." I think that their "drop stale replication slots" is not appropriate. Because NewRelminMxid is determined by GetOldestMultiXactId() and its comment says: * Return the oldest MultiXactId that's still possibly still seen as live by * any running transaction. Older ones might still exist on disk, but they no * longer have any running member transaction. Thus, the presence of an old mxid in a tuple pending removal by a slot is not considered to affect it. In addition, as far as I have tested, leaving the old inactive replication slot does not cause mxid_age(relminmxid) not to decrease after VACUUM. Thanks, ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate 2025-04-04 03:05 BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate PG Bug reporting form <[email protected]> @ 2026-06-11 03:31 ` Fujii Masao <[email protected]> 2026-06-11 04:29 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate TAKATSUKA Haruka <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Fujii Masao @ 2026-06-11 03:31 UTC (permalink / raw) To: [email protected]; [email protected] On Fri, Apr 4, 2025 at 10:30 PM PG Bug reporting form <[email protected]> wrote: > > The following bug has been logged on the website: > > Bug reference: 18876 > Logged by: TAKATSUKA Haruka > Email address: [email protected] > PostgreSQL version: 17.4 > Operating system: any > Description: > > In src/backend/access/transam/multixact.c, there are the following hint > messages: > > "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." > > "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." > > I think that their "drop stale replication slots" is not appropriate. > > Because NewRelminMxid is determined by GetOldestMultiXactId() and its > comment says: > * Return the oldest MultiXactId that's still possibly still seen as live > by > * any running transaction. Older ones might still exist on disk, but > they no > * longer have any running member transaction. > > Thus, the presence of an old mxid in a tuple pending removal by a slot is > not considered to affect it. > > In addition, as far as I have tested, leaving the old inactive replication > slot does not cause mxid_age(relminmxid) not to decrease after VACUUM. I think you're right. So I prepared and attached a patch that refines the MXID wraparound hint messages. The patch also updates the documentation to clarify the relationship between replication slots and MXID wraparound. Thought? Regards, -- Fujii Masao From 84fe025cc5592d7e01ba77b4b2c5eea8d8d5f35d Mon Sep 17 00:00:00 2001 From: "masao.fujii" <[email protected]’s-MacBook-Pro> Date: Thu, 11 Jun 2026 11:18:00 +0900 Subject: [PATCH v1] Refine MultiXact wraparound hints Replication slots can hold back XID horizons, but they do not prevent MultiXact cleanup. Therefore, this commit removes replication slot advice from MultiXact wraparound hint messages and update the maintenance documentation to clarify the distinction. Backpatch to all supported versions. --- doc/src/sgml/maintenance.sgml | 6 ++++++ src/backend/access/transam/multixact.c | 12 ++++++------ src/backend/commands/vacuum.c | 2 +- 3 files changed, 13 insertions(+), 7 deletions(-) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index d6d44d74069..20552317891 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -820,6 +820,12 @@ HINT: Stop the postmaster and vacuum that database in single-user mode. <simpara>Running transactions and prepared transactions can be ignored if there is no chance that they might appear in a multixact.</simpara> </listitem> + <listitem> + <simpara>Unlike transaction ID wraparound, replication slots do not + directly hold back multixact cleanup. Dropping stale replication + slots is therefore not usually relevant to resolving multixact ID + wraparound problems.</simpara> + </listitem> <listitem> <simpara>MXID information is not directly visible in system views such as <literal>pg_stat_activity</literal>; however, looking for old XIDs is still a good diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c index 8408492879b..0778a7c9b80 100644 --- a/src/backend/access/transam/multixact.c +++ b/src/backend/access/transam/multixact.c @@ -1159,14 +1159,14 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset) errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database \"%s\"", oldest_datname), 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."))); + "You might also need to commit or roll back old prepared transactions."))); else ereport(ERROR, (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED), errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database with OID %u", oldest_datoid), 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."))); + "You might also need to commit or roll back old prepared transactions."))); } /* @@ -1190,7 +1190,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset) oldest_datname, multiWrapLimit - result), 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."))); + "You might also need to commit or roll back old prepared transactions."))); else ereport(WARNING, (errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used", @@ -1199,7 +1199,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset) oldest_datoid, multiWrapLimit - result), 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."))); + "You might also need to commit or roll back old prepared transactions."))); } /* Re-acquire lock and start over */ @@ -2475,7 +2475,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid, oldest_datname, multiWrapLimit - curMulti), errhint("To avoid a database shutdown, 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."))); + "You might also need to commit or roll back old prepared transactions."))); else ereport(WARNING, (errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used", @@ -2484,7 +2484,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid, oldest_datoid, multiWrapLimit - curMulti), errhint("To avoid a database shutdown, 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."))); + "You might also need to commit or roll back old prepared transactions."))); } } diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 6c3208eeb84..3185062bf16 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -1176,7 +1176,7 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params, ereport(WARNING, (errmsg("cutoff for freezing multixacts is far in the past"), errhint("Close open transactions soon to avoid wraparound problems.\n" - "You might also need to commit or roll back old prepared transactions, or drop stale replication slots."))); + "You might also need to commit or roll back old prepared transactions."))); /* * Determine the minimum freeze age to use: as specified by the caller, or -- 2.53.0 From 855ad33c97e48d68f6e188e5a11036c9e47883ad Mon Sep 17 00:00:00 2001 From: "masao.fujii" <[email protected]’s-MacBook-Pro> Date: Thu, 11 Jun 2026 11:18:00 +0900 Subject: [PATCH v1] Refine MultiXact wraparound hints Replication slots can hold back XID horizons, but they do not prevent MultiXact cleanup. Therefore, this commit removes replication slot advice from MultiXact wraparound hint messages and update the maintenance documentation to clarify the distinction. Backpatch to all supported versions. --- doc/src/sgml/maintenance.sgml | 6 ++++++ src/backend/access/transam/multixact.c | 12 ++++++------ 2 files changed, 12 insertions(+), 6 deletions(-) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index aa2df371efb..ddca8ea4166 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -819,6 +819,12 @@ HINT: Stop the postmaster and vacuum that database in single-user mode. <simpara>Running transactions and prepared transactions can be ignored if there is no chance that they might appear in a multixact.</simpara> </listitem> + <listitem> + <simpara>Unlike transaction ID wraparound, replication slots do not + directly hold back multixact cleanup. Dropping stale replication + slots is therefore not usually relevant to resolving multixact ID + wraparound problems.</simpara> + </listitem> <listitem> <simpara>MXID information is not directly visible in system views such as <literal>pg_stat_activity</literal>; however, looking for old XIDs is still a good diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c index 8b5220d8aab..dfa470d2228 100644 --- a/src/backend/access/transam/multixact.c +++ b/src/backend/access/transam/multixact.c @@ -1158,14 +1158,14 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset) errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database \"%s\"", oldest_datname), 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."))); + "You might also need to commit or roll back old prepared transactions."))); else ereport(ERROR, (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED), errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database with OID %u", oldest_datoid), 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."))); + "You might also need to commit or roll back old prepared transactions."))); } /* @@ -1189,7 +1189,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset) oldest_datname, multiWrapLimit - result), 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."))); + "You might also need to commit or roll back old prepared transactions."))); else ereport(WARNING, (errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used", @@ -1198,7 +1198,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset) oldest_datoid, multiWrapLimit - result), 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."))); + "You might also need to commit or roll back old prepared transactions."))); } /* Re-acquire lock and start over */ @@ -2476,7 +2476,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid, oldest_datname, multiWrapLimit - curMulti), errhint("To avoid a database shutdown, 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."))); + "You might also need to commit or roll back old prepared transactions."))); else ereport(WARNING, (errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used", @@ -2485,7 +2485,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid, oldest_datoid, multiWrapLimit - curMulti), errhint("To avoid a database shutdown, 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."))); + "You might also need to commit or roll back old prepared transactions."))); } } -- 2.53.0 Attachments: [text/plain] v1-0001-PG16-Refine-MultiXact-wraparound-hints.txt (5.6K, ../../CAHGQGwFKngUKWOM=N3sAOGCL95TjQf2jM8E3put7=cmGKHHg0A@mail.gmail.com/2-v1-0001-PG16-Refine-MultiXact-wraparound-hints.txt) download | inline diff: From 84fe025cc5592d7e01ba77b4b2c5eea8d8d5f35d Mon Sep 17 00:00:00 2001 From: "masao.fujii" <[email protected]’s-MacBook-Pro> Date: Thu, 11 Jun 2026 11:18:00 +0900 Subject: [PATCH v1] Refine MultiXact wraparound hints Replication slots can hold back XID horizons, but they do not prevent MultiXact cleanup. Therefore, this commit removes replication slot advice from MultiXact wraparound hint messages and update the maintenance documentation to clarify the distinction. Backpatch to all supported versions. --- doc/src/sgml/maintenance.sgml | 6 ++++++ src/backend/access/transam/multixact.c | 12 ++++++------ src/backend/commands/vacuum.c | 2 +- 3 files changed, 13 insertions(+), 7 deletions(-) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index d6d44d74069..20552317891 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -820,6 +820,12 @@ HINT: Stop the postmaster and vacuum that database in single-user mode. <simpara>Running transactions and prepared transactions can be ignored if there is no chance that they might appear in a multixact.</simpara> </listitem> + <listitem> + <simpara>Unlike transaction ID wraparound, replication slots do not + directly hold back multixact cleanup. Dropping stale replication + slots is therefore not usually relevant to resolving multixact ID + wraparound problems.</simpara> + </listitem> <listitem> <simpara>MXID information is not directly visible in system views such as <literal>pg_stat_activity</literal>; however, looking for old XIDs is still a good diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c index 8408492879b..0778a7c9b80 100644 --- a/src/backend/access/transam/multixact.c +++ b/src/backend/access/transam/multixact.c @@ -1159,14 +1159,14 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset) errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database \"%s\"", oldest_datname), 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."))); + "You might also need to commit or roll back old prepared transactions."))); else ereport(ERROR, (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED), errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database with OID %u", oldest_datoid), 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."))); + "You might also need to commit or roll back old prepared transactions."))); } /* @@ -1190,7 +1190,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset) oldest_datname, multiWrapLimit - result), 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."))); + "You might also need to commit or roll back old prepared transactions."))); else ereport(WARNING, (errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used", @@ -1199,7 +1199,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset) oldest_datoid, multiWrapLimit - result), 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."))); + "You might also need to commit or roll back old prepared transactions."))); } /* Re-acquire lock and start over */ @@ -2475,7 +2475,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid, oldest_datname, multiWrapLimit - curMulti), errhint("To avoid a database shutdown, 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."))); + "You might also need to commit or roll back old prepared transactions."))); else ereport(WARNING, (errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used", @@ -2484,7 +2484,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid, oldest_datoid, multiWrapLimit - curMulti), errhint("To avoid a database shutdown, 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."))); + "You might also need to commit or roll back old prepared transactions."))); } } diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 6c3208eeb84..3185062bf16 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -1176,7 +1176,7 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params, ereport(WARNING, (errmsg("cutoff for freezing multixacts is far in the past"), errhint("Close open transactions soon to avoid wraparound problems.\n" - "You might also need to commit or roll back old prepared transactions, or drop stale replication slots."))); + "You might also need to commit or roll back old prepared transactions."))); /* * Determine the minimum freeze age to use: as specified by the caller, or -- 2.53.0 [application/octet-stream] v1-0001-Refine-MultiXact-wraparound-hints.patch (6.0K, ../../CAHGQGwFKngUKWOM=N3sAOGCL95TjQf2jM8E3put7=cmGKHHg0A@mail.gmail.com/3-v1-0001-Refine-MultiXact-wraparound-hints.patch) download | inline diff: From b6529d80b6aa7514c5c43a2e9f69caf35e666af4 Mon Sep 17 00:00:00 2001 From: "masao.fujii" <[email protected]’s-MacBook-Pro> Date: Thu, 11 Jun 2026 11:18:00 +0900 Subject: [PATCH v1] Refine MultiXact wraparound hints Replication slots can hold back XID horizons, but they do not prevent MultiXact cleanup. Therefore, this commit removes replication slot advice from MultiXact wraparound hint messages and update the maintenance documentation to clarify the distinction. Backpatch to all supported versions. --- doc/src/sgml/maintenance.sgml | 6 ++++++ src/backend/access/transam/multixact.c | 12 ++++++------ src/backend/commands/vacuum.c | 2 +- 3 files changed, 13 insertions(+), 7 deletions(-) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index 4a21bdb5de7..ec4204f88d9 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -869,6 +869,12 @@ HINT: Execute a database-wide VACUUM in that database. <simpara>Running transactions and prepared transactions can be ignored if there is no chance that they might appear in a multixact.</simpara> </listitem> + <listitem> + <simpara>Unlike transaction ID wraparound, replication slots do not + directly hold back multixact cleanup. Dropping stale replication + slots is therefore not usually relevant to resolving multixact ID + wraparound problems.</simpara> + </listitem> <listitem> <simpara>MXID information is not directly visible in system views such as <literal>pg_stat_activity</literal>; however, looking for old XIDs is still a good diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c index 10cbc0d76bd..8c694658132 100644 --- a/src/backend/access/transam/multixact.c +++ b/src/backend/access/transam/multixact.c @@ -1040,14 +1040,14 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset) errmsg("database is not accepting commands that assign new MultiXactIds to avoid wraparound data loss in database \"%s\"", oldest_datname), 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."))); + "You might also need to commit or roll back old prepared transactions."))); else ereport(ERROR, (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED), errmsg("database is not accepting commands that assign new MultiXactIds to avoid wraparound data loss in database with OID %u", oldest_datoid), 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."))); + "You might also need to commit or roll back old prepared transactions."))); } /* @@ -1073,7 +1073,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset) 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."))); + "You might also need to commit or roll back old prepared transactions."))); else ereport(WARNING, (errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used", @@ -1084,7 +1084,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset) 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."))); + "You might also need to commit or roll back old prepared transactions."))); } /* Re-acquire lock and start over */ @@ -2211,7 +2211,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid) 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."))); + "You might also need to commit or roll back old prepared transactions."))); else ereport(WARNING, (errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used", @@ -2222,7 +2222,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid) 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."))); + "You might also need to commit or roll back old prepared transactions."))); } } diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index a4abb29cf64..38539a6fd3d 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -1177,7 +1177,7 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params, ereport(WARNING, (errmsg("cutoff for freezing multixacts is far in the past"), errhint("Close open transactions soon to avoid wraparound problems.\n" - "You might also need to commit or roll back old prepared transactions, or drop stale replication slots."))); + "You might also need to commit or roll back old prepared transactions."))); /* * Determine the minimum freeze age to use: as specified by the caller, or -- 2.53.0 [text/plain] v1-0001-PG15_PG14-Refine-MultiXact-wraparound-hints.txt (4.9K, ../../CAHGQGwFKngUKWOM=N3sAOGCL95TjQf2jM8E3put7=cmGKHHg0A@mail.gmail.com/4-v1-0001-PG15_PG14-Refine-MultiXact-wraparound-hints.txt) download | inline diff: From 855ad33c97e48d68f6e188e5a11036c9e47883ad Mon Sep 17 00:00:00 2001 From: "masao.fujii" <[email protected]’s-MacBook-Pro> Date: Thu, 11 Jun 2026 11:18:00 +0900 Subject: [PATCH v1] Refine MultiXact wraparound hints Replication slots can hold back XID horizons, but they do not prevent MultiXact cleanup. Therefore, this commit removes replication slot advice from MultiXact wraparound hint messages and update the maintenance documentation to clarify the distinction. Backpatch to all supported versions. --- doc/src/sgml/maintenance.sgml | 6 ++++++ src/backend/access/transam/multixact.c | 12 ++++++------ 2 files changed, 12 insertions(+), 6 deletions(-) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index aa2df371efb..ddca8ea4166 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -819,6 +819,12 @@ HINT: Stop the postmaster and vacuum that database in single-user mode. <simpara>Running transactions and prepared transactions can be ignored if there is no chance that they might appear in a multixact.</simpara> </listitem> + <listitem> + <simpara>Unlike transaction ID wraparound, replication slots do not + directly hold back multixact cleanup. Dropping stale replication + slots is therefore not usually relevant to resolving multixact ID + wraparound problems.</simpara> + </listitem> <listitem> <simpara>MXID information is not directly visible in system views such as <literal>pg_stat_activity</literal>; however, looking for old XIDs is still a good diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c index 8b5220d8aab..dfa470d2228 100644 --- a/src/backend/access/transam/multixact.c +++ b/src/backend/access/transam/multixact.c @@ -1158,14 +1158,14 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset) errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database \"%s\"", oldest_datname), 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."))); + "You might also need to commit or roll back old prepared transactions."))); else ereport(ERROR, (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED), errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database with OID %u", oldest_datoid), 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."))); + "You might also need to commit or roll back old prepared transactions."))); } /* @@ -1189,7 +1189,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset) oldest_datname, multiWrapLimit - result), 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."))); + "You might also need to commit or roll back old prepared transactions."))); else ereport(WARNING, (errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used", @@ -1198,7 +1198,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset) oldest_datoid, multiWrapLimit - result), 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."))); + "You might also need to commit or roll back old prepared transactions."))); } /* Re-acquire lock and start over */ @@ -2476,7 +2476,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid, oldest_datname, multiWrapLimit - curMulti), errhint("To avoid a database shutdown, 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."))); + "You might also need to commit or roll back old prepared transactions."))); else ereport(WARNING, (errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used", @@ -2485,7 +2485,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid, oldest_datoid, multiWrapLimit - curMulti), errhint("To avoid a database shutdown, 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."))); + "You might also need to commit or roll back old prepared transactions."))); } } -- 2.53.0 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate 2025-04-04 03:05 BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate PG Bug reporting form <[email protected]> 2026-06-11 03:31 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate Fujii Masao <[email protected]> @ 2026-06-11 04:29 ` TAKATSUKA Haruka <[email protected]> 2026-07-01 14:30 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate Fujii Masao <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: TAKATSUKA Haruka @ 2026-06-11 04:29 UTC (permalink / raw) To: Fujii Masao <[email protected]>; +Cc: [email protected] Fujii-san Thank you for incorporating my feedback into the code and documentation. with best regards, Haruka Takatsuka / SRA OSS K.K. On Thu, 11 Jun 2026 12:31:28 +0900 Fujii Masao <[email protected]> wrote: > On Fri, Apr 4, 2025 at 10:30 PM PG Bug reporting form > <[email protected]> wrote: > > > > The following bug has been logged on the website: > > > > Bug reference: 18876 > > Logged by: TAKATSUKA Haruka > > Email address: [email protected] > > PostgreSQL version: 17.4 > > Operating system: any > > Description: > > > > In src/backend/access/transam/multixact.c, there are the following hint > > messages: > > > > "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." > > > > "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." > > > > I think that their "drop stale replication slots" is not appropriate. > > > > Because NewRelminMxid is determined by GetOldestMultiXactId() and its > > comment says: > > * Return the oldest MultiXactId that's still possibly still seen as live > > by > > * any running transaction. Older ones might still exist on disk, but > > they no > > * longer have any running member transaction. > > > > Thus, the presence of an old mxid in a tuple pending removal by a slot is > > not considered to affect it. > > > > In addition, as far as I have tested, leaving the old inactive replication > > slot does not cause mxid_age(relminmxid) not to decrease after VACUUM. > > I think you're right. So I prepared and attached a patch that refines > the MXID wraparound hint messages. The patch also updates the > documentation to clarify the relationship between replication slots and > MXID wraparound. > > Thought? > > Regards, > > -- > Fujii Masao ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate 2025-04-04 03:05 BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate PG Bug reporting form <[email protected]> 2026-06-11 03:31 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate Fujii Masao <[email protected]> 2026-06-11 04:29 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate TAKATSUKA Haruka <[email protected]> @ 2026-07-01 14:30 ` Fujii Masao <[email protected]> 2026-07-03 02:25 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate Fujii Masao <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Fujii Masao @ 2026-07-01 14:30 UTC (permalink / raw) To: TAKATSUKA Haruka <[email protected]>; +Cc: [email protected] On Thu, Jun 11, 2026 at 1:29 PM TAKATSUKA Haruka <[email protected]> wrote: > > Fujii-san > > Thank you for incorporating my feedback into the code and documentation. Barring any objections, I will commit this patch. Regards, -- Fujii Masao ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate 2025-04-04 03:05 BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate PG Bug reporting form <[email protected]> 2026-06-11 03:31 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate Fujii Masao <[email protected]> 2026-06-11 04:29 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate TAKATSUKA Haruka <[email protected]> 2026-07-01 14:30 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate Fujii Masao <[email protected]> @ 2026-07-03 02:25 ` Fujii Masao <[email protected]> 2026-07-06 17:22 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate Robert Haas <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Fujii Masao @ 2026-07-03 02:25 UTC (permalink / raw) To: TAKATSUKA Haruka <[email protected]>; +Cc: [email protected] On Wed, Jul 1, 2026 at 11:30 PM Fujii Masao <[email protected]> wrote: > > On Thu, Jun 11, 2026 at 1:29 PM TAKATSUKA Haruka <[email protected]> wrote: > > > > Fujii-san > > > > Thank you for incorporating my feedback into the code and documentation. > > Barring any objections, I will commit this patch. I've pushed the patch. Thanks! Regards, -- Fujii Masao ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate 2025-04-04 03:05 BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate PG Bug reporting form <[email protected]> 2026-06-11 03:31 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate Fujii Masao <[email protected]> 2026-06-11 04:29 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate TAKATSUKA Haruka <[email protected]> 2026-07-01 14:30 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate Fujii Masao <[email protected]> 2026-07-03 02:25 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate Fujii Masao <[email protected]> @ 2026-07-06 17:22 ` Robert Haas <[email protected]> 2026-07-06 23:22 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate Fujii Masao <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Robert Haas @ 2026-07-06 17:22 UTC (permalink / raw) To: Fujii Masao <[email protected]>; +Cc: TAKATSUKA Haruka <[email protected]>; [email protected] On Thu, Jul 2, 2026 at 10:26 PM Fujii Masao <[email protected]> wrote: > I've pushed the patch. Thanks! Hi, I'm a bit concerned about this patch, specifically this part: + <simpara>Unlike transaction ID wraparound, replication slots do not + directly hold back multixact cleanup. Dropping stale replication + slots is therefore not usually relevant to resolving multixact ID + wraparound problems.</simpara> The problem with the word "usually" here is that it leaves room for replication slots to be relevant in some corner case that isn't described, while asserting without explanation that they most often won't matter. I feel like that leaves users and DBAs in a very awkward position. The way it seems to me, either holding back the XID threshold can indirectly hold back the MXID threshold, or it can't. If it can, then the documentation before this patch was correct, and this shouldn't have been committed, and if it can't, then we don't need the word "usually" here. Now maybe that's a bit too simplistic. I'd be OK with a statement involving "usually" if it went on to explain convincingly why it doesn't normally happen and why it sometimes can under extraordinary circumstances. But without that, I feel this is making things less clear rather than more clear. -- Robert Haas EDB: http://www.enterprisedb.com ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate 2025-04-04 03:05 BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate PG Bug reporting form <[email protected]> 2026-06-11 03:31 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate Fujii Masao <[email protected]> 2026-06-11 04:29 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate TAKATSUKA Haruka <[email protected]> 2026-07-01 14:30 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate Fujii Masao <[email protected]> 2026-07-03 02:25 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate Fujii Masao <[email protected]> 2026-07-06 17:22 ` Re: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate Robert Haas <[email protected]> @ 2026-07-06 23:22 ` Fujii Masao <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Fujii Masao @ 2026-07-06 23:22 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: TAKATSUKA Haruka <[email protected]>; [email protected] On Tue, Jul 7, 2026 at 2:23 AM Robert Haas <[email protected]> wrote: > > On Thu, Jul 2, 2026 at 10:26 PM Fujii Masao <[email protected]> wrote: > > I've pushed the patch. Thanks! > > Hi, > > I'm a bit concerned about this patch, specifically this part: > > + <simpara>Unlike transaction ID wraparound, replication slots do not > + directly hold back multixact cleanup. Dropping stale replication > + slots is therefore not usually relevant to resolving multixact ID > + wraparound problems.</simpara> > > The problem with the word "usually" here is that it leaves room for > replication slots to be relevant in some corner case that isn't > described, while asserting without explanation that they most often > won't matter. I feel like that leaves users and DBAs in a very awkward > position. The way it seems to me, either holding back the XID > threshold can indirectly hold back the MXID threshold, or it can't. If > it can, then the documentation before this patch was correct, and this > shouldn't have been committed, and if it can't, then we don't need the > word "usually" here. > > Now maybe that's a bit too simplistic. I'd be OK with a statement > involving "usually" if it went on to explain convincingly why it > doesn't normally happen and why it sometimes can under extraordinary > circumstances. But without that, I feel this is making things less > clear rather than more clear. As far as I understand, there is no case where replication slots hold back MXID cleanup. But I added "usually" only as a cautious wording choice. If that my understanding is correct, I would like to update the docs to remove "usually". Also, since "directly" in the same sentence might cause similar confusion, it may be better to remove that as well. So I am thinking of changing the text to something like this: Unlike transaction ID wraparound, replication slots do not hold back multixact cleanup. Dropping stale replication slots is therefore not relevant to resolving multixact ID wraparound problems. Thoughts? -- Fujii Masao ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-07-06 23:22 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-04-04 03:05 BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate PG Bug reporting form <[email protected]> 2026-06-11 03:31 ` Fujii Masao <[email protected]> 2026-06-11 04:29 ` TAKATSUKA Haruka <[email protected]> 2026-07-01 14:30 ` Fujii Masao <[email protected]> 2026-07-03 02:25 ` Fujii Masao <[email protected]> 2026-07-06 17:22 ` Robert Haas <[email protected]> 2026-07-06 23:22 ` Fujii Masao <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox