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

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

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.