Re: Skipping logical replication transactions on subscriber side - Mailing list pgsql-hackers

On Thu, May 27, 2021 at 9:56 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Wed, May 26, 2021 at 3:43 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> > On Tue, May 25, 2021 at 12:26 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > >
> > > On Mon, May 24, 2021 at 7:51 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > > >
> > > > On Mon, May 24, 2021 at 1:32 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > > >
> > > > I think you need to consider few more things here:
> > > > (a) Say the error occurs after applying some part of changes, then
> > > > just skipping the remaining part won't be sufficient, we probably need
> > > > to someway rollback the applied changes (by rolling back the
> > > > transaction or in some other way).
> > >
> > > After more thought, it might be better to that setting and resetting
> > > the XID to skip requires disabling the subscription.
> > >
> >
> > It might be better if it doesn't require disabling the subscription
> > because it would be more steps for the user to disable/enable it. It
> > is not clear to me what exactly you want to gain by disabling the
> > subscription in this case.
>
> The situation I’m considered is where the user specifies the XID while
> the worker is applying the changes of the transaction with that XID.
> In this case, I think we need to somehow rollback the changes applied
> so far. Perhaps we can either rollback the transaction and ignore the
> remaining changes or restart and ignore the entire transaction from
> the beginning.
>

If we follow your suggestion of only allowing XIDs that have been
known to have conflicts then probably we don't need to worry about
rollbacks.

> > > >
> > > > > For (2), what I'm thinking is to add a new action to ALTER
> > > > > SUBSCRIPTION command like ALTER SUBSCRIPTION test_sub SET SKIP
> > > > > TRANSACTION 590. Also, we can have actions to reset it; ALTER
> > > > > SUBSCRIPTION test_sub RESET SKIP TRANSACTION. Those commands add the
> > > > > XID to a new column of pg_subscription or a new catalog, having the
> > > > > worker reread its subscription information. Once the worker skipped
> > > > > the specified transaction, it resets the transaction to skip on the
> > > > > catalog.
> > > > >
> > > >
> > > > What if we fail while updating the reset information in the catalog?
> > > > Will it be the responsibility of the user to reset such a transaction
> > > > or we will retry it after restart of worker?  Now, say, we give such a
> > > > responsibility to the user and the user forgets to reset it then there
> > > > is a possibility that after wraparound we will again skip the
> > > > transaction which is not intended. And, if we want to retry it after
> > > > restart of worker, how will the worker remember the previous failure?
> > >
> > > As described above, setting and resetting XID to skip is implemented
> > > as a normal system catalog change, so it's crash-safe and persisted. I
> > > think that the worker can either removes the XID or mark it as done
> > > once it skipped the specified transaction so that it won't skip the
> > > same XID again after wraparound.
> > >
> >
> > It all depends on when exactly you want to update the catalog
> > information. Say after skipping commit of the XID, we do update the
> > corresponding LSN to be communicated as already processed to the
> > subscriber and then get the error while updating the catalog
> > information then next time we might not know whether to update the
> > catalog for skipped XID.
> >
> > > Also, it might be better if we reset
> > > the XID also when a subscription field such as subconninfo is changed
> > > because it could imply the worker will connect to another publisher
> > > having a different XID space.
> > >
> > > We also need to handle the cases where the user specifies an old XID
> > > or XID whose transaction is already prepared on the subscriber. I
> > > think the worker can reset the XID with a warning when it finds out
> > > that the XID seems no longer valid or it cannot skip the specified
> > > XID. For example in the former case, it can do that when the first
> > > received transaction’s XID is newer than the specified XID.
> > >
> >
> > But how can we guarantee that older XID can't be received later? Is
> > there a guarantee that we receive the transactions on subscriber in
> > XID order.
>
> Considering the above two comments, it might be better to provide a
> way to skip the transaction that is already known to be conflicted
> rather than allowing users to specify the arbitrary XID.
>

Okay, that makes sense but still not sure how will you identify if we
need to reset XID in case of failure doing that in the previous
attempt. Also, I am thinking that instead of a stat view, do we need
to consider having a system table (pg_replication_conflicts or
something like that) for this because what if stats information is
lost (say either due to crash or due to udp packet loss), can we rely
on stats view for this?

--
With Regards,
Amit Kapila.