Re: DROP SUBSCRIPTION hangs if sub is disabled in the same transaction

On 9/4/17 10:41, Arseny Sher wrote:
> node 2:
> create table t (i int);
> create subscription s CONNECTION 'port=5432' publication p;
> begin;
> alter subscription s disable ;
> alter subscription s set (slot_name = none);
> drop subscription s;
> end;
>
> It hangs in replorigin_drop because we wait until ReplicationState is
> released. This should happen on exit of worker, but worker will not exit
> until transaction commit because he doesn't see that the sub was
> disabled.

I think we are whacking things around a in circle now. First we moved
the worker killing to the end of the transaction to make subscription
DDL transaction-capable. Then we changed replication origin dropping to
wait until the worker detaches. If you do both of these things at once,
you get this circular dependency.

We can break this in any number of ways:

- (your patch) Kill workers right away after ALTER SUBSCRIPTION DISABLE,
thus breaking the appearance of transactional DDL somewhat.

- Revert to the old behavior that the replication origin dropping fails
if it is in use. Then you would get an error instead of hanging. But
that was previously also reported as a bug.

- Disallow DROP SUBSCRIPTION in a transaction under certain
circumstances, for example if a transaction has previously manipulated
the same subscription.

- Have DROP SUBSCRIPTION attempt to kill workers if the subscription is
disabled (and possibly, was changed in the same transaction), which
would address this scenario very narrowly.

Maybe there are more ideas. But I think we have to pick a poison.

Thoughts?

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services