On Wed, Sep 16, 2026 at 8:37 AM Heikki Linnakangas <[email protected]> wrote: > > (adding Michael and Naga) > > I'd actually attribute the below issue to commit 97b101776c "Add > pg_get_multixact_stats()". In a nutshell, MultiXactState->oldestOffset > is not updated during recovery, and that was true before widening > multixacts to 64 bits already. That's intentional and wasn't visible to > users until the pg_get_multixact_stats() function was added. If you run > pg_get_multixact_stats() in a standby, the 'num_members' and > 'members_size' are calculated incorrectly, because oldestOffset is always 0. > > To fix, we could update MultiXactState->oldestOffset during recovery > too, from the control file at start of recovery, and whenever we see a > XLOG_MULTIXACT_TRUNCATE_ID record. We should probably still calculate a > fresh value at end of recovery, like we currently do. > Hi Heikki, Noah,
Thanks for the detailed analysis and for looping me in, and sorry for the
delayed response. This is about the pg_get_multixact_stats() standby issue
above, not the 9.3 pg_upgrade topic the thread has since moved to.
I reproduced it on a primary/standby pair built from REL_19_BETA3. On a
primary with wal_level=replica I generated a lot of multixacts with pgbench
running SELECT ... FOR SHARE (100 clients), took a pg_basebackup -R standby
and started it (still in recovery), then on the primary ran VACUUM FREEZE
in all databases (including template0) and a CHECKPOINT to move oldestOffset
forward.
Results (unpatched REL_19_BETA3)
--------------------------------
=== 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
=== STANDBY after promote ===
pg_is_in_recovery | num_mxids | num_members | members_size |
oldest_multixact
-------------------+-----------+-------------+--------------+------------------
f | 0 | 0 | 0 |
409396
The standby shows about 17.5M members / ~84 MB while the primary correctly
shows zero for the same data. oldestOffset is stuck at 0 on the standby, so
num_members comes out as nextOffset instead of nextOffset minus
oldestOffset. Promoting the standby fixes it right away, since the correct
oldestOffset is only computed at end of recovery.
Fix
---
Following your suggestion, the attached patch updates
MultiXactState->oldestOffset while replaying XLOG_MULTIXACT_TRUNCATE_ID, 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
you mentioned. 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.
Thank you!
Best regards,
Naga Appani
v1-0001-Maintain-MultiXact-oldestOffset-during-recovery.patch
Description: Binary data
