On Fri, Sep 3, 2021 at 2:15 AM Mark Dilger <mark.dil...@enterprisedb.com> wrote: > > > On Aug 30, 2021, at 12:06 AM, Masahiko Sawada <sawada.m...@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 less likely 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 to perform ad-hoc operations on > the database, but once the decision has been made to do so, people try to get > the whole process done as quickly as possible. If multiple transactions on > the publisher fail on the subscriber, they will do so in series, 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 unthinkingly also 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 much we 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 INSERT trigger on the > conflicting table which suppresses or redirects conflicting rows somewhere > else. Particularly for larger transactions containing multiple statements, > suppressing the conflicting rows using a trigger would be less messy than > skipping the transaction. I think your patch adds a useful tool to the > toolkit, but maybe we should mention more alternatives in the docs? > Something like, "changing the data on the subscriber so that it doesn't > conflict with incoming changes, or dropping the conflicting constraint or > unique index, or writing a trigger on the subscriber to suppress or 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.