Re: Proposal: Conflict log history table for Logical Replication

On Mon, Oct 12, 2026 at 9:34 PM Nisha Moond <nisha(dot)moond412(at)gmail(dot)com> wrote:
>
> On Thu, Oct 8, 2026 at 11:42 AM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
> > On Wed, Oct 7, 2026 at 2:48 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> > >
> > > Thanks for the patches. I have resumed reviewing this thread. I think
> > > the size cap added in v79-0002 does not fully prevent the >1GB json
> > > value it is meant to guard against.
> > >
> >
> > I tested further. I am able to reproduce a deadlock on v79 using
> > prepared txns. Here are the steps and analysis:
> >
> > Pub, Sub config: max_prepared_transactions = 10
> >
> > -- pub, sub:
> > create table tp (a int PRIMARY KEY, b text);
> >
> > -- pub:
> > create publication pub1 for all tables;
> >
> > -- sub:
> > create subscription sub1 CONNECTION '...' publication pub1
> > WITH(conflict_log_destination='all', two_phase = on);
> >
> > -- pub:
> > insert into tp values (1,'x');
> >
> > -- sub:
> > delete from tp;
> >
> > -- pub: PREPARE (do NOT commit) a conflicting delete
> > BEGIN; delete from tp WHERE a=1; PREPARE TRANSACTION 'g1';
> >
> > -- sub: check prepared-txn
> > select gid FROM pg_prepared_xacts;
> >
> > -- sub: check lock held on CLT by prepared-txn
> > select c.relname, l.mode, l.granted, l.pid, p.gid
> > from pg_locks l
> > join pg_class c ON c.oid = l.relation
> > left join pg_prepared_xacts p
> > ON p.transaction IN (
> > select transactionid FROM pg_locks l2
> > where l2.virtualtransaction = l.virtualtransaction
> > and l2.locktype = 'transactionid')
> > WHERE c.relname LIKE 'pg_conflict_log_%';
> >
> > -- sub: Drop sub hangs.
> > drop subscription sub1;
> >
> > -- pub: Even rollback on pub can not resolve sub's hang as there is no
> > apply worker to apply that rollback.
> > rollback prepared 'g1';
> >
> > ~~
> >
> > So overall:
> > 1) Apply worker inserts the CLT row (RowExclusiveLock on CLT), then
> > prepares; the lock is now frozen in pg_gid_…, owned by no backend.
> > 2) Drop Sub holds an AccessExclusiveLock on the sub, stops the worker,
> > and attempts to drop the CLT, for which it waits for an
> > AccessExclusiveLock on the CLT (held by the prepared xact).
> > 3) The CLT lock can only be released when either commit or rollback
> > prepared is applied, which only the apply worker can do automatically.
> > 4) But relaunch of apply worker is stuck in InitializeLogRepWorker()
> > on the subscription's AccessShareLock, held by DROP.
> >
> > Thus, we have a deadlock that the deadlock detector is not able to
> > catch. It is only resolved if we do this on susbcriber:
> >
> > The only thing which can resolve it is manual rollback on sub in
> > adifferent session:
> > postgres=# SELECT gid FROM pg_prepared_xacts;
> > gid
> > ------------------
> > pg_gid_16392_667
> > (1 row)
> >
> > postgres=# ROLLBACK PREPARED 'pg_gid_16392_667';
> > ROLLBACK PREPARED
> >
> > ~~
> >
> > We don't see this deadlock with conflict_log_destination='log' config.
> >
>
> Thanks Shveta for the test case.
>
> Here is a patch fixing it. It applies on top of v80-0002.
>
> The root cause of this issue is that a prepared transaction that
> recorded a conflict keeps its lock (RowExclusiveLock) on the CLT until
> the apply worker applies the COMMIT/ROLLBACK PREPARED. Any command
> that needs a stronger lock on the CLT has to wait for it, and only the
> apply worker can finish the prepared transaction. This isn't detected
> as a deadlock, because the prepared transaction doesn't wait for a
> lock: it only ends when the apply worker reads its COMMIT PREPARED
> from the stream. So replication stalls. There are three ways a
> prepared transaction can end up in this situation:
>
> 1) DROP SUBSCRIPTION (the test you shared). DROP stops the workers and
> then drops the CLT, which waits for the prepared transaction. With the
> workers gone, nobody can apply the COMMIT PREPARED, so DROP hangs
> forever.
> Solution: DROP SUBSCRIPTION is refused if the subscription has a
> CLT and prepared transactions exist. The check runs after the workers
> are stopped, so no new prepared transaction can appear.
>
> 2. ALTER SUBSCRIPTION ... SET (conflict_log_destination = 'log'). The
> ALTER needs to drop the CLT, so it waits for the prepared
> transaction's lock. Any next conflict then makes the apply worker wait
> behind the ALTER in the lock queue, which causes an indefinite stall.
> Solution: changing from table/all to log is allowed only when the
> subscription is disabled, no worker is running and no prepared
> transactions exist. The subscription must be disabled because
> otherwise an apply transaction could be prepared while the ALTER is
> already waiting for its lock.
>
> 3. TRUNCATE / VACUUM FULL / REINDEX etc. on the CLT, or directly on
> its TOAST table. The command waits for the prepared transaction to get
> its strong lock. The apply worker's next conflict insert then waits
> behind that command in the queue, and we get the same stall.
> Solution: the apply worker takes the locks on the CLT and its TOAST
> table conditionally, and errors out if they aren't available, instead
> of waiting. This is to keep the CLT consistent: a detected conflict is
> always recorded.
>
> The patch also adds tests for the new error and the prepared
> transaction lock scenario.

I merged v80-0001 and v80-0002 and adjusted the commit message and
test accordingly; I also included Amit's and Hou-san's code
refactoring. Rebased Nisha's V80_2-0003 on top of that as 0002, also
merged Shveta's datestyle fix with some modification into the 0002

--
Regards,
Dilip Kumar
Google