Re: Somebody has not thought through subscription locking considerations

On 31/03/17 19:35, Tom Lane wrote:
> Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> writes:
>> On Fri, Mar 31, 2017 at 9:53 AM, Petr Jelinek
>> <petr(dot)jelinek(at)2ndquadrant(dot)com> wrote:
>>> On 30/03/17 07:25, Tom Lane wrote:
>>>> I await with interest an explanation of what "VACUUM FULL pg_class" is
>>>> doing trying to acquire ShareRowExclusiveLock on pg_subscription_rel, not
>>>> to mention why a DROP SEQUENCE is holding some fairly strong lock on that
>>>> relation.
>
>> VACUUM FULL of any table acquires ShareRowExclusiveLock on
>> pg_subscription_rel because when doDeletion removes old heap the
>> RemoveSubscriptionRel is called in heap_drop_with_catalog.
>
> This seems entirely horrid: it *guarantees* deadlock possibilities.
> And I wonder what happens when I VACUUM FULL pg_subscription_rel
> itself.
>
> At the very least, it would be a good idea to exclude the system
> catalogs from logical replication, wouldn't it?
>

They are excluded. It works same way for triggers and many other objects
so I would not say it's horrid.

The problematic part is that the pg_subscription_rel manipulation
functions acquire ShareRowExclusiveLock and not the usual
RowExclusiveLock because there were some worries about concurrency. I
think though that it's not needed though given the access patterns
there. It's only updated by CREATE SUBSCRIPTION/ALTER SUBSCRIPTION
REFRESH and then by tablesync which holds exclusive lock on the table
itself anyway.

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services