On Tue, 4 Aug 2026 at 11:25, Bertrand Drouvot <[email protected]> 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
