RE: locking [user] catalog tables vs 2pc vs logical rep

On Tuesday, June 1, 2021 4:33 PM Peter Smith <smithpb2250(at)gmail(dot)com>
> To: Andres Freund <andres(at)anarazel(dot)de>
> Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>; Amit Kapila
> <amit(dot)kapila16(at)gmail(dot)com>; Markus Wanner
> <markus(dot)wanner(at)enterprisedb(dot)com>
> Subject: Re: locking [user] catalog tables vs 2pc vs logical rep
>
> Hi.
>
> The attached PG docs patch about catalog deadlocks was previously
> implemented in another thread [1], but it seems more relevant to this one.
>
> PSA.
Thank you for providing the patch.
I have updated your patch to include some other viewpoints.

For example, CLUSTER command scenario
that also causes hang of PREPARE in synchronous mode.
We get this deadlock, using the 2PC patch-set.

FYI, the scenario is
(1) create a table with a trigger
(2) create pub and sub in synchronous mode
(3) then, execute CLUSTER pg_trigger USING pg_trigger_oid_index,
and do some operations (e.g. INSERT) on the trigger-attached table and PREPARE

The mechanism of this is
walsender tries to take a lock on pg_trigger if the table has a trigger,
but, pg_trigger is already locked by the CLUSTER command, which leads to the deadlock.
Then, this scenario requires some operations on the table which has trigger
because it invokes the walsender to take the lock described above.

I also included the description about TRUNCATE on user_catalog_table
in the patch. Please have a look at this patch.

Best Regards,
Takamichi Osumi