Re: Per-table resync for logical replication subscriptions

On Mon, 26 Oct 2026 at 02:18, Cagri Biroglu <cagri(dot)biroglu(at)adyen(dot)com> wrote:
>
> Dear Hayato,
>
> Thank you. v12 is attached.

Hi Cagri,

I reviewed the v12-0001 patch and found a possible deadlock when ALTER
SUBSCRIPTION ... REFRESH PUBLICATION and ALTER SUBSCRIPTION ...
REFRESH TABLE run concurrently on two different subscriptions.

Steps to reproduce the issue:
1. On publisher node:
CREATE TABLE t1 (a int);
CREATE TABLE obsolete (a int);
CREATE PUBLICATION pub1 FOR TABLE t1;
CREATE PUBLICATION pub2 FOR TABLE obsolete;

2. On subscriber node:
CREATE TABLE t1 (a int);
CREATE TABLE obsolete (a int);
CREATE SUBSCRIPTION sub1 CONNECTION ... PUBLICATION pub1 WITH
(enabled = false);
CREATE SUBSCRIPTION sub2 CONNECTION ... PUBLICATION pub2;

3. After sub2 is synchronized, change pub2 on the publisher: ALTER
PUBLICATION pub2 SET TABLE t1;

4. Attach GDB to subscriber session A and add a breakpoint before this
code in AlterSubscription_refresh():
if (!rel)
rel = table_open(SubscriptionRelRelationId, AccessExclusiveLock);
Then run: ALTER SUBSCRIPTION sub2 REFRESH PUBLICATION WITH (copy_data = false);

5. Attach GDB to subscriber session B and add a breakpoint before this
code in AlterSubscription_refresh_table():
foreach_oid(relid, relids)
LockRelationOid(relid, AccessExclusiveLock);
Then run: ALTER SUBSCRIPTION sub1 REFRESH TABLE t1;
Continue session B first, then session A. The sessions wait for each
other, and PostgreSQL reports a deadlock.

The deadlock happens because REFRESH PUBLICATION takes AccessShareLock
on the user table at:
"relid = RangeVarGetRelid(rv, AccessShareLock, false);"
When removing the old table, it requests AccessExclusiveLock on
pg_subscription_rel:
```
if (!rel)
rel = table_open(SubscriptionRelRelationId, AccessExclusiveLock);
```

At the same time, REFRESH TABLE takes RowExclusiveLock on pg_subscription_rel:
"rel = table_open(SubscriptionRelRelationId, RowExclusiveLock);"
It then requests AccessExclusiveLock on the user table here:
```
foreach_oid(relid, relids)
LockRelationOid(relid, AccessExclusiveLock);
```
So, REFRESH PUBLICATION command waits for lock on pg_subscription_rel
but REFRESH TABLE already have a lock on it
and REFRESH TABLE waits for lock on the user table t1 but REFRESH
PUBLICATION already have an lock on it.
Hence it leads to a deadlock.

Thanks,
Shlok Kyal