Re: Re-read subscription state after lock in AlterSubscription

On Tue, 4 Aug 2026 at 11:25, Bertrand Drouvot
<bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Mon, Jul 06, 2026 at 02:53:39PM +0000, Bertrand Drouvot wrote:
> > Hi,
> >
> > On Mon, Jul 06, 2026 at 03:07:24PM +0530, Amit Kapila wrote:
> >
> > DROP SUBSCRIPTION however has its own dedicated code path and does not go through
> > get_object_address(): 0003 adds the retry loop for it. And if DROP already uses
> > the retry loop then ALTER should probably use it too (also done in 0003 and 0004).
>
> Mandatory rebase attached.
>
Hi Bertrand,

While reviewing another patch, I found a bug on HEAD. It occurs when
ALTER SUBSCRIPTION ... REFRESH PUBLICATION and DROP SUBSCRIPTION runs
concurrently.
Suppose we have a logical replication setup between subscriber 'sub1'
and publisher 'pub1'.

Initially, we have:
postgres=# select oid, subname from pg_subscription;
oid | subname
-------+---------
16389 | sub1
(1 row)

postgres=# select * from pg_subscription_rel;
srsubid | srrelid | srsubstate | srsublsn
---------+---------+------------+------------
16389 | 16384 | r | 0/01750B90
(1 row)

Suppose we have two sessions S1 and S2 (attached GDB in S1).
S1: ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION. (stops at a
breakpoint at 'LockSharedObject(SubscriptionRelationId, subid, 0,
AccessExclusiveLock)')
S2: DROP SUBSCRIPTION sub1;
S1: continue the execution

Both ALTER and DROP executes successfully.

After execution:
postgres=# select oid, subname from pg_subscription;
oid | subname
-----+---------
(0 rows)

postgres=# select * from pg_subscription_rel;
srsubid | srrelid | srsubstate | srsublsn
---------+---------+------------+----------
16389 | 16384 | i |
(1 row)

The subscription has been removed from pg_subscription, but entries
for that subscription remain in pg_subscription_rel. In other words,
DROP SUBSCRIPTION succeeds, yet orphaned rows are recreated in
pg_subscription_rel. I believe this is a bug.
The issue occurs because ALTER SUBSCRIPTION fetches the subscription
info before acquiring the subscription lock. If the subscription is
dropped in the meantime, ALTER SUBSCRIPTION continues using the stale
info. Although DROP SUBSCRIPTION removes the existing rows from
pg_subscription_rel, the still-running ALTER SUBSCRIPTION fetches the
publication table list from the publisher and recreates the
corresponding pg_subscription_rel entries in the 'init' state.

I believe the root cause is the same as the issue discussed in this
thread, so I'm reporting it here.
I tested this with the v5 patch series, and it resolves the issue.

Thanks,
Shlok Kyal