Re: pg_rewind and hint bits leads to visibility corruption

From: Jan Nidzwetzki <jan(at)planetscale(dot)com>
To: Tom Pang <tom(at)planetscale(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: pg_rewind and hint bits leads to visibility corruption
Date: 2026-11-20 22:33:35
Message-ID: 67dde646-bd6a-47b5-b5af-ba3a4dfcd17a@planetscale.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Attachment Content-Type Size
0001-Don-t-let-unlogged-hint-bits-reach-disk-on-buffers-d.patch text/plain 28.4 KB

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Masahiko Sawada 2026-11-20 22:35:41 Re: BUG #19879: Blocked COPY FROM reports NULL command and type in pg_stat_progress_copy
Previous Message David G. Johnston 2026-11-20 22:29:45 Re: BUG #19927: PostgreSQL 16: SQL dump adds experimental/incompatible options like \restrict