Re: [HACKERS] DROP SUBSCRIPTION, query cancellations and slothandling

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