Re: Proposal: Conflict log history table for Logical Replication

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.

I am thinking further on how this can be fixed.

Thanks
Shveta