Re: DROP SUBSCRIPTION, query cancellations and slot handling

On 20/04/17 09:35, Michael Paquier wrote:
> On Thu, Apr 20, 2017 at 4:22 PM, Michael Paquier
> <michael(dot)paquier(at)gmail(dot)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