pg_rewind and hint bits leads to visibility corruption
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`.
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
Re: pg_rewind and hint bits leads to visibility corruption
От:
Sebastian Webber <sebastian@swebber.me>
Дата:
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
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