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

On Fri, Sep 3, 2021 at 2:15 AM Mark Dilger <mark.dilger@enterprisedb.com> wrote:
>
> > On Aug 30, 2021, at 12:06 AM, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > I've attached rebased patches.
> For the v12-0003 patch:
>
> I believe this feature is needed, but it also seems like a very powerful foot-gun.  Can we do anything to make it
lesslikely that users will hurt themselves with this tool? 
>

This won't do any more harm than currently, users can do via
pg_replication_slot_advance and the same is documented as well, see
[1]. This will be allowed to only superusers. Its effect will be
documented with a precautionary note to use it only when the other
available ways can't be used. Any better ideas?

> I am thinking back to support calls I have attended.  When a production system is down, there is often some hesitancy
toperform ad-hoc operations on the database, but once the decision has been made to do so, people try to get the whole
processdone as quickly as possible.  If multiple transactions on the publisher fail on the subscriber, they will do so
inseries, not in parallel. 
>

The subscriber will know only one transaction failure at a time, till
that is resolved, the apply won't move ahead and it won't know even if
there are other transactions that are going to fail in the future.

>
> If the user could instead clear all failed transactions of the same type, that might make it less likely that they
unthinkinglyalso skip subsequent errors of some different type.  Perhaps something like ALTER SUBSCRIPTION ... SET
(skip_failures= 'duplicate key value violates unique constraint "test_pkey"')? 
>

I think if we want we can allow to skip particular error via
skip_error_code instead of via error message but not sure if it would
be better to skip a particular operation of the transaction rather
than the entire transaction. Normally from the atomicity purpose the
transaction can be either committed or rolled-back but not partially
done so I think it would be preferable to skip the entire transaction
rather than skipping it partially.

>  This is arguably a different feature request, and not something your patch is required to address, but I wonder how
muchwe should limit people shooting themselves in the foot?  If we built something like this using your skip_xid
feature,rather than instead of your skip_xid feature, would your feature need to be modified? 
>

Sawada-San can answer better but I don't see any problem building any
such feature on top of what is currently proposed.

>
> I'm having trouble thinking of an example conflict where skipping a transaction would be better than writing a BEFORE
INSERTtrigger on the conflicting table which suppresses or redirects conflicting rows somewhere else.  Particularly for
largertransactions containing multiple statements, suppressing the conflicting rows using a trigger would be less messy
thanskipping the transaction.  I think your patch adds a useful tool to the toolkit, but maybe we should mention more
alternativesin the docs?  Something like, "changing the data on the subscriber so that it doesn't conflict with
incomingchanges, or dropping the conflicting constraint or unique index, or writing a trigger on the subscriber to
suppressor redirect conflicting incoming changes, or as a last resort, by skipping the whole transaction"? 
>

+1 for extending the docs as per this suggestion.

--
With Regards,
Amit Kapila.