Re: Skipping logical replication transactions on subscriber side

On Tue, Dec 14, 2021 at 11:40 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Fri, Dec 3, 2021 at 12:12 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >
> > Skipping a whole transaction by specifying xid would be a good start.
> > Ideally, we'd like to automatically skip only operations within the
> > transaction that fail but it seems not easy to achieve. If we allow
> > specifying operations and/or relations, probably multiple operations
> > or relations need to be specified in some cases. Otherwise, the
> > subscriber cannot continue logical replication if the transaction has
> > multiple operations on different relations that fail. But similar to
> > the idea of specifying multiple xids, we need to note the fact that
> > user wouldn't know of the second operation failure unless the apply
> > worker applies the change. So I'm not sure there are many use cases in
> > practice where users can specify multiple operations and relations in
> > order to skip applies that fail.
>
> I think there would be use cases for specifying the relations or
> operation, e.g. if the user finds an issue in inserting in a
> particular relation then maybe based on some manual investigation he
> founds that the table has some constraint due to that it is failing on
> the subscriber side but on the publisher side that constraint is not
> there so maybe the user is okay to skip the changes for this table and
> not for other tables, or there might be a few more tables which are
> designed based on the same principle and can have similar error so
> isn't it good to provide an option to give the list of all such
> tables.
>

That's right and I agree there could be some use case for it and even
specifying the operation but I think we can always extend the existing
feature for it if the need arises. Note that the user can anyway only
specify a single relation or an operation because there is a way to
know only one error and till that is resolved, the apply process won't
proceed. We have discussed providing these additional options in this
thread but thought of doing it later once we have the base feature and
based on the feedback from users.

--
With Regards,
Amit Kapila.