On Mon, Aug 4, 2025 at 9:07 AM vignesh C <vignesh21@gmail.com> wrote:
>
> On Thu, 31 Jul 2025 at 14:34, Hayato Kuroda (Fujitsu)
> <kuroda.hayato@fujitsu.com> wrote:
> >
> > Dear Vignesh, Dilip,
> >
> > I found another corner case:
> >
> > ```
> > postgres=# CREATE SUBSCRIPTION sub CONNECTION 'dbname=db1 user=postgres port=5431' PUBLICATION pub1 WITH
(connect=false,slot_name=sub);
> > WARNING: subscription was created, but is not connected
> > HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh
thesubscription.
> > CREATE SUBSCRIPTION
> > postgres=# DROP SUBSCRIPTION sub ;
> > ... (won't return)
> > ```
> >
> > Because still can explicitly specify the slot_name while creating the subscription.
> > Another pattern is to run ALTER SUBSCRIPTION SET (slot_name) command after the
> > CREATE SUBSCRIPTION WITH (connect=false);.
> >
> > Should we fix the case? If so, how?
>
> An alternative approach would be to acquire an AccessShareLock on
> pg_subscription, and then explicitly lock the target subscription row
> using LockSharedObject with AccessExclusiveLock while dropping the
> subscription. On the launcher side, before starting a worker, it
> should similarly acquire an AccessExclusiveLock on the corresponding
> subscription row using LockSharedObject. Once the lock is acquired, it
> must revalidate that the subscription still exists, to prevent race
> conditions with concurrent drops, before proceeding to start the
> worker.
Thanks for the idea, so currently during drop subscription we are
already doing LockSharedObject on the subscription in
AccessExclusiveLock. So now we should try to acquire
AccessExclusiveLock on the launcher side and then revalidate the
object existence, let me try this and post a patch in a while?
--
Regards,
Dilip Kumar
Google
