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

On Thu, Aug 14, 2025 at 2:18 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Thu, Aug 14, 2025 at 2:05 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> >
> > Yeah this looks fine to me. PFA patches for back branches.
> >
>
> Can we add a test based on the scenario reported in this email?

At first, I thought we can just add this test in subscription.sql, but
IMHO we should not do that because after testing DROP SUBSCRIPTION
give error we should eventually DROP the subscription for cleanup by
creating the slot and enabling the subscription (as shown below[1])
but I that realized during regression wal_level is not logical so we
are not allowed to create the logical slot. So maybe we should write
it in 100_bugs.pl? Am I missing something?

[1]
-- ok CREATE SUBSCRIPTION WITH (connect=false),
CREATE DATABASE regression_db;
CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regression_db'
PUBLICATION testpub WITH (connect=false);

-- fail, due to non existing slot
-- should not create deadlock with initilizing caches of new databse
DROP SUBSCRIPTION regress_testsub;
SELECT 'init' FROM
pg_create_logical_replication_slot('regress_testsub', 'pgoutput');

-- ok, create missing slot, enable the subscription and drop again
SELECT 'init' FROM
pg_create_logical_replication_slot('regress_testsub', 'pgoutput');
ALTER SUBSCRIPTION regress_testsub ENABLE;
DROP SUBSCRIPTION regress_testsub;

--
Regards,
Dilip Kumar
Google