Document prepared transaction lock waits with two_phase subscriptions

Hi,

While reviewing the prepared-transaction locking hazard involving the
conflict log table [1], we noticed that the issue is not specific to the
conflict log table and also affects regular user tables.

When two_phase is enabled, a prepared transaction on the publisher is
also prepared on the subscriber, and like any prepared transaction, it
continues to hold its locks until COMMIT PREPARED or ROLLBACK PREPARED.
On the subscriber, if a replicated prepared transaction holds a lock on
a user table and a local session then tries to acquire a conflicting
lock on the same table (e.g., via TRUNCATE, VACUUM FULL, or ALTER
TABLE), the local session will block.

In this state, if there are further replicated changes on the same
table, the apply worker will also be blocked when acquiring the table
lock to apply those changes. Because the apply worker cannot advance to
subsequent transactions, even if COMMIT PREPARED or ROLLBACK PREPARED is
issued on the publisher, the apply worker will not be able to reach and
apply it. As a result, replication stalls because the sessions wait for
each other, and the deadlock detector does not detect this wait cycle
since the prepared transaction is not waiting on any lock. This is
similar to the existing caution in the prepared transaction documentation:

"""
It is unwise to leave transactions in the prepared state for a long
time ... Keep in mind also that the transaction continues to hold
whatever locks it held. The intended usage of the feature is that a
prepared transaction will normally be committed or rolled back as soon
as an external transaction manager has verified that other databases are
also prepared to commit.
"""

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. Please see the attached
patch for the documentation changes.

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.

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. For example, suppose a prepared transaction holds a
lock on table t1, a local session acquires AccessExclusiveLock on table
t2 and then blocks trying to TRUNCATE t1, and the apply worker next
tries to apply a change to t2. Because the apply worker is waiting on t2
while the prepared transaction holds the lock on t1, inspecting only
t2's lock would miss the cycle.

Given these trade-offs, we prefer to document this locking hazard and
the workarounds for now.

[1]
https://www.postgresql.org/message-id/CAJpy0uAWb7hs0271M_NxhOpetAfFcQidrUce5xSOL8jU3Hx1iQ%40mail.gmail.com

--
Best Regards,
Zhijie Hou