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

On Thu, 31 Jul 2025 at 14:34, 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?

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.

Thoughts?

Regards,
Vignesh