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

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.

> This would not be
> a restriction for users since logical replication is likely to already
> stop (and possibly repeating restarting and stopping) due to an error.
> Setting and resetting the XID modifies the system catalog so it's a
> crash-safe change and survives beyond the server restarts. When a
> logical replication worker starts, it checks the XID. If the worker
> receives changes associated with the transaction with the specified
> XID, it can ignore the entire transaction.
>
> > (b) How do you handle streamed transactions? It is possible that some
> > of the streams are successful and the error occurs after that, say
> > when writing to the stream file. Now, would you skip writing to stream
> > file or will you write it, and then during apply, you will skip the
> > entire transaction and remove the corresponding stream file.
>
> I think streamed transactions can be handled in the same way described in (a).
>
> > (c) There is also a possibility that the error occurs while applying
> > the changes of some subtransaction (this is only possible for
> > streaming xacts), so, in such cases, do we allow users to rollback the
> > subtransaction or user has to rollback the entire transaction. I am
> > not sure but maybe for very large transactions users might just want
> > to rollback the subtransaction.
>
> If the user specifies XID of a subtransaction, it would be better to
> skip only the subtransaction. If specifies top transaction XID, it
> would be better to skip the entire transaction. What do you think?
>

makes sense.

> > (d) How about prepared transactions? Do we need to rollback the
> > prepared transaction if user decides to skip such a transaction? We
> > already allow prepared transactions to be streamed to plugins and the
> > work for subscriber-side apply is in progress [1], so I think we need
> > to consider this case as well.
>
> If a transaction replicated from the subscriber could be prepared on
> the subscriber, it would be guaranteed to be able to be either
> committed or rolled back. Given that this feature is to skip a problem
> transaction, I think it should not do anything for transactions that
> are already prepared on the subscriber.
>

makes sense, but I think we need to reset the XID in such a case.

> > (e) Do we want to provide such a feature via output plugins as well,
> > if not, why?
>
> You mean to specify an XID to skip on the publisher side? Since I've
> been considering this feature as a way to resume the logical
> replication having a problem I've not thought of that idea but It
> would be a good idea. Do you have any use cases?
>

No. On again thinking about this, I think we can leave this for now.

> If we specified the
> XID on the publisher, multiple subscribers would skip that
> transaction.
>
> >
> > > 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.

--
With Regards,
Amit Kapila.