Re: Proposal: Conflict log history table for Logical Replication

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.

--
Thanks,
Nisha