Re: Logical Replication vs. 2PC

On Thu, Mar 18, 2021 at 3:16 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> While reviewing/testing subscriber-side work for $SUBJECT [1], I
> noticed a problem that seems to need a broader discussion, so started
> this thread. We can get prepare for the same GID more than once for
> the cases where we have defined multiple subscriptions for
> publications on the same server and prepared transaction has
> operations on tables subscribed to those subscriptions. For such
> cases, one of the prepare will be successful and others will fail in
> which case the server will send them again. Once the commit prepared
> is done for the first one, the next prepare will be successful. Now,
> this is not ideal but will work.
>
> However, if the user has setup synchronous_standby_names for all the
> subscriptions then we won't be able to proceed because the prepare on
> publisher will wait for all the subscriptions to ack and the
> subscriptions are waiting for the first prepare to finish. See an
> example below for such a situation. I think this can also happen if we
> get any key violation while applying the changes on the subscriber,
> but for that, we can ask the user to remove the violating key on the
> subscriber as that is what we suggest now also for commits. Similarly,
> say the user has already prepared the transaction with the same GID on
> subscriber-node, then also we can get into a similar situation but for
> that, we can ask the user to commit such a GID.
>
> We can think of appending some unique identifier (like subid) with GID
> but that won't work for cascaded standby setup (where the prepares on
> subscriber will be again sent to another subscriber) as the GID can
> become too long. So that might not be a good solution, maybe we can
> optimize it in some way that we append only when there is a GID clash.
> The other thing we could do is to ask the user to temporarily disable
> the subscription and change synchronous_standby_settings on the
> publisher node. Any better ideas?
>
> 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.
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.

Regards,
Vignesh