Re: locking [user] catalog tables vs 2pc vs logical rep - Mailing list pgsql-hackers

On Mon, Jun 7, 2021 at 4:18 AM osumi.takamichi@fujitsu.com
<osumi.takamichi@fujitsu.com> wrote:
>
> On Thursday, June 3, 2021 7:07 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > On Thu, Jun 3, 2021 at 9:18 AM osumi.takamichi@fujitsu.com
> > <osumi.takamichi@fujitsu.com> wrote:
> > > Thank you for providing the patch.
> > > I have updated your patch to include some other viewpoints.
> > >
> >
> > I suggest creating a synchronous replication part of the patch for
> > back-branches as well.
> You are right. Please have a look at the attached patch-set.
> Needless to say, the patch for HEAD has descriptions that depend on
> the 2pc patch-set.
>

1)
+     <para>
+       The use of any command to take an ACCESS EXCLUSIVE lock on
[user] catalog tables
+       can cause the deadlock of logical decoding in synchronous
mode. This means that
+       at the transaction commit or prepared transaction, the command
hangs or the server
+       becomes to block any new connections. To avoid this, users
must refrain from such
+       operations.
+     </para>

Can we change it something like:
Logical decoding of transactions in synchronous replication mode
requires access to system tables and/or user catalog tables, hence
user should refrain from taking exclusive lock on system tables and/or
user catalog tables or refrain from executing commands like cluster
command which will take exclusive lock on system tables internally. If
not the transaction will get blocked at commit/prepare time because of
a deadlock.

2) I was not sure if we should include the examples below or the above
para is enough, we can hear from others and retain it if required:
+     <para>
+       When <command>COMMIT</command> is conducted for a transaction that has
+       issued explicit <command>LOCK</command> on
<structname>pg_class</structname>
+       with logical decoding, the deadlock occurs. Also, committing
one that runs
+       <command>CLUSTER</command> <structname>pg_class</structname> is another
+       deadlock scenario.
+     </para>
+
+     <para>
+       Similarly, executing <command>PREPARE TRANSACTION</command>
+       after <command>LOCK</command> command on
<structname>pg_class</structname> and
+       logical decoding of published table within the same
transaction leads to the deadlock.
+       Clustering <structname>pg_trigger</structname> by
<command>CLUSTER</command> command
+       brings about the deadlock as well, when published table has a
trigger and any operations
+       that will be decoded are conducted on the same table.
+     </para>
+
+     <para>
+       The deadlock can happen when users execute <command>TRUNCATE</command>
+       on user_catalog_table under the condition that output plugin
have reference to it.
      </para>

Regards,
Vignesh