Re: Logical Replication vs. 2PC

On Thu, Mar 18, 2021 at 5:31 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Thu, Mar 18, 2021 at 3:16 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> >
> > In short, on the subscriber, both the apply workers (corresponding to
> > two subscriptions) are getting the same prepare transaction GID,
> > leading to an error on the subscriber and making the publisher wait
> > forever.
> >
> > Thoughts?
>
> I see the main problem here is because the GID clashes as you have
> rightly pointed out. I'm not sure if we are allowed to change the
> GID's in the subscriber.
> If we are allowed to change the GID's in the subscriber. Worker can do
> something like: When the apply worker is applying the prepared
> transaction, try to apply the prepare transaction with the GID as is.
> If there is an error GID already in use, workers can try to catch that
> error and change the GID to a fixed length hash key of (GID,
> subscription name, node name, timestamp,etc) to generate a unique hash
> key(modified GID), prepare the transaction with the generated hash
> key. Store this key and the original GID for later use, this will be
> required during commit prepared or in case of rollback prepared. When
> applying the commit prepared or rollback prepared, change the GID with
> the hash key that was used during the prepare transaction.
>

I think it will be tricky to distinguish the clash is due to the user
has already prepared a xact with the same GID on a subscriber or it is
from one of the apply workers. For earlier cases, the user needs to
take action. You need to change both file format and WAL for this and
not sure but generating hash key for this looks a bit shaky. Now, we
might be able to make it work but how about if we always append subid
with GID for prepare and store GID and subid separately in WAL (I
think we can store additional subscriber-id information
conditionally). Then during recovery, we will use both GID and subid
for prepare but for decoding, we will only use GID. This way for
cascaded set up we can always send GID by reading WAL and the
downstream subscriber will append its subid to GID. I know this is
also not that straight-forward but I don't have any better ideas at
the moment.

> If we are not allowed to change the GID's in the subscriber. This
> thought is in similar lines where in one of the earlier design
> prepared spool files was used. Can we have some mechanism where we can
> identify this scenario and store the failing prepare transaction
> information, so that when the worker is restarted worker can use this
> stored information to identify the failed prepare transaction, once
> worker identifies that it is a failed prepare transaction then all of
> this transaction can be serialized into a file and later when the
> apply worker receives a commit prepared it can get the changes from
> the file and apply this transaction or discard the file in case of
> rollback prepared.
>

Hmm, this idea will face similar problems as described here [1].

Note: added Petr Jelinek to see if he has any opinion on this matter.

[1] - https://www.postgresql.org/message-id/CAA4eK1LVEdPYnjdajYzu3k6KEii1%2BF0jdQ6sWnYugiHcSGZD6Q%40mail.gmail.com

--
With Regards,
Amit Kapila.