Re: Per-table resync for logical replication subscriptions

Hi Shlok ,

thank you , fixed in v13, attached.

REFRESH TABLE held pg_subscription_rel while it waited for a lock on a user
relation. It now takes every relation lock first and opens the catalogue
afterwards, which is the order AlterSubscription_refresh() already uses, so
the cycle cannot form. The lock level was not the problem: RowExclusiveLock
conflicts with the AccessExclusiveLock that command wants there just as
surely.

039 asserts it directly -- while REFRESH TABLE waits on a relation lock it
must hold no lock on pg_subscription_rel -- and then runs your two commands
concurrently in that window. Four of those five checks fail if the change
is
reverted, one with "deadlock detected".

The reproduction is attached as poc_refresh_publication_deadlock.sh: your
recipe, without gdb, so it can run unattended. A prepared transaction
holds a
second table named in the REFRESH TABLE, which parks the command in its
first
pass exactly where your breakpoint was. Can you please confirm ?

B opens pg_subscription_rel, resolves t1, waits on t_block
A takes AccessShareLock on t1, then waits on pg_subscription_rel
X commits, releasing t_block
B asks for AccessExclusiveLock on t1, which A holds -> deadlock detected

Best regards,
Cagri Biroglu

On Thu, Oct 29, 2026 at 1:32 PM Shlok Kyal <shlok(dot)kyal(dot)oss(at)gmail(dot)com> wrote:

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