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

On Tue, Apr 20, 2021 at 9:57 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> This similar problem exists in case of synchronous replication setup
> having synchronous_standby_names referring to the subscriber, when we
> do the steps "begin;lock pg_class; insert into test1 values(10);
> commit". In this case while decoding of commit, the commit will wait
> while trying to acquire a lock on pg_class relation,
>

So, this appears to be an existing caveat of synchronous replication.
If that is the case, I am not sure if it is a good idea to just block
such ops for the prepared transaction. Also, what about other
operations which acquire an exclusive lock on [user]_catalog_tables
like:
cluster pg_trigger using pg_class_oid_index, similarly cluster on any
user_catalog_table, then the other problematic operation could
truncate of user_catalog_table as is discussed in another thread [1].
I think all such operations can block even with synchronous
replication. I am not sure if we can create examples for all cases
because for ex. we don't have use of user_catalog_tables in-core but
maybe for others, we can try to create examples and see what happens?

If all such operations can block for synchronous replication and
prepared transactions replication then we might want to document them
as caveats at page:
https://www.postgresql.org/docs/devel/logicaldecoding-synchronous.html
and then also give the reference for these caveats at prepared
transactions page:https://www.postgresql.org/docs/devel/logicaldecoding-two-phase-commits.html

What do you think?

As this appears to be an existing caveat of logical replication, I
have added the Petr and Peter E in this email.

[1] - https://www.postgresql.org/message-
id/OSBPR01MB4888314C70DA6B112E32DD6AED2B9%40OSBPR01MB4888.jpnprd01.prod.outlook.com

--
With Regards,
Amit Kapila.