Re: DROP SUBSCRIPTION with no slot

Hi,

On 25/09/2019 01:07, Jeff Janes wrote:
> On Tue, Sep 24, 2019 at 5:25 PM Peter Eisentraut
> <peter(dot)eisentraut(at)2ndquadrant(dot)com
> <mailto:peter(dot)eisentraut(at)2ndquadrant(dot)com>> wrote:
>
> On 2019-09-24 16:31, Jeff Janes wrote:
> > I recently had to cut loose (pg_drop_replication_slot) a logical
> replica
> > that couldn't keep up and so was threatening to bring down the
> master.
> >
> > In mopping up on the replica side, I couldn't just drop the
> > subscription, because it couldn't drop the nonexistent slot on the
> > master and so refused to work.  So I had to do a silly little dance
> > where I first disable the subscription, then ALTER SUBSCRIPTION
> ... SET
> > (slot_name = NONE), then drop it.
> >
> > Wanting to clean up after itself is admirable, but if there is
> nothing
> > to clean up, why should that be an error condition?
>
> The alternatives seem quite error prone to me.  Better be explicit.
>
>
> If you can connect to the master, and see that the slot already fails to
> exist, what is error prone about that?
>
> If someone goes to the effort of setting up a different master,
> configures it to receive replica connections, and alters the
> subscription CONNECTION parameter on the replica to point to that
> poisoned master, will an error on the DROP SUBSCRIPTION really force
> them to see the error of their ways, or will they just succeed at
> explicitly doing the silly dance and finalize the process of shooting
> themselves in the foot via a roundabout mechanism?

All that needs to happen to get into this situation is to have
replication go through haproxy or some other loadbalancer or dns name
that points to different server after failover. So user really does not
have to touch the subscription

We should at least offer HINT though.

However, I'd be in favor of removing this restriction once the patch
which limits how much wal a slot can retain gets in.

--
Petr Jelinek
2ndQuadrant - PostgreSQL Solutions for the Enterprise
https://www.2ndQuadrant.com/