Re: Proposal: Conflict log history table for Logical Replication

On Sat, Oct 24, 2026 at 5:10 PM Zhijie Hou <houzhijie22(at)gmail(dot)com> wrote:
>
> 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.

1)
I checked and verified the fix posted by Hou-San on this idea. You’re
right. With the fix, when the apply worker and the blocking command
share the same lock_timeout, the user inititated TRUNCATE’s indefinite
wait becomes a lock-timeout ERROR and the situation self-resolves (the
TRUNCATE gives up and the worker proceeds). The only case that doesn’t
self-resolve is when the apply worker has lock_timeout > 0 but the
TRUNCATE session has 0; then the TRUNCATE hangs and the worker keeps
erroring/retrying until it’s cancelled. But I think this configuration
will be rare and impractical, so I don’t think this case is worth
handling or documenting. Let me know if you think otherwise?. (I
reproduced it by doing 'SET lock_timeout = 0' in session
performingTRUNCATE but I guess there could be other ways as well to
achieve this)

2)
I did notice the message differs between the two lock_timeout
settings. With lock_timeout > 0 we get the generic 'canceling
statement due to lock timeout'; with 0 we get the more informative
'could not obtain lock on conflict log table + hint'. Do you think it
would be better to maje the
informative message appear in both by checking for a prepared
transaction before the LockTimeout > 0 branch:

if (MySubscription->twophasestate == LOGICALREP_TWOPHASE_STATE_ENABLED &&
LookupGXactBySubid(MySubscription->oid))
ereport(ERROR, ... "could not obtain lock on conflict log table ...");

if (MySubscription->twophasestate != LOGICALREP_TWOPHASE_STATE_ENABLED ||
LockTimeout > 0)
{
LockRelationOid(relid, RowExclusiveLock);
return;
}

The fix otherwise looks good to me. One note: as with the DateStyle
approach we dropped, this also temporarily sets a GUC (lock_timeout)
in the apply worker, via the same save/restore mechanism. The
difference is that the DateStyle one ran on the common path (every
conflict logged to the table), whereas this only happens on a rare
path; when two_phase is enabled, no prepared transaction exists, and
the CLT lock still isn’t immediately available. Since it’s not on the
hot path, I think it should be fine here, right?

> >
> > 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