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

On Wed, May 26, 2021 at 1:53 PM Petr Jelinek
<petr(dot)jelinek(at)enterprisedb(dot)com> wrote:
>
> On 26 May 2021, at 05:04, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > On Tue, May 25, 2021 at 12:40 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> >>
> >> 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 vote for just documenting it, same way as the sync rep issue.
>

+1.

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

The point was that even if somehow we block for prepare, there doesn't
seem to be a simple way for synchronous logical replication which can
also have similar problems. So, I would prefer to document it and we
can even think to backpatch the sync rep related documentation.
Michael seems to be a bit interested in dealing with some of the 2PC
issues due to reasons different than logical replication which I am
not completely sure is a good idea and Tom also feels that is not a
good idea.

--
With Regards,
Amit Kapila.