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

On Mon, Jun 7, 2021 at 4:18 AM osumi(dot)takamichi(at)fujitsu(dot)com
<osumi(dot)takamichi(at)fujitsu(dot)com> wrote:
>
> On Thursday, June 3, 2021 7:07 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > On Thu, Jun 3, 2021 at 9:18 AM osumi(dot)takamichi(at)fujitsu(dot)com
> > <osumi(dot)takamichi(at)fujitsu(dot)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