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

On Thu, Jul 31, 2025 at 5:14 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Thu, Jul 31, 2025 at 2:34 PM Hayato Kuroda (Fujitsu)
> <kuroda(dot)hayato(at)fujitsu(dot)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?
>
> IMHO, what we should do is to set a flag in subscription that whether
> the connect is true or not, and drop subscription should not try to
> drop the slot if the connect is false, thoughts?

Maybe not, since this exists in previous branches as well and we
cannot alter the system catalog in back branches. Let me put more
thoughts on this.

--
Regards,
Dilip Kumar
Google