pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feed[Patch] Fix pg_get_multixact_stats() over-reporting members on a hot standby
8+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2026-09-24 4545cee303 Forbid pg_get_multixact_stats() during recovery
* [Patch] Fix pg_get_multixact_stats() over-reporting members on a hot standby
@ 2026-09-21 04:43 Naga Appani <nagnrik@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Naga Appani @ 2026-09-21 04:43 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org; +Cc: Heikki Linnakangas <hlinnaka@iki.fi>; Noah Misch <noah@leadboat.com>; Michael Paquier <michael@paquier.xyz>
Hi,
Splitting this out into its own thread for CommitFest tracking. It was
first discussed in [0], where Heikki pointed out that
pg_get_multixact_stats() reports wrong member counts on a hot standby.
MultiXactState->oldestOffset is not maintained during recovery, so on a hot
standby it stays at 0 for the life of the server. pg_get_multixact_stats()
then computes num_members as nextOffset instead of nextOffset -
oldestOffset, and over-reports members and members_size. num_mxids and
oldest_multixact are correct.
I reproduced it on REL_19_BETA3 by building up multixacts on a primary,
setting up a standby, then advancing the primary's oldestOffset (VACUUM
FREEZE across all databases and a CHECKPOINT) and comparing
pg_get_multixact_stats() on both:
=== PRIMARY ===
num_mxids | num_members | members_size | oldest_multixact
-----------+-------------+--------------+------------------
0 | 0 | 0 | 409396
=== STANDBY (in recovery) ===
pg_is_in_recovery | num_mxids | num_members | members_size |
oldest_multixact
-------------------+-----------+-------------+--------------+------------------
t | 0 | 17563829 | 87819145 |
409396
The standby shows about 17.5M members / ~84 MB while the primary correctly
shows zero for the same data. Promoting the standby fixes it right away,
since the correct oldestOffset is only computed at end of recovery.
The attached patch updates MultiXactState->oldestOffset while replaying
XLOG_MULTIXACT_TRUNCATE_ID, as Heikki suggested, so the value stays current
during recovery. TrimMultiXact() still computes a fresh value at end of
recovery. With the patch, the same workload gives matching results on the
standby:
=== PRIMARY ===
num_mxids | num_members | members_size | oldest_multixact
-----------+-------------+--------------+------------------
0 | 0 | 0 | 397660
=== STANDBY (in recovery) ===
pg_is_in_recovery | num_mxids | num_members | members_size |
oldest_multixact
-------------------+-----------+-------------+--------------+------------------
t | 0 | 0 | 0 |
397660
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.
I would love to help see this through. I may not be able to stay very
hands-on over the next while though, so if it needs follow-up and I am slow
to respond, please do not wait on me. I am happy for someone to build on it
or take a different approach, and glad to review and help where I can.
Patch attached.
[0] https://www.postgresql.org/message-id/05d064e8-219d-40bd-9fdf-652b648ba317%40iki.fi
Best regards,
Naga Appani
Attachments:
v1-0001-Maintain-MultiXact-oldestOffset-during-recovery.patch (application/octet-stream, 1.8K) download
From fb0fa652780dda22f0f763ff7e176eff1d6943f4 Mon Sep 17 00:00:00 2001
From: Naga Appani <nagnrik@gmail.com>
Date: Sat, 19 Sep 2026 01:45:42 +0000
Subject: [PATCH v1] Maintain MultiXact oldestOffset during recovery
MultiXactState->oldestOffset was only updated outside recovery, so on a
hot standby it stayed at 0 for the life of the server. As a result
pg_get_multixact_stats() computed num_members as nextOffset - 0 and
over-reported the retained member count (and members_size), contradicting
the same query on the primary for identical data.
Update oldestOffset from the WAL record while replaying
XLOG_MULTIXACT_TRUNCATE_ID, so the value is kept current during recovery.
TrimMultiXact() still recomputes a fresh value at end of recovery.
Suggested-by: Heikki Linnakangas <heikki.linnakangas@iki.fi>
Discussion: https://www.postgresql.org/message-id/05d064e8-219d-40bd-9fdf-652b648ba317%40iki.fi
---
src/backend/access/transam/multixact.c | 9 +++++++++
1 file changed, 9 insertions(+)
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 424516d..0019228 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2989,6 +2989,15 @@ multixact_redo(XLogReaderState *record)
PerformMembersTruncation(xlrec.oldestOffset);
PerformOffsetsTruncation(xlrec.oldestMulti);
+ /*
+ * Keep oldestOffset current during recovery too. It is not
+ * otherwise maintained until end of recovery, which would make
+ * pg_get_multixact_stats() over-report members on a hot standby.
+ */
+ LWLockAcquire(MultiXactGenLock, LW_EXCLUSIVE);
+ MultiXactState->oldestOffset = xlrec.oldestOffset;
+ LWLockRelease(MultiXactGenLock);
+
LWLockRelease(MultiXactTruncationLock);
}
else
--
2.50.1
* Re: [Patch] Fix pg_get_multixact_stats() over-reporting members on a hot standby
@ 2026-09-21 07:19 Heikki Linnakangas <hlinnaka@iki.fi>
parent: Naga Appani <nagnrik@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Heikki Linnakangas @ 2026-09-21 07:19 UTC (permalink / raw)
To: Naga Appani <nagnrik@gmail.com>; pgsql-hackers@lists.postgresql.org; +Cc: Noah Misch <noah@leadboat.com>; Michael Paquier <michael@paquier.xyz>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [Patch] Fix pg_get_multixact_stats() over-reporting members on a hot standby
@ 2026-09-21 07:30 Michael Paquier <michael@paquier.xyz>
parent: Heikki Linnakangas <hlinnaka@iki.fi>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Paquier @ 2026-09-21 07:30 UTC (permalink / raw)
To: Heikki Linnakangas <hlinnaka@iki.fi>; +Cc: Naga Appani <nagnrik@gmail.com>; pgsql-hackers@lists.postgresql.org, Noah Misch <noah@leadboat.com>
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
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [Patch] Fix pg_get_multixact_stats() over-reporting members on a hot standby
@ 2026-09-21 15:21 Heikki Linnakangas <hlinnaka@iki.fi>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 8+ messages in thread
From: Heikki Linnakangas @ 2026-09-21 15:21 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Naga Appani <nagnrik@gmail.com>; pgsql-hackers@lists.postgresql.org, Noah Misch <noah@leadboat.com>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [Patch] Fix pg_get_multixact_stats() over-reporting members on a hot standby
@ 2026-09-21 15:55 Naga Appani <nagnrik@gmail.com>
parent: Heikki Linnakangas <hlinnaka@iki.fi>
0 siblings, 2 replies; 8+ messages in thread
From: Naga Appani @ 2026-09-21 15:55 UTC (permalink / raw)
To: Heikki Linnakangas <hlinnaka@iki.fi>; +Cc: Michael Paquier <michael@paquier.xyz>; pgsql-hackers@lists.postgresql.org, Noah Misch <noah@leadboat.com>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [Patch] Fix pg_get_multixact_stats() over-reporting members on a hot standby
@ 2026-09-21 22:56 Michael Paquier <michael@paquier.xyz>
parent: Naga Appani <nagnrik@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Michael Paquier @ 2026-09-21 22:56 UTC (permalink / raw)
To: Naga Appani <nagnrik@gmail.com>; +Cc: Heikki Linnakangas <hlinnaka@iki.fi>; pgsql-hackers@lists.postgresql.org, Noah Misch <noah@leadboat.com>
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
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [Patch] Fix pg_get_multixact_stats() over-reporting members on a hot standby
@ 2026-09-24 00:06 Michael Paquier <michael@paquier.xyz>
parent: Naga Appani <nagnrik@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Michael Paquier @ 2026-09-24 00:06 UTC (permalink / raw)
To: Naga Appani <nagnrik@gmail.com>; +Cc: Heikki Linnakangas <hlinnaka@iki.fi>; pgsql-hackers@lists.postgresql.org, Noah Misch <noah@leadboat.com>
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
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [Patch] Fix pg_get_multixact_stats() over-reporting members on a hot standby
@ 2026-09-24 02:40 Naga Appani <nagnrik@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 0 replies; 8+ messages in thread
From: Naga Appani @ 2026-09-24 02:40 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Heikki Linnakangas <hlinnaka@iki.fi>; pgsql-hackers@lists.postgresql.org, Noah Misch <noah@leadboat.com>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2026-09-24 02:40 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-21 04:43 [Patch] Fix pg_get_multixact_stats() over-reporting members on a hot standby Naga Appani <nagnrik@gmail.com>
2026-09-21 07:19 ` Heikki Linnakangas <hlinnaka@iki.fi>
2026-09-21 07:30 ` Michael Paquier <michael@paquier.xyz>
2026-09-21 15:21 ` Heikki Linnakangas <hlinnaka@iki.fi>
2026-09-21 15:55 ` Naga Appani <nagnrik@gmail.com>
2026-09-21 22:56 ` Michael Paquier <michael@paquier.xyz>
2026-09-24 00:06 ` Michael Paquier <michael@paquier.xyz>
2026-09-24 02:40 ` Naga Appani <nagnrik@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox