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

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