pg_rewind and hint bits leads to visibility corruption
Hi there,
We've found a visibility corruption bug that can happen if you use
pg_rewind against a standby that has written hint bits. Attached is a
TAP test that reproduces it reliably.
At a high level, this bug is:
1. We write hint bits on standbys
2. pg_rewind doesn't overwrite hint bits written, only pages that are
written via the diverging WAL on the primary
3. a standby that is rewound can have a divergence in which rows are
visible from the new primary.
Postgres attempts to avoid this kind of issue by not marking pages as
dirty when writing hint bits on a standby. But WAL replay can dirty the
same page and lead to those hint bits reaching disk.
I've reproduced this against main, as well as 18.6.
A high level description of the TAP test follows. There are many
similar scenarios possible; this is one of the simplest/smallest.
1. Setup a streaming replication setup with 3 nodes, A as primary, S1,
S2 as replicas.
2. S2 is our target, S1 is our rewind source
3. On A, create table c with 100 rows and checkpoint.
4. In a background session on A: BEGIN; INSERT INTO c VALUES (999999);,
capture the transaction's xid, then PREPARE TRANSACTION 'p' — a
two-phase
transaction left in limbo. A checkpoint runs on A, and both
standbys replay up to it.
5. Detach S1 from A: clear its primary_conninfo, reload, wait for its
WAL receiver to drop to zero.
6. Kill the background session, rollback the prepared transaction on A,
and let S2 replay the ROLLBACK PREPARED.
7. Stop S2 cleanly.
8. Promote S1, then force a checkpoint on S1
9. Crash-stop A (immediate).
10. On S1, COMMIT PREPARED 'p' — S1 resolves the orphaned prepared
transaction as committed on its own timeline.
11. Run pg_rewind with S1 as source, S2 as target
12. Fix S2's port (pg_rewind copied S1's config), start S2
At this point, we have different results from the following SELECT on
S1 and S2, despite having just successfully done a pg_rewind:
SELECT count(*) FROM c WHERE id = 999999;
Specifically, S1 will return `1` and S2 will return `0`.
Thanks to my coworkers jan@planetscale.com and sinjo@planetscale.com for
their assistance on this bug.
Tom
Hi Tom,
On Wed, Oct 28, 2026 at 11:47 AM Tom Pang <tom@planetscale.com> wrote:
I've reproduced this against main, as well as 18.6.
Your test reproduces it on every supported branch too: 19rc1, 18, 17,
16, 15 and 14. On each one, tests 3 and 4 fail the same way as on
master. S2 still has HEAP_XMIN_INVALID after the rewind and returns 0
rows for id = 999999, while S1 returns 1.
On 14 to 18 the test needed two changes:
- The last wait_for_replay_catchup($node_s2) times out. The TAP
framework sets PGAPPNAME to the script name, and
--write-recovery-conf keeps it in primary_conninfo. So S2 streams as
"012_standby_hint_bits.pl", and the query on pg_stat_replication
never finds "node_s2". On master and 19 the helper uses WAIT FOR LSN
on the standby instead (7e8aeb9e483), so the name does not matter.
I replaced the wait with a poll on pg_last_wal_replay_lsn() on S2.
With that change master still fails the same way.
- 14 and 15 have no wait_for_replay_catchup, so I used
wait_for_catchup($node, 'replay'). I also dropped the meson.build
hunk, since those trees were built with autoconf.
I can send the adjusted test if it helps.
Builds: REL_19_RC1 (0b8bff042a), REL_18_STABLE (e4807a0f3f),
REL_17_STABLE (b3852eec68), REL_16_STABLE (17c5a0659f), REL_15_STABLE
(cb51daa27d), REL_14_STABLE (9a56e080f1) with autoconf, and master
(4848fd6752) with meson. Debian trixie, the Debian packages' build
dependencies and configure options (without LLVM), plus assertions.
I tested the branch heads, not released minor versions.
I used Claude Opus 5.5 (reasoning medium) to run the builds and tests
and to read the results, and Codex gpt-6.1-sol (reasoning high) to
review the test changes against the source.
Regards,
Sebastian
Hi,
On 28.10.26 12:47, Tom Pang wrote:
Postgres attempts to avoid this kind of issue by not marking pages as
dirty when writing hint bits on a standby. But WAL replay can dirty the
same page and lead to those hint bits reaching disk.
I looked into the problem. As far as I can see, pg_rewind keeps blocks
that were last changed before the redo pointer of the last common
checkpoint, assuming the checkpoint has written them out.
A standby can replay checkpoint records without always performing a
restartpoint, so a buffer last changed before that redo pointer might
still be dirty at the fork. During recovery, a hint bit does not dirty a
clean buffer (see MarkSharedBufferDirtyHint()), so it is lost if the
buffer is evicted. However, if replay has already dirtied the buffer,
the hint bit is written out with it. In this case, pg_rewind keeps the
block because no WAL record after that redo pointer refers to it.
last common checkpoint
redo record fork
| | |
S2 ------+-----+-----+------------+--------+--------+------->
| | \ |
| PREPARE T \ ROLLBACK
INSERT by T \ PREPARED T
(page LSN) \
S1 +------+----->
|
COMMIT
PREPARED T
pg_rewind, for a block last changed in this part of the WAL:
<-- kept as is -->|<-- FPI in replay -->|<--- copied ---->
In Tom's test, S2 sets HEAP_XMIN_INVALID for T on the dirty buffer,
while T commits on S1. This can also happen after a promotion and before
the first checkpoint, since hint bits are WAL-logged based on the redo
pointer of the last restartpoint until then.
I don't see a full fix in pg_rewind. It would need to start at the last
restartpoint the target finished before the fork, but that isn't
recorded anywhere. Copying blocks touched by xids >= oldestActiveXid of
the last common checkpoint misses index LP_DEAD hints set after a
promotion, requires target WAL that a standby usually no longer has, and
doesn't cover shutdown checkpoints.
Proposed fix
============
A hint on a page last changed before the redo pointer of a replayed
checkpoint is only an issue if its transaction ended after that
checkpoint. oldestActiveXid is calculated before the redo pointer, so
any xid below it had ended by then. The patch tracks the redo pointer
and oldestActiveXid for the last four replayed checkpoints. For a page
whose LSN isn't newer than the redo pointer of the last replayed
checkpoint, a hint is only safe if its xid comes before the
oldestActiveXid of the first replayed checkpoint that started at or
after the page LSN. Otherwise:
* During recovery, the hint is not set on a dirty buffer.
* After a promotion and before the first checkpoint, the hint is
WAL-logged. Index LP_DEAD hints are skipped. If a hint dirties a clean
buffer, it is WAL-logged even if it is safe, since the buffer might have
hints set in memory during recovery.
Performance
===========
Some initial benchmarks (shared_buffers set to 3GB, data checksums
enabled) show no difference for pgbench select-only and TPC-B on either
the primary or standby. Only a few cases are slower, all of them first
scans: a scan of standby pages that are still dirty from before the last
replayed checkpoint (98 ms versus 86 ms unpatched), a scan on the
primary after 2 million single-row transactions (111.5 ms versus 108.8
ms), and an index-only scan after a promotion, which wrote 1.338 GB of
WAL instead of 1.317 GB.
Attached is v1 for master, including an updated version of Tom's TAP test.
Any comments on the approach?
Best regards
Jan
--
Jan Nidzwetzki
PlanetScale
Hello
1. On Fri, 20 Nov 2026, Jan Nidzwetzki <jan@planetscale.com> wrote:
and an index-only scan after a promotion, which wrote 1.338 GB of
WAL instead of 1.317 GB.
Depending on the workload, this can be much more significant: with the patch a standby after promotion, but before the first checkpoint will write all accessed previous dirty pages into the WAL as full page writes. Depending on how many dirty pages it has, and what queries are executed on it, this can possibly result in a huge wal burst. I'm not saying that this is a blocker, it is possibly acceptable, but it is something that should be pointed out.
2. Another performance issue: a clean restart of the primary disables the xid exemption on the standby
Shutdown checkpoints carry an Invalid oldestActiveXid. If that is the first replayed checkpoint after a page's LSN, every hint on that page is refused, even for long-committed rows. That lasts until a restartpoint, even if online checkpoints follow. This triggers the FPI case in (1), but it also causes a measurable performance degradation in queries during normal standby operation.
And while testing this I found a different issue: pg_rewind can't detect properly a mixed wal_log_hint_bits cluster if data checksums are also off, and operates in scenarios it shouldn't be; I'll create a separate thread about that
Hi Jan
I have some concerns about wal bloating here.
MarkBufferDirtyHint() forces a WAL record on a dirty page, even for
hints that don't depend on a transaction outcome. The pd_prune_xid
update in heap_page_prune_and_freeze() hits this on every page. With 1M
rows committed before the last checkpoint, the first scan after
promotion wrote 85 MB.
For Zsolt's 2:
xlog_redo() already gets the oldest prepared xid from
PrescanPreparedTransactions()
for a shutdown checkpoint, we can just reuse this?
Thanks,
Shihao
Hi Jan,
One correction to my last mail. pd_prune_xid does depend on a
transaction outcome, but a stale value is harmless.
The attached diff brings the first scan after promotion from 85 MB back
to 7.7 kB and your test is still good
Thanks,
Shihao