Re: Create subscription with `create_slot=false` and incorrect slot name

2017-05-22 17:52 GMT-03:00 Dmitry Dolgov <9erthalion6(at)gmail(dot)com>:

> Maybe this question was already raised before, but I couldn't find a
> discussion. When I'm creating a subscription with `create_slot=false` looks
> like it's possible to pass a slot name with invalid characters. In this
> particular case both publisher and subscriber were on the same machine:
>
> =# CREATE SUBSCRIPTION test_sub CONNECTION 'dbname=db host=host
> port=port user=user' PUBLICATION test_pub WITH (create_slot=false,
> slot_name='test slot');
> NOTICE: 00000: synchronized table states
> LOCATION: CreateSubscription, subscriptioncmds.c:443
> CREATE SUBSCRIPTION
> Time: 5.887 ms
>
> The command succeed even if slot_name is invalid. That's because slot_name
isn't validated. ReplicationSlotValidateName() should be called in
parse_subscription_options() to avoid a pilot error. IMHO we should prevent
a future error (use of invalid slot name).

> Of course `test slot` doesn't exist, because I can't create a slot with
> incorrect name. And consequently I can't drop this subscription:
>
> =# DROP SUBSCRIPTION test_sub;
> ERROR: XX000: could not drop the replication slot "test slot" on
> publisher
> DETAIL: The error was: ERROR: replication slot name "test slot"
> contains invalid character
> HINT: Replication slot names may only contain lower case letters,
> numbers, and the underscore character.
> LOCATION: DropSubscription, subscriptioncmds.c:947
> Time: 2.615 ms
>
> Indeed you can drop the subscription. There are two details: (i)
subscription should be disabled and (ii) slot name can't be set.

bar=# drop subscription sub1;
ERROR: could not drop the replication slot "does_not_exist" on publisher
DETAIL: The error was: ERROR: replication slot "does_not_exist" does not
exist
bar=# alter subscription sub1 set (slot_name = NONE);
ERROR: cannot set slot_name = NONE for enabled subscription
bar=# alter subscription sub1 disable;
ALTER SUBSCRIPTION
bar=# drop subscription sub1;
ERROR: could not drop the replication slot "does_not_exist" on publisher
DETAIL: The error was: ERROR: replication slot "does_not_exist" does not
exist
bar=# alter subscription sub1 set (slot_name = NONE);
ALTER SUBSCRIPTION
bar=# drop subscription sub1;
DROP SUBSCRIPTION

Should we add a hint for 'could not drop the replication slot' message?

--
Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
<http://www.timbira.com.br>