Prevent WAIT FOR LSN from deadlocking recovery on held locks A backend waiting for WAL replay can retain locks acquired by earlier statements. If the startup process needs one of those locks, directly or through another backend, before reaching the target LSN, a deadlock can arise: startup waits for the backend to release the lock, while the backend waits for startup to advance replay. The lock manager records the backend's held locks, but WAIT FOR LSN does not register its dependency on replay as a lock wait. The deadlock detector therefore cannot see the complete cycle. With unlimited standby conflict delays and no other timeout or cancellation, this deadlock can persist indefinitely.
Write and flush waits are restricted as well. Their positions are floored by the replay position, so without an active walreceiver the startup process can be their only source of progress. If a held lock blocks replay, these waits can form the same cycle: the backend waits for replay to advance, while replay waits for the backend to release the lock. Streaming does advance them independently, but only while WAL keeps arriving. If reception stops before the target is reached, a blocked startup process cannot restart the walreceiver. It also cannot replay newer checkpoint records needed to advance restartpoints and recycle WAL, so continued reception can exhaust available space in pg_wal before the target is reached. An active receiver at the start of the wait therefore does not guarantee that the wait can finish while replay remains blocked. Reject an unsatisfied standby_replay, standby_write, or standby_flush wait while recovery is active when the backend already holds a granted heavyweight lock. This conservative restriction covers direct relation-lock cycles and indirect cycles involving advisory locks. It also rejects some write and flush waits that an active receiver could satisfy. Requests whose target is observed as already reached are exempt from this check, as are primary_flush requests and requests issued after recovery has ended. Existing snapshot and recovery-state checks still apply. Report one of the held locks so the user can find it, using the same description the deadlock report uses. Add replay-mode tests for relation and advisory locks and for the already-reached case, and document the restriction along with the recommended usage pattern. Author: Xuneng Zhou <[email protected]> Discussion: https://postgr.es/m/CABPTF7U0gW5%2B-4oL7-qdML-yerZxUb7ku4QXp7JxCYo0qyJ_Tw%40mail.gmail.com Reviewed-by: Alexander Korotkov <[email protected]> Backpatch-through: 19 Branch ------ REL_19_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/f7c0929170a370f8229e66f54ea7396441efaaf1 Modified Files -------------- doc/src/sgml/ref/wait_for.sgml | 40 ++++++++++++++++++++++++ src/backend/commands/wait.c | 55 +++++++++++++++++++++++++++++++++ src/backend/storage/lmgr/lock.c | 32 +++++++++++++++++++ src/include/storage/lock.h | 1 + src/test/recovery/t/049_wait_for_lsn.pl | 36 +++++++++++++++++++++ 5 files changed, 164 insertions(+)
