Hot Standby: missing snapshot conflicts in CREATE INDEX, rewrite, hash

From: Michael Malis <malis(at)pgrust(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Hot Standby: missing snapshot conflicts in CREATE INDEX, rewrite, hash
Date: 2026-11-16 20:28:08
Message-ID: CANVD+MDikAoTBK1ZCAd+_OuXdNO-kkcBke4E54MGfgWwdYmcGQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,

(I shared Case 1 briefly as a sample in my Nov 2 pgsql-hackers question;
filing it properly here together with two sibling cases and the minimal TAP
test Andres suggested.)

Reproduced on PostgreSQL 18.7 (REL_18_7, commit f5c41f6681c9) and verified
unchanged in source on REL_18_STABLE (commit 2f613719fa4e) and master
(commit 7fc2f0423320).

With default hot_standby_feedback = off, an open REPEATABLE READ
transaction on a streaming Hot Standby can silently lose rows visible to
its snapshot, without raising a recovery conflict, when the primary runs
any of three operations that discard dead tuples without logging a
snapshotConflictHorizon:

1. Non-concurrent CREATE INDEX (or REINDEX when the standby transaction has
not yet locked the target index).
2. VACUUM FULL or CLUSTER (and non-concurrent REPACK on master) when the
standby transaction established its snapshot before locking the table.
3. A hash index bucket split (or overflow page squeeze) that skips index
entries marked LP_DEAD on the primary.

Per the Hot Standby documentation (high-availability.sgml, "Handling Query
Conflicts"), replaying row removal from WAL conflicts with standby
transactions whose snapshots can still see any of the removed rows, waiting
up to max_standby_streaming_delay and then canceling the conflicting query.
In all three cases below, replay completes immediately with zero conflicts
recorded in pg_stat_database_conflicts even when
max_standby_streaming_delay = 30s, breaking the REPEATABLE READ snapshot
guarantee.

Steps to reproduce (primary and streaming standby with default
hot_standby_feedback = off and max_standby_streaming_delay = 30s):

Case 1: Non-concurrent CREATE INDEX

-- Primary:
CREATE TABLE t_idx (id int, val int) WITH (autovacuum_enabled = off);
INSERT INTO t_idx SELECT g, g FROM generate_series(1, 100) AS g;

-- Standby (after replay catches up):
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT id, val FROM t_idx WHERE val = 1;
-- id | val
-- ----+-----
-- 1 | 1
-- (1 row)

-- Primary:
DELETE FROM t_idx WHERE id <= 10;
CREATE INDEX t_idx_val ON t_idx (val);

-- Standby (after replay catches up, in the same transaction):
SELECT id, val FROM t_idx WHERE val = 1;
-- id | val
-- ----+-----
-- (0 rows)

SELECT id, val FROM t_idx WHERE id = 1;
-- id | val
-- ----+-----
-- 1 | 1
-- (1 row)

SELECT count(*) FROM t_idx;
-- count
-- -------
-- 100
-- (1 row)

SELECT count(*) FROM t_idx WHERE val <= 20;
-- count
-- -------
-- 10
-- (1 row)
-- expected: 20, or transaction canceled by recovery conflict

COMMIT;

Case 2: VACUUM FULL and CLUSTER

-- Primary:
CREATE TABLE t_rewrite (id int PRIMARY KEY, val int)
WITH (autovacuum_enabled = off);
INSERT INTO t_rewrite SELECT g, g FROM generate_series(1, 20) AS g;
VACUUM pg_catalog.pg_class;

-- Standby (after replay catches up, before locking t_rewrite):
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT 1;

-- Primary:
DELETE FROM t_rewrite WHERE id <= 10;
VACUUM FULL t_rewrite;

-- Standby (after replay catches up, in the same transaction):
SELECT count(*) FROM t_rewrite;
-- count
-- -------
-- 10
-- (1 row)
-- expected: 20, or transaction canceled by recovery conflict

COMMIT;

-- Primary:
VACUUM pg_catalog.pg_class;

-- Standby (after replay catches up, same pattern with CLUSTER):
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT 1;

-- Primary:
DELETE FROM t_rewrite WHERE id <= 15;
CLUSTER t_rewrite USING t_rewrite_pkey;

-- Standby (after replay catches up, in the same transaction):
SELECT count(*) FROM t_rewrite;
-- count
-- -------
-- 5
-- (1 row)
-- expected: 10, or transaction canceled by recovery conflict

COMMIT;

Case 3: Hash index bucket split with LP_DEAD entries

-- Primary:
CREATE TABLE t_hash (id int, k int) WITH (autovacuum_enabled = off);
INSERT INTO t_hash SELECT g, g FROM generate_series(1, 80) AS g;
ANALYZE t_hash;
CREATE INDEX t_hash_k ON t_hash USING hash (k) WITH (fillfactor = 10);

-- Standby (after replay catches up):
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET LOCAL enable_seqscan = off;
SET LOCAL enable_bitmapscan = off;
SELECT id, k FROM t_hash WHERE k = 1;
-- id | k
-- ----+---
-- 1 | 1
-- (1 row)

-- Primary (mark k = 1 LP_DEAD via plain index scan, then trigger split):
DELETE FROM t_hash WHERE k = 1;
SET enable_seqscan = off;
SET enable_bitmapscan = off;
SELECT id, k FROM t_hash WHERE k = 1;
RESET enable_seqscan;
RESET enable_bitmapscan;
INSERT INTO t_hash VALUES (81, 81);

-- Standby (after replay catches up, in the same transaction):
SELECT id, k FROM t_hash WHERE k = 1;
-- id | k
-- ----+---
-- (0 rows)

SET LOCAL enable_seqscan = on;
SET LOCAL enable_indexscan = off;
SELECT id, k FROM t_hash WHERE id = 1;
-- id | k
-- ----+---
-- 1 | 1
-- (1 row)

COMMIT;

-- Standby:
SELECT confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock
FROM pg_stat_database_conflicts
WHERE datname = current_database();
-- confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock
-- ------------+----------------+-----------------+----------------
-- 0 | 0 | 0 | 0
-- (1 row)

Attached is 099_hot_standby_mvcc.pl, a minimal src/test/recovery/t/ TAP test
modeled on 031_recovery_conflict.pl that reproduces all three cases.

Observations from the source:

1. CREATE INDEX and REINDEX:
In src/backend/access/heap/heapam_handler.c,
heapam_index_build_range_scan() computes OldestXmin via
GetOldestNonRemovableTransactionId(heapRelation) for non-concurrent
builds (!indexInfo->ii_Concurrent) and scans the heap with SnapshotAny.
Tuples deleted and committed before the primary's OldestXmin test as
HEAPTUPLE_DEAD under HeapTupleSatisfiesVacuum() and are skipped
(indexIt = false). Index pages written through smgr_bulk_write() and
log_newpages() (for example in src/backend/access/nbtree/nbtsort.c)
carry no snapshotConflictHorizon, and index_build() in
src/backend/catalog/index.c only sets indcheckxmin when
ii_BrokenHotChain is true. Because DefineIndex() takes ShareLock on the
heap relation, replay does not conflict with AccessShareLock held by an
open REPEATABLE READ transaction on the standby, so subsequent index
scans, index-only scans, and bitmap index scans in that snapshot use the
new index and omit the deleted tuples while sequential scans still
return them. Calling reindex_index() in src/backend/catalog/index.c
invokes the same index_build() path; its AccessExclusiveLock on the
index cancels standby transactions that already locked the index, but a
REPEATABLE READ transaction whose snapshot was taken before touching the
table or index hits no lock conflict and observes the same discrepancy
afterward.

2. VACUUM FULL and CLUSTER (and non-concurrent REPACK on master):
In copy_table_data() (src/backend/commands/cluster.c on REL_18_STABLE;
src/backend/commands/repack.c on master), vacuum_get_cutoffs() computes
cutoffs.OldestXmin and passes it to heapam_relation_copy_for_cluster()
in src/backend/access/heap/heapam_handler.c. Tuples returning
HEAPTUPLE_DEAD against OldestXmin are omitted from NewHeap
(isdead = true), and src/backend/access/heap/rewriteheap.c writes
NewHeap via smgr_bulk_write() with no snapshotConflictHorizon. Replay of
AccessExclusiveLock (XLOG_STANDBY_LOCK) only conflicts with standby
transactions that already hold a lock on that table. Unless a pg_class
page happens to be full and triggers an unrelated catalog
XLOG_HEAP2_PRUNE_ON_ACCESS record on a later scan (avoided in the
reproducer above by vacuuming pg_class during setup), an older standby
REPEATABLE READ transaction that has not yet locked the table avoids any
conflict and reads NewHeap, missing rows that were visible in its
snapshot on both sequential and index scans.

3. Hash index bucket split and overflow squeeze:
Among the three index AMs that set LP_DEAD hint bits during index scans
(btree, gist, and hash), btree (_bt_delitems_delete_check() in
src/backend/access/nbtree/nbtpage.c), gist (gistprunepage() in
src/backend/access/gist/gist.c), and hash's single-page cleanup
(_hash_vacuum_one_page() in src/backend/access/hash/hashinsert.c) all
compute a snapshotConflictHorizon via table_index_delete_tuples() or
index_compute_xid_horizon_for_tuples() and resolve snapshot conflicts on
replay. By contrast, when _hash_kill_items() in
src/backend/access/hash/hashutil.c marks an item LP_DEAD on the primary
(via MarkBufferDirtyHint(), unlogged when wal_log_hints = off) and a
later insert triggers _hash_expandtable(), _hash_splitbucket() in
src/backend/access/hash/hashpage.c skips ItemIdIsDead() items instead of
copying them to the new bucket (XLOG_HASH_SPLIT_PAGE and
XLOG_HASH_SPLIT_COMPLETE), and hashbucketcleanup() in
src/backend/access/hash/hash.c deletes entries belonging to the new
bucket from the old bucket (XLOG_HASH_DELETE). Similarly,
_hash_squeezebucket() in src/backend/access/hash/hashovfl.c skips
ItemIdIsDead() items on overflow pages before freeing them
(XLOG_HASH_SQUEEZE_PAGE). None of those split or squeeze WAL records log
a snapshotConflictHorizon.

On backbranches where WAL record layouts must stay compatible across minor
releases, emitting an existing conflict-carrying WAL record when dead
tuples were skipped (and, for hash indexes, either retaining LP_DEAD items
across bucket splits and squeezes or cleaning them via
_hash_vacuum_one_page() beforehand) would avoid changing existing WAL
record structs.

Cheers,
Michael

Attachment Content-Type Size
099_hot_standby_mvcc.pl text/plain 5.4 KB

Browse pgsql-bugs by date

  From Date Subject
Next Message Michael Malis 2026-11-16 20:28:10 dbase_redo: XLOG_DBASE_CREATE_FILE_COPY replay empties live database
Previous Message Борис Мурашов 2026-11-16 20:23:02 Re: BUG #19906: Backend crash (signal 11) creating a LANGUAGE sql function that returns a composite type from a sub-