Re: Logical Replication WIP

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.

> 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.

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