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

On Tue, May 25, 2021 at 12:40 PM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Mon, May 24, 2021 at 10:03:01AM +0530, Amit Kapila wrote:
> > 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?
>
> It seems to me that the 2PC issues on catalog tables and the issues
> related to logical replication in synchonous mode are two distinct
> things that need to be fixed separately.
>

Fair enough. But the way we were looking at them as they will also
block (lead to deadlock) for logical replication of prepared
transactions and also logical replication in synchonous mode without
prepared transactions. Now, if we want to deal with the 2PC issues
separately that should be fine as well. However, for that we need to
see which all operations we want to block on [user]_catalog_tables.
The first one is lock command, then there are other operations like
Cluster which take exclusive lock on system catalog tables and we
allow them to be part of prepared transactions (example Cluster
pg_trigger using pg_trigger_oid_index;), another kind of operation is
Truncate on user_catalog_tables. Now, some of these might not allow
connecting after restart so we might need to think whether we want to
prohibit all such operations or only some of them.

>
> The second issue with logical replication is still disruptive, but it
> looks to me more like a don't-do-it issue, and documenting the caveats
> sounds fine enough.
>

Right, that is what I also think.

-- 
With Regards,
Amit Kapila.