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

On Mon, Jun 7, 2021 at 9:26 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> 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.
>

I think this is better than what the patch has proposed. I suggest
minor modifications to your proposed changes. Let's write the above
para as: "In synchronous replication setup, a deadlock can happen, if
the transaction has locked [user] catalog tables exclusively. This is
because logical decoding of transactions can lock catalog tables to
access them. To avoid this users must refrain from taking an exclusive
lock on [user] catalog tables. This can happen in the following ways:"

+ <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>

The above points need to be mentioned in the <itemizedlist> fashion.
See <sect2 id="continuous-archiving-caveats"> for an example. I think
the above point can be split as follows.

<listitem>
<para>
User has issued an explicit <command>LOCK</command> on
<structname>pg_class</structname> (or any other catalog table) in a
transaction. Now when we try to decode such a transaction, a deadlock
can happen.
</para>
</listitem>

Similarly, write separate points for Cluster and Truncate.

One more comment is that for HEAD, first just create a patch with
synchronous replication-related doc changes and then write a separate
patch for prepared transactions.

> 2) I was not sure if we should include the examples below or the above
> para is enough,
>

It is better to give examples but let's use the format as I suggested above.

--
With Regards,
Amit Kapila.