Re: [HACKERS] logical decoding of two-phase transactions

On Tue, Dec 15, 2020 at 11:42 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Mon, Dec 14, 2020 at 2:59 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
>
> Today, I looked at one of the issues discussed earlier in this thread
> [1] which is that decoding can block (or deadlock can happen) when the
> user explicitly locks the catalog relation (like Lock pg_class) or
> perform Cluster on non-relmapped catalog relations (like Cluster
> pg_trigger using pg_class_oid_index; and the user_table on which we
> have performed any operation has a trigger) in the prepared xact. As
> discussed previously, we don't have a problem when user tables are
> exclusively locked because during decoding we don't acquire any lock
> on those and in fact, we have a test case for the same in the patch.
>
Yes, and as described in that mail, the current code explicitly denies
preparation of a 2PC transaction.
under some circumstances:

postgres=# BEGIN;
postgres=# CLUSTER pg_class using pg_class_oid_index ;
postgres=# PREPARE TRANSACTION 'test_prepared_lock';
ERROR: cannot PREPARE a transaction that modified relation mapping

> In the previous discussion, most people seem to be of opinion that we
> should document it in a category "don't do that", or prohibit to
> prepare transactions that lock system tables in the exclusive mode as
> any way that can block the entire system. The other possibility could
> be that the plugin can allow enabling lock_timeout when it wants to
> allow decoding of two-phase xacts and if the timeout occurs it tries
> to fetch by disabling two-phase option provided by the patch.
>
> I think it is better to document this as there is no realistic
> scenario where it can happen. I also think separately (not as part of
> this patch) we can investigate whether it is a good idea to prohibit
> prepare for transactions that acquire exclusive locks on catalog
> relations.
>
> Thoughts?

I agree with the documentation option. If we choose to disable
two-phase on timeout, we still need to decide what to
do with already prepared transactions.

regards,
Ajin Cherian
Fujitsu Australia