On Thu, Sep 24, 2026 at 7:34 PM Xuneng Zhou <[email protected]> wrote:
>
> On Thu, Sep 24, 2026 at 7:00 PM Xuneng Zhou <[email protected]> wrote:
> >
> > On Thu, Sep 24, 2026 at 5:28 PM Bertrand Drouvot
> > <[email protected]> wrote:
> > >
> > > Hi,
> > >
> > > On Thu, Sep 24, 2026 at 03:49:35PM +0800, Xuneng Zhou wrote:
> > > > Hi hackers,
> > > >
> > > > I don't see a clear solution to this potential issue, because the
> > > > interface
> > > > is a function, which means that the held snapshots cannot be popped
> > > > cleanly
> > > > since they belong to the surrounding executor.
> > >
> > > Thanks for the report and reproducers!
> > >
> > > Thinking out loud, I wonder if we could add a transient PGPROC state
> > > while a
> > > backend depends on recovery replay. After deadlock_timeout,
> > > ResolveRecoveryConflictWithVirtualXIDs()
> > > could check whether a VXID in its waitlist has that state set and, if so,
> > > use the
> > > existing recovery conflict cancellation path.
> > >
> > > Does that make sense to you and others? If so, I can have a look at
> > > preparing a
> > > patch.
> >
> > The overall direction looks promising to me, and I haven't come up
> > with a simpler fix. As a side benefit, it could also break the
> > potential deadlock where 'WAIT' command waits for replay while
> > recovery waits for the same backend's VXID. It would be good to hear
> > more echo before heading to implementation.
>
> Here's the reproducer for the mentioned VXID issue. I think we need to
> test the fix for it as well, since the underlying issue remains the
> same. The reproducers could fit in existing test files like 031, but
> for clarity, they are in standalone files. Also CCed Alexander for
> this.
After more investigation, both slot functions seem also vulnerable to
VXID deadlock issue like the WAIT command. My original thought for the
fix of the issue is to let ResolveRecoveryConflictWithSnapshot make
the blocking decision based on the actual snapshot conflict rather
than simply checking whether the VXID has gone away. However, that is
more complex and needs more consideration than what you proposed. It
could be a follow-up optimization, not necessarily the bug fix.
Another problem that both functions suffered is the heavyweight
deadlock issue[1].
I also asked Astra to do a broader inspection for the same categorical
issue in the tree, and it did find more, which I'll share later.
[1]
https://www.postgresql.org/message-id/CABPTF7U0gW5%2B-4oL7-qdML-yerZxUb7ku4QXp7JxCYo0qyJ_Tw%40mail.gmail.com
--
Regards,
Xuneng Zhou
HighGo Software Co., Ltd.
# Copyright (c) 2026, PostgreSQL Global Development Group
# Reproducer: WAIT FOR LSN on a standby can deadlock with WAL replay when
# max_standby_streaming_delay = -1.
#
# Snapshot conflict resolution captures the VXIDs of transactions whose
# snapshots conflict with a cleanup record, and then waits for those
# transactions to end. A captured transaction can release its snapshot but
# stay open, and then run WAIT FOR LSN for a position beyond the cleanup
# record. The startup process waits for the transaction, and the
# transaction waits for replay. Neither side has a timeout.
#
# The assertions below describe the deadlock, so they pass on affected
# servers. hot_standby_feedback is off (the default); with it on, the
# primary would normally keep the old row version and no conflict would
# arise.
use strict;
use warnings FATAL => 'all';
use PostgreSQL::Test::Cluster;
use PostgreSQL::Test::Utils;
use Test::More;
my $primary = PostgreSQL::Test::Cluster->new('primary');
$primary->init(allows_streaming => 1);
$primary->start;
$primary->safe_psql(
'postgres',
'CREATE TABLE tab (a int) WITH (autovacuum_enabled = off);
INSERT INTO tab VALUES (1);');
$primary->backup('backup');
my $standby = PostgreSQL::Test::Cluster->new('standby');
$standby->init_from_backup($primary, 'backup', has_streaming => 1);
$standby->append_conf(
'postgresql.conf', qq[
max_standby_streaming_delay = -1
log_recovery_conflict_waits = on
]);
$standby->start;
$primary->wait_for_replay_catchup($standby);
# Hold a snapshot through a table-free cursor. No relation lock is taken,
# so WAIT FOR is allowed once the cursor is closed.
my $session = $standby->background_psql('postgres', on_error_stop => 0);
my $session_pid = $session->query_safe('SELECT pg_backend_pid()');
chomp $session_pid;
$session->query_safe(
q[BEGIN;
DECLARE c CURSOR FOR SELECT 1;
FETCH c;]);
# Remove the old row version on the primary. Replaying the prune record
# conflicts with the cursor's snapshot, so the startup process captures the
# session's VXID and waits for its transaction to end.
my $log_offset = -s $standby->logfile;
$primary->safe_psql('postgres', 'UPDATE tab SET a = a + 1');
$primary->safe_psql('postgres', 'VACUUM tab');
$standby->wait_for_log(qr/Conflicting process: $session_pid\b/, $log_offset);
ok(1, 'startup process waits for the session');
# A committed transaction flushes the WAL written so far, so the standby
# receives the target position below.
$primary->safe_psql('postgres', 'CREATE TABLE flush_marker ()');
my $target_lsn = $primary->lsn('flush');
# Release the snapshot but keep the transaction open, then wait for a
# position beyond the blocked prune record.
$session->query_until(
qr/waiting/, qq[
CLOSE c;
\\echo waiting
WAIT FOR LSN '$target_lsn';
]);
ok( $standby->poll_query_until(
'postgres',
"SELECT wait_event = 'WaitForWalReplay' FROM pg_stat_activity WHERE pid = $session_pid"
),
'session is waiting in WAIT FOR');
$standby->poll_query_until('postgres',
"SELECT pg_last_wal_receive_lsn() >= '$target_lsn'")
or die "standby did not receive the target position";
# The session no longer holds a snapshot, but the startup process still
# waits for its transaction, and the session waits for replay.
is( $standby->safe_psql(
'postgres',
"SELECT backend_xmin IS NULL FROM pg_stat_activity WHERE pid = $session_pid"
),
't',
'session no longer holds a snapshot');
is( $standby->safe_psql(
'postgres', "SELECT pg_last_wal_replay_lsn() < '$target_lsn'"),
't',
'replay has not reached the received target position');
is( $standby->safe_psql(
'postgres',
"SELECT wait_event FROM pg_stat_activity WHERE backend_type = 'startup'"
),
'RecoveryConflictSnapshot',
'startup process is still waiting on the snapshot conflict');
# Break the cycle by canceling the WAIT. The error aborts the transaction,
# which ends its VXID, so replay continues while the session is still in
# the aborted transaction block.
$standby->safe_psql('postgres', "SELECT pg_cancel_backend($session_pid)");
$primary->wait_for_replay_catchup($standby);
ok(1, 'replay resumes once the WAIT is canceled');
$session->query('ROLLBACK');
$session->quit;
done_testing();
# Copyright (c) 2026, PostgreSQL Global Development Group
# Probe: the slot sync worker keeps a synced slot temporary, and active for
# its own PID, when the remote slot is behind what the standby can reserve
# ("could lead to data loss"). Replaying an XLOG_DBASE_DROP for that slot's
# database then runs ReplicationSlotsDropDBSlots(), which errors on an active
# slot; in the startup process that is FATAL and shuts the standby down.
#
# Default max_standby_streaming_delay, no cancel, no replay pause.
#
# mv: ALTER DATABASE d SET TABLESPACE on the primary (the primary keeps
# the slot)
# dr: DROP DATABASE d on the primary (the primary drops the slot)
use strict;
use warnings FATAL => 'all';
use PostgreSQL::Test::Cluster;
use PostgreSQL::Test::Utils;
use Test::More;
for my $case (qw(mv dr))
{
my $primary = PostgreSQL::Test::Cluster->new("${case}_primary");
$primary->init(allows_streaming => 'logical');
$primary->append_conf('postgresql.conf', 'allow_in_place_tablespaces = on');
$primary->start;
$primary->safe_psql('postgres', 'CREATE DATABASE d');
$primary->safe_psql('postgres', "CREATE TABLESPACE ts2 LOCATION ''");
$primary->safe_psql('postgres',
"SELECT pg_create_physical_replication_slot('sb_slot')");
$primary->backup('backup');
my $standby = PostgreSQL::Test::Cluster->new("${case}_standby");
$standby->init_from_backup($primary, 'backup', has_streaming => 1);
my $connstr = $primary->connstr;
$standby->append_conf(
'postgresql.conf', qq[
hot_standby_feedback = on
primary_slot_name = 'sb_slot'
primary_conninfo = '$connstr dbname=postgres'
log_recovery_conflict_waits = on
]);
$standby->start;
# An inactive failover slot in d, then a transaction that moves the
# standby's safe catalog xmin past the slot's.
$primary->safe_psql('d',
"SELECT pg_create_logical_replication_slot('fs', 'test_decoding', false, false, true)"
);
$primary->safe_psql('postgres', 'CREATE TABLE t (a int)');
$primary->wait_for_replay_catchup($standby);
my $offset = -s $standby->logfile;
$standby->append_conf('postgresql.conf', 'sync_replication_slots = on');
$standby->reload;
$standby->wait_for_log(qr/could not synchronize replication slot "fs"/,
$offset);
my $worker = $standby->safe_psql('postgres',
"SELECT pid FROM pg_stat_activity WHERE backend_type = 'slotsync worker'"
);
is( $standby->safe_psql(
'postgres',
"SELECT temporary, active_pid FROM pg_replication_slots WHERE slot_name = 'fs'"
),
"t|$worker",
"$case: synced slot stays temporary and active for the worker between cycles"
);
$offset = -s $standby->logfile;
if ($case eq 'mv')
{
$primary->safe_psql('postgres', 'ALTER DATABASE d SET TABLESPACE ts2');
}
else
{
$primary->safe_psql('postgres', 'DROP DATABASE d');
}
# Either the standby shuts down, or it replays past the drop.
my $lsn = $primary->lsn('insert');
my $result;
for (1 .. 100)
{
my $log = slurp_file($standby->logfile, $offset);
if ($log =~ /(startup\[\d+\] FATAL: [^\n]*)\n[^\n]*(CONTEXT: [^\n]*)/)
{
$result = "$1 / $2";
last;
}
my ($ret, $out) = $standby->psql('postgres',
"SELECT pg_last_wal_replay_lsn() >= '$lsn'");
if ($ret == 0 && $out eq 't')
{
$result = 'replayed past the drop';
last;
}
select(undef, undef, undef, 0.1);
}
note "$case: $result";
like($result, qr/FATAL: replication slot "fs" is active for PID $worker/,
"$case: replaying the database drop is FATAL in the startup process");
like(
slurp_file($standby->logfile, $offset),
qr/shutting down due to startup process failure/,
"$case: the standby shut down");
$primary->stop;
}
done_testing();
# Copyright (c) 2026, PostgreSQL Global Development Group
# Reproducer: creating a logical replication slot on a standby can deadlock
# with WAL replay through a *heavyweight lock*, not only through a snapshot.
#
# pg_create_logical_replication_slot() keeps waiting for replay to supply an
# xl_running_xacts record to decode from. If the creating transaction also
# holds a heavyweight lock on a relation, and replay next reaches a record
# that needs a conflicting lock on that relation (e.g. an AccessExclusiveLock
# taken on the primary), then the startup process waits for the slot creator
# to release the lock, while the slot creator waits for replay. Neither side
# has a timeout when max_standby_streaming_delay = -1.
#
# This is the same cycle WAIT FOR refuses to enter: WAIT errors out with
# "cannot wait for a standby LSN while holding locks". Slot creation has no
# such guard. The assertions below describe the deadlock, so they pass on
# affected servers.
use strict;
use warnings FATAL => 'all';
use PostgreSQL::Test::Cluster;
use PostgreSQL::Test::Utils;
use Test::More;
my $primary = PostgreSQL::Test::Cluster->new('primary');
$primary->init(allows_streaming => 'logical');
$primary->start;
$primary->safe_psql('postgres',
'CREATE TABLE foo (a int); CREATE TABLE bar (a int);');
$primary->backup('backup');
my $standby = PostgreSQL::Test::Cluster->new('standby');
$standby->init_from_backup($primary, 'backup', has_streaming => 1);
$standby->append_conf(
'postgresql.conf', qq[
max_standby_streaming_delay = -1
log_recovery_conflict_waits = on
]);
$standby->start;
$primary->wait_for_replay_catchup($standby);
# Transaction Z on the primary stays open so that any xl_running_xacts record
# logged meanwhile lists it as running; the slot creator therefore cannot find
# a start point and keeps waiting for replay.
my $xact_z = $primary->background_psql('postgres');
$xact_z->query_safe('BEGIN; SELECT pg_current_xact_id();');
# The slot creator's session first takes an AccessShareLock on foo, then starts
# creating a logical slot in the same transaction. It now holds the lock and
# waits for replay.
my $creator = $standby->background_psql('postgres', on_error_stop => 0);
my $creator_pid = $creator->query_safe('SELECT pg_backend_pid()');
chomp $creator_pid;
$creator->query_safe('BEGIN; LOCK TABLE foo IN ACCESS SHARE MODE;');
$creator->query_until(
qr/started/, q[
\echo started
SELECT pg_create_logical_replication_slot('standby_slot', 'test_decoding');
]);
$standby->poll_query_until('postgres',
"SELECT restart_lsn IS NOT NULL FROM pg_replication_slots WHERE slot_name = 'standby_slot'"
) or die "slot creation did not reserve WAL";
is( $standby->safe_psql(
'postgres',
"SELECT count(*) FROM pg_locks WHERE pid = $creator_pid AND relation = 'foo'::regclass AND granted"
),
'1',
'slot creator holds a lock on foo while waiting for replay');
# On the primary, take an AccessExclusiveLock on foo. This logs a standby lock
# record. Replaying it requires an AccessExclusiveLock on foo on the standby,
# which conflicts with the creator's AccessShareLock.
my $log_offset = -s $standby->logfile;
my $lock_holder = $primary->background_psql('postgres');
$lock_holder->query_safe('BEGIN; LOCK TABLE foo IN ACCESS EXCLUSIVE MODE;');
# Log a marker record after the lock record so we have a concrete LSN that sits
# behind the blocked lock record.
my $marker_lsn =
$primary->safe_psql('postgres', 'SELECT pg_log_standby_snapshot()');
$standby->poll_query_until('postgres',
"SELECT pg_last_wal_receive_lsn() >= '$marker_lsn'")
or die "standby did not receive the lock record";
# The startup process is now waiting for the creator to release its lock on foo.
$standby->wait_for_log(qr/recovery conflict on lock|Conflicting process: $creator_pid/,
$log_offset);
ok(1, 'startup process is waiting on the lock conflict');
# The standby has received the lock record but cannot replay it: the startup
# process waits for the slot creator's lock, and the slot creator waits for
# replay.
is( $standby->safe_psql(
'postgres', "SELECT pg_last_wal_replay_lsn() < '$marker_lsn'"),
't',
'replay has not reached the received marker record');
is( $standby->safe_psql(
'postgres',
"SELECT wait_event_type = 'Lock' FROM pg_stat_activity WHERE backend_type = 'startup'"
),
't',
'startup process is waiting on a lock');
is( $standby->safe_psql(
'postgres',
"SELECT confirmed_flush_lsn IS NULL FROM pg_replication_slots WHERE slot_name = 'standby_slot'"
),
't',
'slot creation has not found a start point');
# For contrast: WAIT FOR refuses to enter this cycle at all. Holding a lock
# and running WAIT FOR for an unreached target errors out immediately instead
# of hanging. This is the guard slot creation lacks.
my $target = $primary->lsn('flush');
my ($ret, $stdout, $stderr) = $standby->psql('postgres',
"BEGIN; LOCK TABLE bar IN ACCESS SHARE MODE; WAIT FOR LSN '$target';");
like(
$stderr,
qr/cannot wait for standby replay while holding|cannot wait for a standby LSN while holding locks/,
'WAIT FOR refuses to wait while holding a lock (the guard slot creation lacks)'
);
# Break the cycle by canceling the slot creator. The error aborts its
# transaction, releasing the lock and ending its VXID, so replay proceeds.
$standby->safe_psql('postgres', "SELECT pg_cancel_backend($creator_pid)");
$primary->wait_for_replay_catchup($standby);
ok(1, 'replay resumes once the slot creator is canceled');
ok( $standby->poll_query_until(
'postgres', 'SELECT count(*) = 0 FROM pg_replication_slots'),
'canceled slot creation left no slot');
$lock_holder->quit;
$xact_z->quit;
eval { $creator->quit };
done_testing();
# Copyright (c) 2026, PostgreSQL Global Development Group
# Reproducer: slot synchronization on a standby can deadlock with WAL replay
# through a heavyweight lock when max_standby_streaming_delay = -1.
#
# Slot sync decodes WAL from the slot's start and waits for replay to reach
# the remote confirmed_lsn. While it waits it holds heavyweight locks:
#
# fn_rel: a relation lock the calling transaction took before calling
# pg_sync_replication_slots(). Replaying a primary's
# AccessExclusiveLock on that relation waits for it.
# fn_db: AccessShareLock on the slot's database, which synchronize_slots()
# holds around synchronize_one_slot(). Replaying the XLOG_DBASE_DROP
# record that ALTER DATABASE ... SET TABLESPACE writes takes
# AccessExclusiveLock on the database first, and waits for it.
# wk_db: the same as fn_db, but the slot sync worker holds the lock.
#
# In all three, the startup process waits in ResolveRecoveryConflictWithLock(),
# which under -1 never cancels anyone. Its deadlock_timeout request is
# ignored because the syncer is waiting for replay, not for a lock.
#
# "side" separately checks what replaying the moved database's
# XLOG_DBASE_DROP does to a logical slot created on the standby.
#
# The assertions describe the deadlock, so they pass on affected servers.
use strict;
use warnings FATAL => 'all';
use PostgreSQL::Test::Cluster;
use PostgreSQL::Test::Utils;
use Test::More;
my @names = qw(fn_rel fn_db wk_db);
my $primary = PostgreSQL::Test::Cluster->new('primary');
$primary->init(allows_streaming => 'logical');
$primary->append_conf('postgresql.conf', 'allow_in_place_tablespaces = on');
$primary->start;
$primary->safe_psql('postgres', 'CREATE TABLE foo (a int)');
$primary->safe_psql('postgres', 'CREATE DATABASE d');
$primary->safe_psql('postgres', "CREATE TABLESPACE ts2 LOCATION ''");
$primary->safe_psql('postgres',
"SELECT pg_create_physical_replication_slot('${_}_slot')")
for @names;
# A logical slot on the primary in d, created before the move.
$primary->safe_psql('d',
"SELECT pg_create_logical_replication_slot('primary_keep', 'test_decoding')"
);
$primary->backup('backup');
my $connstr = $primary->connstr;
my %node;
for my $name (@names)
{
my $node = PostgreSQL::Test::Cluster->new($name);
$node->init_from_backup($primary, 'backup', has_streaming => 1);
$node->append_conf(
'postgresql.conf', qq[
hot_standby_feedback = on
primary_slot_name = '${name}_slot'
primary_conninfo = '$connstr dbname=postgres'
max_standby_streaming_delay = -1
deadlock_timeout = 100ms
log_recovery_conflict_waits = on
]);
$node->start;
$primary->wait_for_replay_catchup($node);
$node{$name} = $node;
}
my $side = PostgreSQL::Test::Cluster->new('side');
$side->init_from_backup($primary, 'backup', has_streaming => 1);
$side->start;
$primary->wait_for_replay_catchup($side);
$side->create_logical_slot_on_standby($primary, 'standby_own', 'd');
# fn_rel: the calling transaction holds a lock on foo.
my $fn_rel = $node{fn_rel}->background_psql('postgres', on_error_stop => 0);
my %sync_pid;
$sync_pid{fn_rel} = $fn_rel->query_safe('SELECT pg_backend_pid()');
chomp $sync_pid{fn_rel};
$fn_rel->query_safe('BEGIN; LOCK TABLE foo IN ACCESS SHARE MODE;');
# Pause replay so that the following steps happen in a known order. This
# stands in for replay lag (e.g. replay still copying the moved database).
$node{$_}->safe_psql('postgres', 'SELECT pg_wal_replay_pause()') for @names;
# On the primary: an AccessExclusiveLock on foo, then move d, then create a
# failover slot in d whose WAL starts after both.
$primary->safe_psql('postgres',
'BEGIN; LOCK TABLE foo IN ACCESS EXCLUSIVE MODE; COMMIT;');
$primary->safe_psql('postgres', 'ALTER DATABASE d SET TABLESPACE ts2');
$primary->safe_psql('d',
"SELECT pg_create_logical_replication_slot('fs', 'test_decoding', false, false, true)"
);
my $slot_lsn = $primary->safe_psql('postgres',
"SELECT confirmed_flush_lsn FROM pg_replication_slots WHERE slot_name = 'fs'"
);
for my $name (@names)
{
$node{$name}->poll_query_until('postgres',
"SELECT pg_last_wal_receive_lsn() >= '$slot_lsn'")
or die "$name did not receive the slot's WAL";
}
# side: replaying the move's XLOG_DBASE_DROP drops every logical slot of d on
# the standby, although the primary kept its own.
$primary->wait_for_replay_catchup($side);
is( $primary->safe_psql(
'postgres',
"SELECT count(*) FROM pg_replication_slots WHERE slot_name = 'primary_keep'"
),
'1',
'side: the primary keeps its logical slot in d after the move');
is( $side->safe_psql(
'postgres',
"SELECT count(*) FROM pg_replication_slots WHERE slot_name = 'standby_own'"
),
'0',
'side: the standby lost its own logical slot in d after replaying the move'
);
# Start syncing.
my $fn_db = $node{fn_db}->background_psql('postgres', on_error_stop => 0);
$sync_pid{fn_db} = $fn_db->query_safe('SELECT pg_backend_pid()');
chomp $sync_pid{fn_db};
$fn_rel->query_until(qr/started/,
"\\echo started\nSELECT pg_sync_replication_slots();\n");
$fn_db->query_until(qr/started/,
"\\echo started\nSELECT pg_sync_replication_slots();\n");
$node{wk_db}->append_conf('postgresql.conf', 'sync_replication_slots = on');
$node{wk_db}->reload;
for my $name (@names)
{
my $node = $node{$name};
$node->poll_query_until('postgres',
"SELECT count(*) = 1 FROM pg_replication_slots WHERE slot_name = 'fs' AND synced"
) or die "$name: slot sync did not create the local slot";
if ($name eq 'wk_db')
{
$sync_pid{$name} = $node->safe_psql('postgres',
"SELECT pid FROM pg_stat_activity WHERE backend_type = 'slotsync worker'"
);
}
is( $node->safe_psql(
'postgres',
"SELECT count(*) FROM pg_locks
WHERE pid = $sync_pid{$name} AND locktype = 'object'
AND classid = 'pg_database'::regclass
AND objid = (SELECT oid FROM pg_database WHERE datname = 'd')
AND mode = 'AccessShareLock' AND granted"
),
'1',
"$name: syncer holds AccessShareLock on database d while waiting for replay"
);
}
my %log_offset;
for my $name (@names)
{
$log_offset{$name} = -s $node{$name}->logfile;
$node{$name}->safe_psql('postgres', 'SELECT pg_wal_replay_resume()');
}
my %expect = (
fn_rel => [ 'relation', "locktype = 'relation' AND relation = 'foo'::regclass" ],
fn_db => [ 'object', "locktype = 'object' AND classid = 'pg_database'::regclass" ],
wk_db => [ 'object', "locktype = 'object' AND classid = 'pg_database'::regclass" ]);
for my $name (@names)
{
my $node = $node{$name};
my $pid = $sync_pid{$name};
my ($event, $lockqual) = @{ $expect{$name} };
ok( $node->poll_query_until(
'postgres',
"SELECT wait_event_type = 'Lock' AND wait_event = '$event' FROM pg_stat_activity WHERE backend_type = 'startup'"
),
"$name: startup waits for a heavyweight lock ($event)");
$node->wait_for_log(
qr/recovery conflict on lock[^\n]*\n[^\n]*Conflicting process: $pid\b/,
$log_offset{$name});
ok(1, "$name: startup reports the syncer as the conflicting process");
}
# Many deadlock_timeout periods pass; nothing breaks the cycle.
sleep(3);
for my $name (@names)
{
my $node = $node{$name};
my $pid = $sync_pid{$name};
my ($event, $lockqual) = @{ $expect{$name} };
is( $node->safe_psql(
'postgres', "SELECT pg_last_wal_replay_lsn() < '$slot_lsn'"),
't',
"$name: replay has not reached the slot position");
is( $node->safe_psql(
'postgres',
"SELECT wait_event_type || ':' || wait_event FROM pg_stat_activity WHERE backend_type = 'startup'"
),
"Lock:$event",
"$name: startup still waits for the lock");
is( $node->safe_psql(
'postgres',
"SELECT count(*) FROM pg_locks
WHERE pid = (SELECT pid FROM pg_stat_activity WHERE backend_type = 'startup')
AND $lockqual AND mode = 'AccessExclusiveLock' AND NOT granted"
),
'1',
"$name: startup's AccessExclusiveLock request is not granted");
is( $node->safe_psql(
'postgres',
"SELECT coalesce(wait_event_type, '-') <> 'Lock' FROM pg_stat_activity WHERE pid = $pid"
),
't',
"$name: the syncer is not waiting for a lock");
is( $node->safe_psql(
'postgres',
"SELECT temporary FROM pg_replication_slots WHERE slot_name = 'fs'"),
't',
"$name: synced slot has not become persistent");
note "$name: syncer activity: "
. $node->safe_psql('postgres',
"SELECT backend_type, state, coalesce(wait_event_type, '-'), coalesce(wait_event, '-') FROM pg_stat_activity WHERE pid = $pid"
);
}
# Break each cycle by canceling the syncer. The syncer releases its
# heavyweight locks when it aborts, but releases its temporary synced slot
# only later. In the fn_db and wk_db cases the lock release wakes the startup
# process at once, and if it reaches ReplicationSlotsDropDBSlots() before the
# slot is released, it fails with "replication slot is active", which is
# FATAL in the startup process and shuts the standby down. Which side wins is
# timing; the worker's longer exit path makes the crash likely there.
for my $name (@names)
{
my $node = $node{$name};
my $offset = -s $node->logfile;
$node->safe_psql('postgres', "SELECT pg_cancel_backend($sync_pid{$name})");
my $lsn = $primary->lsn('insert');
my $outcome;
for (1 .. 300)
{
my $log = slurp_file($node->logfile, $offset);
if ($log =~ /startup\[\d+\] (FATAL: [^\n]*)/)
{
$outcome = "standby shut down: $1";
last;
}
my ($ret, $out) = $node->psql('postgres',
"SELECT pg_last_wal_replay_lsn() >= '$lsn'");
if ($ret == 0 && $out eq 't')
{
$outcome = 'replay caught up';
last;
}
select(undef, undef, undef, 0.1);
}
note "$name: after canceling the syncer: " . ($outcome // 'no progress');
ok(defined $outcome, "$name: replay is no longer blocked once the syncer is canceled");
}
$fn_rel->quit;
$fn_db->quit;
done_testing();
# Copyright (c) 2026, PostgreSQL Global Development Group
# Probe: with a finite max_standby_streaming_delay the lock cycle of 061
# (syncer holds AccessShareLock on database d and waits for replay; replay of
# ALTER DATABASE d SET TABLESPACE needs AccessExclusiveLock on d) is broken by
# the startup process canceling the syncer. What happens next?
#
# The syncer releases its heavyweight locks when it aborts, before it
# releases its temporary synced slot. If the startup process gets the
# database lock in that window, ReplicationSlotsDropDBSlots() finds the slot
# still active and errors, which is FATAL in the startup process.
#
# wk_fin: slot sync worker
# fn_finN: pg_sync_replication_slots()
use strict;
use warnings FATAL => 'all';
use PostgreSQL::Test::Cluster;
use PostgreSQL::Test::Utils;
use Test::More;
my @names = qw(wk_fin fn_fin1 fn_fin2 fn_fin3);
my $primary = PostgreSQL::Test::Cluster->new('primary');
$primary->init(allows_streaming => 'logical');
$primary->append_conf('postgresql.conf', 'allow_in_place_tablespaces = on');
$primary->start;
$primary->safe_psql('postgres', 'CREATE DATABASE d');
$primary->safe_psql('postgres', "CREATE TABLESPACE ts2 LOCATION ''");
$primary->safe_psql('postgres',
"SELECT pg_create_physical_replication_slot('${_}_slot')")
for @names;
$primary->backup('backup');
my $connstr = $primary->connstr;
my %node;
for my $name (@names)
{
my $node = PostgreSQL::Test::Cluster->new($name);
$node->init_from_backup($primary, 'backup', has_streaming => 1);
$node->append_conf(
'postgresql.conf', qq[
hot_standby_feedback = on
primary_slot_name = '${name}_slot'
primary_conninfo = '$connstr dbname=postgres'
max_standby_streaming_delay = 1s
log_recovery_conflict_waits = on
]);
$node->start;
$primary->wait_for_replay_catchup($node);
$node->safe_psql('postgres', 'SELECT pg_wal_replay_pause()');
$node{$name} = $node;
}
$primary->safe_psql('postgres', 'ALTER DATABASE d SET TABLESPACE ts2');
$primary->safe_psql('d',
"SELECT pg_create_logical_replication_slot('fs', 'test_decoding', false, false, true)"
);
my $slot_lsn = $primary->safe_psql('postgres',
"SELECT confirmed_flush_lsn FROM pg_replication_slots WHERE slot_name = 'fs'"
);
$node{$_}->poll_query_until('postgres',
"SELECT pg_last_wal_receive_lsn() >= '$slot_lsn'")
for @names;
my (%sync_pid, %psql);
for my $name (@names)
{
my $node = $node{$name};
if ($name =~ /^wk_/)
{
$node->append_conf('postgresql.conf', 'sync_replication_slots = on');
$node->reload;
}
else
{
my $p = $node->background_psql('postgres', on_error_stop => 0);
$sync_pid{$name} = $p->query_safe('SELECT pg_backend_pid()');
chomp $sync_pid{$name};
$p->query_until(qr/started/,
"\\echo started\nSELECT pg_sync_replication_slots();\n");
$psql{$name} = $p;
}
$node->poll_query_until('postgres',
"SELECT count(*) = 1 FROM pg_replication_slots WHERE slot_name = 'fs' AND synced"
) or die "$name: slot sync did not create the local slot";
$sync_pid{$name} //= $node->safe_psql('postgres',
"SELECT pid FROM pg_stat_activity WHERE backend_type = 'slotsync worker'"
);
}
my %log_offset;
for my $name (@names)
{
$log_offset{$name} = -s $node{$name}->logfile;
$node{$name}->safe_psql('postgres', 'SELECT pg_wal_replay_resume()');
}
# The startup process cancels each syncer after about 1s.
for my $name (@names)
{
my $pid = $sync_pid{$name};
$node{$name}->wait_for_log(
qr/\[$pid\] [^\n]*ERROR: [^\n]*conflict with recovery/,
$log_offset{$name});
ok(1, "$name: startup canceled the syncer ($pid)");
}
sleep(2);
for my $name (@names)
{
my $log = slurp_file($node{$name}->logfile, $log_offset{$name});
my ($cancel) = $log =~ /(ERROR: [^\n]*conflict with recovery\n[^\n]*DETAIL: [^\n]*)/;
note "$name: syncer got: " . ($cancel // '?');
if ($log =~ /(startup\[\d+\] FATAL: [^\n]*)\n[^\n]*(CONTEXT: [^\n]*)/)
{
note "$name: $1 / $2";
like($log, qr/shutting down due to startup process failure/,
"$name: standby shut down after the startup process failed");
}
else
{
$primary->wait_for_replay_catchup($node{$name});
pass("$name: standby survived and caught up");
}
}
for my $p (values %psql)
{
eval { $p->quit };
}
done_testing();
# Copyright (c) 2026, PostgreSQL Global Development Group
# Reproducer: pg_sync_replication_slots() on a standby can deadlock with WAL
# replay when max_standby_streaming_delay = -1.
#
# To sync a failover slot, the standby decodes WAL from the slot's start and
# waits for replay to reach it. Suppose replay first reaches a DROP
# TABLESPACE record whose directories cannot be removed yet, because a standby
# session has temporary files there. Tablespace conflict resolution then
# waits for every transaction that was active, including the slot sync, which
# is waiting for replay. Once the temporary files are gone, only the slot
# sync is left, and neither side has a timeout.
#
# Slot synchronization requires hot_standby_feedback, which prevents most
# snapshot conflicts but not tablespace conflicts. The assertions below
# describe the deadlock, so they pass on affected servers.
use strict;
use warnings FATAL => 'all';
use PostgreSQL::Test::Cluster;
use PostgreSQL::Test::Utils;
use Test::More;
my $primary = PostgreSQL::Test::Cluster->new('primary');
$primary->init(allows_streaming => 'logical');
$primary->append_conf('postgresql.conf', 'allow_in_place_tablespaces = on');
$primary->start;
$primary->safe_psql('postgres', "CREATE TABLESPACE ts LOCATION ''");
$primary->safe_psql('postgres',
"SELECT pg_create_physical_replication_slot('sb1_slot')");
$primary->backup('backup');
my $standby = PostgreSQL::Test::Cluster->new('standby');
$standby->init_from_backup($primary, 'backup', has_streaming => 1);
my $primary_connstr = $primary->connstr;
$standby->append_conf(
'postgresql.conf', qq[
hot_standby_feedback = on
primary_slot_name = 'sb1_slot'
primary_conninfo = '$primary_connstr dbname=postgres'
max_standby_streaming_delay = -1
log_recovery_conflict_waits = on
]);
$standby->start;
$primary->wait_for_replay_catchup($standby);
# A standby session with temporary files in the tablespace. They prevent
# replay from removing the tablespace directories.
my $temp_user = $standby->background_psql('postgres');
$temp_user->query_safe(
q[BEGIN;
SET temp_tablespaces = ts;
SET work_mem = '64kB';
DECLARE c CURSOR FOR SELECT count(*) FROM generate_series(1, 6000);
FETCH c;]);
is( $standby->safe_psql(
'postgres',
"SELECT count(*) > 0 FROM pg_ls_tmpdir((SELECT oid FROM pg_tablespace WHERE spcname = 'ts'))"
),
't',
'standby session has temporary files in the tablespace');
# Pause replay so that the following steps happen in a known order. This
# stands in for ordinary replay lag.
$standby->safe_psql('postgres', 'SELECT pg_wal_replay_pause()');
# Drop the tablespace, then create a failover slot. The slot's WAL starts
# after the DROP TABLESPACE record, so syncing it requires replay to pass
# that record.
$primary->safe_psql('postgres', 'DROP TABLESPACE ts');
$primary->safe_psql('postgres',
"SELECT pg_create_logical_replication_slot('failover_slot', 'test_decoding', false, false, true)"
);
my $slot_lsn = $primary->safe_psql('postgres',
"SELECT confirmed_flush_lsn FROM pg_replication_slots WHERE slot_name = 'failover_slot'"
);
# Slot sync skips a slot whose position the standby has not received yet.
$standby->poll_query_until('postgres',
"SELECT pg_last_wal_receive_lsn() >= '$slot_lsn'")
or die "standby did not receive the slot's WAL";
# Start syncing. The sync creates the local slot, then decodes WAL from the
# slot's start and waits for replay, which is paused.
my $syncer = $standby->background_psql('postgres', on_error_stop => 0);
my $syncer_pid = $syncer->query_safe('SELECT pg_backend_pid()');
chomp $syncer_pid;
$syncer->query_until(
qr/started/, q[
\echo started
SELECT pg_sync_replication_slots();
]);
$standby->poll_query_until('postgres',
"SELECT count(*) = 1 FROM pg_replication_slots WHERE slot_name = 'failover_slot' AND synced"
) or die "slot sync did not create the local slot";
# Resume replay. Replaying DROP TABLESPACE cannot remove the directories, so
# the startup process waits for every active transaction, including the slot
# sync.
my $log_offset = -s $standby->logfile;
$standby->safe_psql('postgres', 'SELECT pg_wal_replay_resume()');
$standby->wait_for_log(qr/Conflicting process(?:es)?: [^\n]*\b$syncer_pid\b/,
$log_offset);
ok(1, 'startup process waits for the slot sync');
# Release the temporary files and end that transaction. The startup process
# rechecks the transaction it waits for at least once a second, so a few
# seconds later it can only be waiting for the slot sync.
$temp_user->query_safe('CLOSE c; COMMIT;');
sleep(3);
# Replay cannot pass the DROP TABLESPACE record: the startup process waits for
# the slot sync, which waits for replay.
is( $standby->safe_psql(
'postgres', "SELECT pg_last_wal_replay_lsn() < '$slot_lsn'"),
't',
'replay has not reached the slot position');
is( $standby->safe_psql(
'postgres',
"SELECT wait_event FROM pg_stat_activity WHERE backend_type = 'startup'"
),
'RecoveryConflictTablespace',
'startup process is still waiting on the tablespace conflict');
is( $standby->safe_psql(
'postgres',
"SELECT state FROM pg_stat_activity WHERE pid = $syncer_pid"),
'active',
'slot sync is still running');
is( $standby->safe_psql(
'postgres',
"SELECT temporary FROM pg_replication_slots WHERE slot_name = 'failover_slot'"
),
't',
'synced slot has not become persistent');
# Break the cycle by canceling the slot sync.
$standby->safe_psql('postgres', "SELECT pg_cancel_backend($syncer_pid)");
$primary->wait_for_replay_catchup($standby);
ok(1, 'replay resumes once the slot sync is canceled');
$syncer->quit;
$temp_user->quit;
done_testing();
# Copyright (c) 2026, PostgreSQL Global Development Group
# Reproducer: creating a logical replication slot on a standby can deadlock
# with WAL replay when max_standby_streaming_delay = -1.
#
# pg_create_logical_replication_slot() keeps its statement snapshot while it
# waits for replay to supply an xl_running_xacts record to start decoding
# from. If replay first reaches a cleanup record that conflicts with that
# snapshot, the startup process waits for the slot creator, and the slot
# creator waits for replay. Neither side has a timeout.
#
# The assertions below describe the deadlock, so they pass on affected
# servers. hot_standby_feedback is off (the default); with it on, the
# primary would normally keep the old row version and no conflict would
# arise.
use strict;
use warnings FATAL => 'all';
use PostgreSQL::Test::Cluster;
use PostgreSQL::Test::Utils;
use Test::More;
my $primary = PostgreSQL::Test::Cluster->new('primary');
$primary->init(allows_streaming => 'logical');
$primary->start;
$primary->safe_psql(
'postgres',
'CREATE TABLE tab (a int) WITH (autovacuum_enabled = off);
INSERT INTO tab VALUES (1);');
$primary->backup('backup');
my $standby = PostgreSQL::Test::Cluster->new('standby');
$standby->init_from_backup($primary, 'backup', has_streaming => 1);
$standby->append_conf(
'postgresql.conf', qq[
max_standby_streaming_delay = -1
log_recovery_conflict_waits = on
]);
$standby->start;
$primary->wait_for_replay_catchup($standby);
# Transaction X updates the row; VACUUM will later remove the old version.
# X stays open until the slot creator has taken its snapshot, so that the
# snapshot still sees the old version.
my $xact_x = $primary->background_psql('postgres');
$xact_x->query_safe('BEGIN; UPDATE tab SET a = a + 1;');
# Transaction Z is newer than X and stays open until the conflict is in
# place. Any xl_running_xacts record logged meanwhile lists Z as running,
# so slot creation cannot find a start point before the conflict arises.
my $xact_z = $primary->background_psql('postgres');
$xact_z->query_safe('BEGIN; SELECT pg_current_xact_id();');
# Start creating a logical slot on the standby, and wait until it has
# reserved WAL. It now waits for replay while holding the SELECT's snapshot.
my $creator = $standby->background_psql('postgres', on_error_stop => 0);
my $creator_pid = $creator->query_safe('SELECT pg_backend_pid()');
chomp $creator_pid;
$creator->query_until(
qr/started/, q[
\echo started
SELECT pg_create_logical_replication_slot('standby_slot', 'test_decoding');
]);
$standby->poll_query_until('postgres',
"SELECT restart_lsn IS NOT NULL FROM pg_replication_slots WHERE slot_name = 'standby_slot'"
) or die "slot creation did not reserve WAL";
# Commit X and vacuum. Replaying the prune record conflicts with the slot
# creator's snapshot, so the startup process waits for the slot creator.
my $log_offset = -s $standby->logfile;
$xact_x->query_safe('COMMIT');
$primary->safe_psql('postgres', 'VACUUM tab');
$standby->wait_for_log(qr/Conflicting process: $creator_pid\b/, $log_offset);
ok(1, 'startup process waits for the slot creator');
# Commit Z and log an xl_running_xacts record. It lists no running
# transactions, which is all the slot creator needs, but it follows the
# blocked prune record in the WAL.
$xact_z->query_safe('COMMIT');
my $running_xacts_lsn =
$primary->safe_psql('postgres', 'SELECT pg_log_standby_snapshot()');
$standby->poll_query_until('postgres',
"SELECT pg_last_wal_receive_lsn() >= '$running_xacts_lsn'")
or die "standby did not receive the xl_running_xacts record";
# The standby has received that record but cannot replay it: the startup
# process waits for the slot creator, which waits for replay.
is( $standby->safe_psql(
'postgres', "SELECT pg_last_wal_replay_lsn() < '$running_xacts_lsn'"),
't',
'replay has not reached the received xl_running_xacts record');
is( $standby->safe_psql(
'postgres',
"SELECT wait_event FROM pg_stat_activity WHERE backend_type = 'startup'"
),
'RecoveryConflictSnapshot',
'startup process is still waiting on the snapshot conflict');
is( $standby->safe_psql(
'postgres',
"SELECT confirmed_flush_lsn IS NULL FROM pg_replication_slots WHERE slot_name = 'standby_slot'"
),
't',
'slot creation has not found a start point');
# Break the cycle by canceling the slot creator.
$standby->safe_psql('postgres', "SELECT pg_cancel_backend($creator_pid)");
$primary->wait_for_replay_catchup($standby);
ok(1, 'replay resumes once the slot creator is canceled');
ok( $standby->poll_query_until(
'postgres', 'SELECT count(*) = 0 FROM pg_replication_slots'),
'canceled slot creation left no slot');
$creator->quit;
$xact_x->quit;
$xact_z->quit;
done_testing();
