Re: DROP SUBSCRIPTION with no slot

This also seems to be a problem for somewhat fringe case of subscriptions created with connect=false option.
They cannot be dropped in an obvious way, without knowing the ALTER SUBSCRIPTION trick.

For example:

contrib_regression=# create subscription test_sub connection 'dbname=contrib_regression' publication test_pub with ( connect=false );
WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
CREATE SUBSCRIPTION

contrib_regression=# drop subscription test_sub; -- fails
ERROR: could not drop the replication slot "test_sub" on publisher
DETAIL: The error was: ERROR: replication slot "test_sub" does not exist

contrib_regression=# alter subscription test_sub set ( slot_name=none );
ALTER SUBSCRIPTION

contrib_regression=# drop subscription test_sub; -- succeeds
DROP SUBSCRIPTION

Note that the publication was never refreshed.
It seems that the first DROP should succeed in the above case.
Or at least some hint should be given how to fix this.

> On 24 Sep 2019, at 23:25, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
>
> On 2019-09-24 16:31, Jeff Janes wrote:
>> I recently had to cut loose (pg_drop_replication_slot) a logical replica
>> that couldn't keep up and so was threatening to bring down the master.
>>
>> In mopping up on the replica side, I couldn't just drop the
>> subscription, because it couldn't drop the nonexistent slot on the
>> master and so refused to work. So I had to do a silly little dance
>> where I first disable the subscription, then ALTER SUBSCRIPTION ... SET
>> (slot_name = NONE), then drop it.
>>
>> Wanting to clean up after itself is admirable, but if there is nothing
>> to clean up, why should that be an error condition?
>
> The alternatives seem quite error prone to me. Better be explicit.
>
> --
> Peter Eisentraut http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>