Re: Proposal: Conflict log history table for Logical Replication

Hi,

On Thu, Oct 22, 2026 at 7:38 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> 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.

I tried implementing this approach in the 0002 top up patch. The patch reuses
the existing lock_timeout GUC to break the wait on the CLT table lock. The basic
logic is: it first tests whether it can directly acquire the lock (using the
conditional lock function); if not, it checks whether a prepared transaction
exists and reports an error if so. If no prepared transaction exists, it
manually sets lock_timeout = deadlock_timeout + 5s and then waits on the CLT
table. If the user already set a lock_timeout, the patch just tries to wait on
the CLT lock to avoid complexity.

While developing, I also thought of another approach: following what we do for
transaction_timeout and the standby conflict recovery logic, by registering a
timeout event for our CLT path. Each time the timeout callback fires, it sets
the flag ConflictLogLockTimeoutPending to true; then the CIC call during
ProcSleep() notices this and does a timeout check to see whether a prepared
transaction for this subscription exists, and if so, reports an ERROR.

But since we consider this ERROR a very rare case, it does not seem worth
pushing the second approach, even though it's slightly more accurate, it checks
for a prepared transaction only after the CLT lock times out, but it interacts
with multiple other mechanisms, which seems over-engineered.

II also improved a few comments and some doc in 0002 to make it read better.
Other patches are unchanged.

Best Regards,
Zhijie Hou