# Copyright (c) 2026, PostgreSQL Global Development Group # Test that Hot Standby REPEATABLE READ snapshots either observe all # snapshot-visible rows or are canceled by a recovery conflict across: # 1. Non-concurrent CREATE INDEX # 2. VACUUM FULL and CLUSTER # 3. Hash index bucket splits with LP_DEAD-hinted entries use strict; use warnings FATAL => 'all'; use PostgreSQL::Test::Cluster; use Test::More; my $node_primary = PostgreSQL::Test::Cluster->new('primary'); $node_primary->init(allows_streaming => 1); $node_primary->append_conf( 'postgresql.conf', qq[ autovacuum = off hot_standby_feedback = off max_standby_streaming_delay = 50ms ]); $node_primary->start; $node_primary->backup('my_backup'); my $node_standby = PostgreSQL::Test::Cluster->new('standby'); $node_standby->init_from_backup($node_primary, 'my_backup', has_streaming => 1); $node_standby->start; my $psql_standby = $node_standby->background_psql('postgres', on_error_stop => 0); sub standby_vxid_alive { my ($pid) = @_; return $node_standby->safe_psql('postgres', "SELECT count(*) FROM pg_locks WHERE pid = $pid AND locktype = 'virtualxid';" ) ne '0'; } # Case 1: Non-concurrent CREATE INDEX on primary while a REPEATABLE READ # transaction is open on the standby. $node_primary->safe_psql( 'postgres', qq[ CREATE TABLE t_idx (id int, val int); INSERT INTO t_idx SELECT g, g FROM generate_series(1, 100) AS g; ]); $node_primary->wait_for_replay_catchup($node_standby); my $pid = $psql_standby->query_safe( qq[ BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT pg_backend_pid() FROM t_idx WHERE val = 1; ]); $node_primary->safe_psql( 'postgres', qq[ DELETE FROM t_idx WHERE id <= 10; CREATE INDEX t_idx_val ON t_idx (val); ]); $node_primary->wait_for_replay_catchup($node_standby); if (!standby_vxid_alive($pid)) { pass('CREATE INDEX: conflicting standby snapshot was canceled'); } else { my $idx_cnt = $psql_standby->query_safe( qq[ SET LOCAL enable_seqscan = off; SELECT count(*) FROM t_idx WHERE val = 1; ]); my $seq_cnt = $psql_standby->query_safe( qq[ SET LOCAL enable_seqscan = on; SET LOCAL enable_indexscan = off; SET LOCAL enable_bitmapscan = off; SELECT count(*) FROM t_idx WHERE id = 1; ]); is("$idx_cnt|$seq_cnt", '1|1', 'CREATE INDEX: index scan and seq scan agree in REPEATABLE READ'); } $psql_standby->reconnect_and_clear(); # Case 2: VACUUM FULL and CLUSTER when the standby REPEATABLE READ snapshot # was established before locking the rewritten table. Vacuum pg_class before # taking the snapshot so catalog updates stay HOT and do not trigger an # unrelated pg_class prune conflict. $node_primary->safe_psql( 'postgres', qq[ CREATE TABLE t_rewrite (id int PRIMARY KEY, val int); INSERT INTO t_rewrite SELECT g, g FROM generate_series(1, 20) AS g; VACUUM pg_catalog.pg_class; ]); $node_primary->wait_for_replay_catchup($node_standby); $pid = $psql_standby->query_safe( qq[ BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT pg_backend_pid(); ]); $node_primary->safe_psql( 'postgres', qq[ DELETE FROM t_rewrite WHERE id <= 10; VACUUM FULL t_rewrite; ]); $node_primary->wait_for_replay_catchup($node_standby); if (!standby_vxid_alive($pid)) { pass('VACUUM FULL: conflicting standby snapshot was canceled'); } else { my $res = $psql_standby->query_safe('SELECT count(*) FROM t_rewrite;'); is($res, '20', 'VACUUM FULL: standby snapshot sees all 20 visible rows'); } $psql_standby->reconnect_and_clear(); $node_primary->safe_psql('postgres', 'VACUUM pg_catalog.pg_class;'); $node_primary->wait_for_replay_catchup($node_standby); $pid = $psql_standby->query_safe( qq[ BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT pg_backend_pid(); ]); $node_primary->safe_psql( 'postgres', qq[ DELETE FROM t_rewrite WHERE id <= 15; CLUSTER t_rewrite USING t_rewrite_pkey; ]); $node_primary->wait_for_replay_catchup($node_standby); if (!standby_vxid_alive($pid)) { pass('CLUSTER: conflicting standby snapshot was canceled'); } else { my $res = $psql_standby->query_safe('SELECT count(*) FROM t_rewrite;'); is($res, '10', 'CLUSTER: standby snapshot sees all 10 visible rows'); } $psql_standby->reconnect_and_clear(); # Case 3: Hash index bucket split skipping LP_DEAD-hinted index entries. $node_primary->safe_psql( 'postgres', qq[ CREATE TABLE t_hash (id int, k int); 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); ]); $node_primary->wait_for_replay_catchup($node_standby); $pid = $psql_standby->query_safe( qq[ BEGIN ISOLATION LEVEL REPEATABLE READ; SET LOCAL enable_seqscan = off; SET LOCAL enable_bitmapscan = off; SELECT pg_backend_pid() FROM t_hash WHERE k = 1; ]); $node_primary->safe_psql( 'postgres', qq[ 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); ]); $node_primary->wait_for_replay_catchup($node_standby); if (!standby_vxid_alive($pid)) { pass('hash split: conflicting standby snapshot was canceled'); } else { my $idx_cnt = $psql_standby->query_safe('SELECT count(*) FROM t_hash WHERE k = 1;'); my $seq_cnt = $psql_standby->query_safe( qq[ SET LOCAL enable_seqscan = on; SET LOCAL enable_indexscan = off; SELECT count(*) FROM t_hash WHERE id = 1; ]); is("$idx_cnt|$seq_cnt", '1|1', 'hash split: hash index scan and seq scan agree in REPEATABLE READ'); } $psql_standby->quit; $node_standby->stop; $node_primary->stop; done_testing();