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

On 26 May 2021, at 05:04, Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> 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.
>


IIRC this was discussed the first time 2PC decoding was proposed and everybody seemed fine with the limitation so I'd
votefor just documenting it, same way as the sync rep issue. 

If you'd prefer fixing it by blocking something, wouldn't it make more sense to simply not allow PREPARE if one of
theseoperations was executed, similarly to what we do with temp table access? 

--
Petr