Re: DROP SUBSCRIPTION, query cancellations and slot handling

On 20/04/17 09:22, Michael Paquier wrote:
> Hi,
>
> I have noticed the following behavior with DROP SUBSCRIPTION followed
> by a cancel request. If the remote replication slot is dropped, the
> subscription may still be present locally:
> =# CREATE SUBSCRIPTION mysub CONNECTION 'port=5432 user=mpaquier
> dbname=mpaquier' PUBLICATION mypub, insert_only;
> NOTICE: 00000: created replication slot "mysub" on publisher
> LOCATION: CreateSubscription, subscriptioncmds.c:408
> NOTICE: 00000: synchronized table states
> LOCATION: CreateSubscription, subscriptioncmds.c:434
> CREATE SUBSCRIPTION
> =# DROP SUBSCRIPTION mysub;
> ^CCancel request sent
> NOTICE: 00000: dropped replication slot "mysub" on publisher
> LOCATION: DropSubscription, subscriptioncmds.c:873
> ERROR: 57014: canceling statement due to user request
> LOCATION: ProcessInterrupts, postgres.c:2984
>
> In this case the subscription is not dropped:
> =# select subname from pg_subscription;
> subname
> ---------
> mysub
> (1 row)
> But trying to issue once again a drop results in an error:
> =# DROP SUBSCRIPTION mysub;
> ERROR: XX000: could not drop the replication slot "mysub" on publisher
> DETAIL: The error was: ERROR: replication slot "mysub" does not exist
> LOCATION: DropSubscription, subscriptioncmds.c:869
>
> A subscription with the same name cannot be created either, so there
> is nothing that the user can do except drop manually the slot on the
> publisher. It seems to me that the moment where the slot is created
> should be a point of no-return: the subcription has to be dropped on
> the replication slot is dropped on the remote.
>

DROP SUBSCRIPTION mysub NODROP SLOT;

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services