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

On 20/04/17 09:35, Michael Paquier wrote:
> On Thu, Apr 20, 2017 at 4:22 PM, Michael Paquier
> <michael.paquier@gmail.com> wrote:
>> I am adding an open item.
> 
> Just adding something... When a subscription is created, if the step
> synchronizing tables fails then CREATE SUBSCRIPTION fails but the slot
> remains present on the publisher side, so trying to re-create the same
> subscription results in an error:
> 
> =# CREATE SUBSCRIPTION mysub CONNECTION 'port=5432' PUBLICATION mypub,
> insert_only;
> NOTICE:  00000: Sleeping now...
> NOTICE:  00000: created replication slot "mysub" on publisher
> LOCATION:  CreateSubscription, subscriptioncmds.c:411
> ERROR:  42P01: relation "public.aa" does not exist
> LOCATION:  RangeVarGetRelidExtended, namespace.c:400
> Time: 1033.739 ms (00:01.034)
> =# CREATE SUBSCRIPTION mysub CONNECTION 'port=5432 user=mpaquier
> dbname=mpaquier' PUBLICATION mypub, insert_only;
> NOTICE:  00000: Sleeping now...
> LOCATION:  CreateSubscription, subscriptioncmds.c:376
> ERROR:  XX000: could not create replication slot "mysub": ERROR:
> replication slot "mysub" already exists
> LOCATION:  libpqrcv_create_slot, libpqwalreceiver.c:776
> 


CREATE SUBSCRIPTION mysub CONNECTION 'port=5432' PUBLICATION mypub,
insert_only WITH(NOCREATE SLOT);

Or you can drop the slot manually on upstream.



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