Re: Proposal: Conflict log history table for Logical Replication

On Tue, Oct 20, 2026 at 12:30 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> Also, if we don't check whether a prepared transaction exists, we
> would end up with an error if an autovacuum worker is truncating the
> CLT and the apply worker tries to acquire a lock on it. I'd rather
> expect the autovacuum worker to be interrupted in that case. If the
> apply worker waited for the lock as usual, autovacuum would give up
> truncating the table as it does for other lock waiters, but the
> conditional lock acquisition bypasses that.
>

The reason for not checking the prepared transaction is mentioned in
comments (We don't check whether prepared transactions exist before
erroring out, because one can appear while we wait. With parallel
apply, another worker of this subscription may prepare the transaction
that the command is waiting for, and we would then be stuck as
above.). Consider the following scenario:

PA-A : streamed txn S1 records a conflict -> holds RowExclusive on CLT
(S1 not prepared)
S2 : TRUNCATE CLT -> waits for S1
PA-B : streamed txn S2' records a conflict -> conditional lock fails
(TRUNCATE waiting) -> no prepared transactions -> blocking wait,
queued behind TRUNCATE
PA-A : STREAM PREPARE -> S1 becomes prepared P1 -> TRUNCATE now waits for P1
pub : COMMIT S2', then COMMIT PREPARED P1
leader: hands STREAM COMMIT to PA-B and waits for it -> PA-B is stuck
behind TRUNCATE -> COMMIT PREPARED P1 is never applied -> stall, no
ERROR

Now, to avoid the above race-condition, we can have a solution as
follows: (a) Take the lock normally on CLT with a timeout a bit above
deadlock_timeout, then ERROR if it expires. (b) Autovacuum then sees
us as a waiter and is cancelled. For the user
TRUNCATE-waiting-on-a-prepared-transaction case, the timeout turns the
hang into an ERROR and a restart.

With the above solution in place, we can also have a check for
prepared transactions instead of two_phase as the above will close the
boundary cases w.r.t parallel apply workers.

If for CLT, we agree something on the lines of above then for normal
tables, we can mention in two_phase option docs that when prepare
transactions are being replicated avoid conflicting locks like via
TRUNCATE on the table involved as that can lead to deadlock and the
only way is for user to rollback that transaction and arrange to
replicate it again by switching of two_phase.

--
With Regards,
Amit Kapila.