Re: Optionally automatically disable logical replication subscriptions on error

> On Jun 19, 2021, at 7:44 AM, Mark Dilger <mark.dilger@enterprisedb.com> wrote:
>
> Wouldn't the user rather skip just the problematic rows?  I understand that on the subscriber side it is difficult to
doso, but if you are going to implement this sort of thing, it makes more sense to allow the user to filter out data
thatis problematic rather than filtering out xids that are problematic, and the filter shouldn't just be an in-or-out
filter,but rather a mapping function that can redirect the data someplace else or rewrite it before inserting or change
thepre-existing conflicting data prior to applying the problematic data or whatever. 

Thinking about this some more, it seems my patch already sets the stage for this sort of thing.

We could extend the concept of triggers to something like ErrorTriggers that could be associated with subscriptions.  I
alreadyhave the code catching errors for subscriptions where disable_on_error is true.  We could use that same code
pathfor subscriptions that have one or more BEFORE or AFTER ErrorTriggers defined.  We could pass the trigger all the
errorcontext information along with the row and subscription information, and allow the trigger to either modify the
databeing replicated or make modifications to the table being changed.  I think having support for both BEFORE and
AFTERwould be important, as a common design pattern might be to move aside the conflicting rows in the BEFORE trigger,
thenreconcile and merge them back into the table in the AFTER trigger.  If the xid still cannot be replicated after one
attemptusing the triggers, the second attempt to disable the subscription instead. 

There are a lot of details to consider, but to my mind this idea is much more user friendly than the idea that users
shouldmuck about with xids for arbitrarily many conflicting transactions. 

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company