Hi, On Fri, Nov 13, 2026 at 11:58:10AM +0800, Zhijie Hou wrote: > > To recover from this state, the user can either cancel the waiting local > session on the subscriber so the apply worker can proceed, or manually roll > back the prepared transaction on the subscriber, disable two_phase until > that transaction is replicated again after being committed on the publisher, > and then re-enable two_phase if required. Considering the existing caution > about prepared transactions and the available workarounds, we suggest > documenting this locking hazard and how to resolve it for logical > replication as well.
Yeah, +1 for documenting it. > Please see the attached patch for the documentation changes. === 1 I wonder if it would also be worth mentioning a nonzero lock_timeout as a possible mitigation too? === 2 + logical replication apply worker can be completed only by that worker, see s/can be completed only by that worker/is normally completed by the subscription's apply worker/? === 3 + <command>ALTER TABLE</command>), it has to wait, and later lock + requests on that table s/later lock requests/later conflicting lock requests/? === 4 + The apply worker then cannot reach + the <command>COMMIT PREPARED</command> or <command>ROLLBACK + PREPARED</command> that would release the lock, and the sessions + wait for each other. This is not detected as a deadlock, so + replication stalls. what about? or <command>ROLLBACK PREPARED</command>. This situation is not detected as a deadlock, so replication can remain stalled indefinitely. > Apart from documenting this, Amit, Nisha, and I also discussed off-list > whether the apply worker could detect and resolve this wait cycle > automatically. One option we considered is to have the apply worker walk its > lock blocking chain once a lock wait exceeds deadlock_timeout. Starting from > the lock it is waiting for, the worker would trace conflicting lock holders > and conflicting waiters ahead of it in the wait queue (similar to how > pg_blocking_pids() finds blockers) to see whether the chain leads back to a > prepared transaction of the same subscription, and if so, cancel the session > waiting on that prepared transaction (or raise an error if the worker is > directly blocked by its own prepared transaction). However, this effectively > introduces a separate wait-graph traversal alongside the existing deadlock > detector, which seems overly complex for this scenario. Yeah, I agree. Teaching the lock manager about this logical dependency seems too complex. > We also thought about a much simpler check where, on timeout, the apply > worker only inspects the lock it is currently waiting on and treats it as a > wait cycle if a prepared transaction of the same subscription also holds a > lock on that same table. Unfortunately, that does not cover multi-table > cases. Agreed. FWIW, lock_timeout on the local session handles this multi-table case too. > Given these trade-offs, we prefer to document this locking hazard and the > workarounds for now. Another options I could see (because the doc changes explain the workarounds) would be to help diagnose: 1/ improve log_lock_waits to identify a prepared transaction holding a lock by its XID, instead of reporting it as process 0. 2/ improve the existing pg_locks example query so that it identifies locks held by both newly prepared and recovered prepared transactions. Thoughts? Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
