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

On Fri, Apr 4, 2025 at 10:30 PM PG Bug reporting form
<noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 18876
Logged by: TAKATSUKA Haruka
Email address: harukat@sraoss.co.jp
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

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 <masao.fujii@gmail.com> wrote:

On Thu, Jun 11, 2026 at 1:29 PM TAKATSUKA Haruka <harukat@sraoss.co.jp> 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

On Wed, Jul 1, 2026 at 11:30 PM Fujii Masao <masao.fujii@gmail.com> wrote:

On Thu, Jun 11, 2026 at 1:29 PM TAKATSUKA Haruka <harukat@sraoss.co.jp> 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

On Thu, Jul 2, 2026 at 10:26 PM Fujii Masao <masao.fujii@gmail.com> 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

On Tue, Jul 7, 2026 at 2:23 AM Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Jul 2, 2026 at 10:26 PM Fujii Masao <masao.fujii@gmail.com> 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