Re: Get stuck when dropping a subscription during synchronizing table

On 6/22/17 03:26, Masahiko Sawada wrote:
> Thank you for the patch. Some comment and question.
> DropSubscriptionRelState requests ExclusiveLock but why is it not
> ShareRowExclusiveLock?

fixed

> I test DROP SUBSCIPTION case but even with this patch, "tuple
> concurrently updated" is still occurred.
>
> @@ -405,7 +405,7 @@ RemoveSubscriptionRel(Oid subid, Oid relid)
> }
> heap_endscan(scan);
>
> - heap_close(rel, RowExclusiveLock);
> + heap_close(rel, lockmode);
> }
>
> I think we should not release the table lock here, should be
> heap_close(rel, NoLock) instead? After changed it so on my
> environment, DROP SUBSCRIPTION seems to work fine.

fixed

> Also, ALTER SUBSCRIPTION SET PUBLICATION seems the same. Even with
> Petr's patch, the same error still occurred during ALTER SUBSCRIPTION
> SET PUBLICATION. Currently it acquires RowExclusiveLock on
> pg_subscription_rel but as long as both DDL and table sync worker
> could modify the same record on pg_subscription this error can
> happen.

fixed

> On the other hand if we take a strong lock on pg_subscription
> during DDL the deadlock risk will be increased.

The original problem was that DROP TABLE locked things in the order 1)
user table, 2) pg_subscription_rel, whereas a full-database VACUUM would
lock things in the opposite order. I think this was a pretty wide
window if you have many tables. In this case, however, we are only
dealing with pg_subscription and pg_subscription_rel, and I think even
VACUUM would always processes them in the same order.

Could please try the attached patch so see if it addresses your test
scenarios?

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