Subject: BUG #18876: HINT messages for mxid wrap-around say "drop stale slots", but that may not be appropriate

From: PG Bug reporting form <[email-masked]>
Date: Fri, 04 Apr 2025 03:05:12 +0000

The following bug has been logged on the website:

Bug reference:      18876
Logged by:          TAKATSUKA Haruka
Email address:      [email-masked]
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,

---

From: Fujii Masao <[email-masked]>
Date: Thu, 11 Jun 2026 12:31:28 +0900

> 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-masked]’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-masked]’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

---

From: TAKATSUKA Haruka <[email-masked]>
Date: Thu, 11 Jun 2026 13:29:10 +0900

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

---

From: Fujii Masao <[email-masked]>
Date: Wed, 1 Jul 2026 23:30:27 +0900

> Thank you for incorporating my feedback into the code and documentation.

Barring any objections, I will commit this patch.

Regards,

-- 
Fujii Masao

---

From: Fujii Masao <[email-masked]>
Date: Fri, 3 Jul 2026 11:25:54 +0900

> Barring any objections, I will commit this patch.

I've pushed the patch. Thanks!

Regards,

-- 
Fujii Masao

---

From: Robert Haas <[email-masked]>
Date: Mon, 6 Jul 2026 13:22:48 -0400

> I've pushed the patch. Thanks!

Hi,

I'm a bit concerned about this patch, specifically this part:

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

---

From: Fujii Masao <[email-masked]>
Date: Tue, 7 Jul 2026 08:22:50 +0900

> 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