Horton

Re: Skipping logical replication transactions on subscriber side

Amit Kapila <amit.kapila16@gmail.com>

View thread View on postgr.es
From: Amit Kapila <amit.kapila16@gmail.com>
To: Dilip Kumar <dilipbalaut@gmail.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>, vignesh C <vignesh21@gmail.com>, Greg Nancarrow <gregn4422@gmail.com>, "tanghy.fnst@fujitsu.com" <tanghy.fnst@fujitsu.com>, "osumi.takamichi@fujitsu.com" <osumi.takamichi@fujitsu.com>, Alexey Lesovsky <lesovsky@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2021-12-15T04:16:20Z
Lists: pgsql-hackers 
On Tue, Dec 14, 2021 at 11:40 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> On Fri, Dec 3, 2021 at 12:12 PM Masahiko Sawada <sawada.mshk@gmail.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.

Commits

  1. Test ALIGNOF_DOUBLE==4 compatibility under ALIGNOF_DOUBLE==8.

  2. Reorder subskiplsn in pg_subscription to avoid alignment issues.

  3. Add ALTER SUBSCRIPTION ... SKIP.

  4. Optionally disable subscriptions on error.

  5. Update docs of logical replication for commit 8d74fc96db.

  6. Respect permissions within logical replication.

  7. Fix regression test failure caused by commit 8d74fc96db.

  8. Add a view to show the stats of subscription workers.

  9. Add logical change details to logical replication worker errcontext.

  10. Rename LOGICAL_REP_MSG_STREAM_END to LOGICAL_REP_MSG_STREAM_STOP.

  11. Fix typo in protocol.sgml.

  12. Remove unused argument in apply_handle_commit_internal().

  13. Fix replication of in-progress transactions in tablesync worker.

  14. Reorder pg_sequence columns to avoid alignment issue