Re: BUG #18988: DROP SUBSCRIPTION locks not-yet-accessed database

On Mon, Aug 4, 2025 at 9:29 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> 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 the subscription.
> > > 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?
>

I have worked on this and produced a first version of patch, let's see
what others think about this idea.  It would have been better if we
could use SysCache for rechecking the subscription, but since we are
not connected to the database in the launcher we can not use the
SysCache, at least that's what I think.

-- 
Regards,
Dilip Kumar
Google