On 10/01/17 14:52, Peter Eisentraut wrote:
> On 1/2/17 8:32 AM, Petr Jelinek wrote:
>> On 02/01/17 05:23, Steve Singer wrote:
>>> but I can't drop the subscription either
>>>
>>>
>>> test_b=# drop subscription mysub;
>>> ERROR: could not drop replication origin with OID 1, in use by PID 24996
>>>
>>> alter subscription mysub disable;
>>> ALTER SUBSCRIPTION
>>> drop subscription mysub;
>>> ERROR: could not drop replication origin with OID 1, in use by PID 24996
>>>
>>> drop subscription mysub nodrop slot;
>>>
>>> doesn't work either. If I first drop the working/active subscription on
>>> the original 'test' database it works but I can't seem to drop the
>>> subscription record on test_b
>
> I can't reproduce this exactly, but I notice that CREATE SUBSCRIPTION
> NOCREATE SLOT does not create a replication origin, but DROP
> SUBSCRIPTION NODROP SLOT does attempt to drop the origin. If the origin
> is not in use, it will just go away, but if it is in use, it might lead
> to the situation described above, where the second subscription cannot
> be removed.
This is thinko in it's own regard, origin needs to be created regardless
of the slot.
>
>> I guess this is because replication origins are pg instance global and
>> we use subscription name for origin name internally. Maybe we need to
>> prefix/suffix it with db oid or something like that, but that's
>> problematic a bit as well as they both have same length limit. I guess
>> we could use subscription OID as replication origin name which is
>> somewhat less user friendly in terms of debugging but would be unique.
>
> I think the most robust way would be to associate origins to
> subscriptions using the object dependency mechanism, and just pick an
> internal name like we do for automatically created indexes or sequences,
> for example.
>
That will not help, issue is that we consider names for origins to be
unique across cluster while subscription names are per database so if
there is origin per subscription (which there has to be) it will always
clash if we just use the name. I already have locally changed this to
pg_<subscription_oid> naming scheme and it works fine.
-- Petr Jelinek http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training &
Services