[Patch] Fix pg_get_multixact_stats() over-reporting members on a hot standby

On 21/09/2026 07:43, Naga Appani wrote:

I did not add the start-of-recovery initialization from the control file
Heikki mentioned [0]. With only the redo-path update there is still a short
window before the first truncation record is replayed where oldestOffset
reads 0. Handling that at startup needs the value without the
find_multixact_start() lookup, which asserts finishedStartup, so I left it
out for now.

Hmm, the window could be very long, there's no guarantee that you'll see
a MULTIXACT_TRUNCATE_ID record any time soon. And after restart, it'll
revert back to 0 again. If we can't get a reasonable value at startup, I
wonder if we should just always show NULL in the standby.

- Heikki

On Mon, Sep 21, 2026 at 10:19:47AM +0300, Heikki Linnakangas wrote:

Hmm, the window could be very long, there's no guarantee that you'll see a
MULTIXACT_TRUNCATE_ID record any time soon. And after restart, it'll revert
back to 0 again. If we can't get a reasonable value at startup, I wonder if
we should just always show NULL in the standby.

I'd like to think that we should just throw an ERROR in this case with
a ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE, similarly to a lot of the
WAL functions. NULL is useful when for example using such a function
with a JOIN on catalogs, when called for a lot of tuples, but that's
not the case here.
--
Michael

On 21/09/2026 10:30, Michael Paquier wrote:

On Mon, Sep 21, 2026 at 10:19:47AM +0300, Heikki Linnakangas wrote:

Hmm, the window could be very long, there's no guarantee that you'll see a
MULTIXACT_TRUNCATE_ID record any time soon. And after restart, it'll revert
back to 0 again. If we can't get a reasonable value at startup, I wonder if
we should just always show NULL in the standby.

I'd like to think that we should just throw an ERROR in this case with
a ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE, similarly to a lot of the
WAL functions. NULL is useful when for example using such a function
with a JOIN on catalogs, when called for a lot of tuples, but that's
not the case here.

That would disable pg_get_multixact_stats() in a standby altogether, but
only the members-related fields (num_members, members_size) have this
problem. So that would throw some babies out with the bathwater. It's a
new function so we could still do it though, if that's what we want.

Another idea is to add the oldestOffset field to the control file. I
actually find it a little weird that we don't have it there already. But
I'm uncomfortable making that change so late in the release cycle, just
for this function, in particular because it would require a catversion bump.

- Heikki

On Mon, Sep 21, 2026 at 10:21 AM Heikki Linnakangas <hlinnaka@iki.fi> wrote:

On 21/09/2026 10:30, Michael Paquier wrote:

On Mon, Sep 21, 2026 at 10:19:47AM +0300, Heikki Linnakangas wrote:

Hmm, the window could be very long, there's no guarantee that you'll see a
MULTIXACT_TRUNCATE_ID record any time soon. And after restart, it'll revert
back to 0 again. If we can't get a reasonable value at startup, I wonder if
we should just always show NULL in the standby.

I'd like to think that we should just throw an ERROR in this case with
a ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE, similarly to a lot of the
WAL functions. NULL is useful when for example using such a function
with a JOIN on catalogs, when called for a lot of tuples, but that's
not the case here.

That would disable pg_get_multixact_stats() in a standby altogether, but
only the members-related fields (num_members, members_size) have this
problem. So that would throw some babies out with the bathwater. It's a
new function so we could still do it though, if that's what we want.

Another idea is to add the oldestOffset field to the control file. I
actually find it a little weird that we don't have it there already. But
I'm uncomfortable making that change so late in the release cycle, just
for this function, in particular because it would require a catversion bump.

- Heikki

Thanks both for looking into this!

I lean toward ERROR over NULL, but not strongly.

AFAICS none of this is really actionable on a standby either way, since
freezing and vacuum are only actionable on the primary. An error seems
more honest than returning numbers that look real but don't drive any
decision. Could be missing a use case though, please let me know what you
think.

Thanks,
Naga

On Mon, Sep 21, 2026 at 10:55:02AM -0500, Naga Appani wrote:

AFAICS none of this is really actionable on a standby either way, since
freezing and vacuum are only actionable on the primary. An error seems
more honest than returning numbers that look real but don't drive any
decision. Could be missing a use case though, please let me know what you
think.

Yeah, I'd just choose an error at this stage rather than trying to
provide partially-true numbers. If somebody is interested in
providing these numbers on a standby, that could always be done later
on.
--
Michael

On Mon, Sep 21, 2026 at 10:55:02AM -0500, Naga Appani wrote:

I lean toward ERROR over NULL, but not strongly.

I'll go switch that to an error if called while RecoveryInProgress(),
then. Bonus points regarding the fact that we can reuse some of the
existing error messages from xlogfuncs.c, meaning no extra translation
required.
--
Michael

On Wed, Sep 23, 2026 at 7:06 PM Michael Paquier <michael@paquier.xyz> wrote:

On Mon, Sep 21, 2026 at 10:55:02AM -0500, Naga Appani wrote:

I lean toward ERROR over NULL, but not strongly.

I'll go switch that to an error if called while RecoveryInProgress(),
then. Bonus points regarding the fact that we can reuse some of the
existing error messages from xlogfuncs.c, meaning no extra translation
required.
--
Michael

Thanks Michael, that matches what I had in v2 as well (same
errmsg/errhint pattern from xlogfuncs.c).

Thanks,
Naga