Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.6\))
Subject: Re: Optionally automatically disable logical replication
 subscriptions on error
From: Mark Dilger <mark.dilger@enterprisedb.com>
In-Reply-To: <DE7E13B7-DC76-416A-A98F-3BC3F80E6BE9@enterprisedb.com>
Date: Sat, 19 Jun 2021 09:21:56 -0700
Cc: Sawada Masahiko <sawada.mshk@gmail.com>,
 "Smith, Peter" <peters@fast.au.fujitsu.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <3DDDA96A-D335-4122-A332-4E1D3F060CF3@enterprisedb.com>
References: <DB35438F-9356-4841-89A0-412709EBD3AB@enterprisedb.com>
 <CAA4eK1KkhsNAW3=XxOdVRD9+RcQG5PRRFBFzAMibMG0YFFmAmg@mail.gmail.com>
 <B522A0FA-0B5C-4A66-9B73-928CAA86FB88@enterprisedb.com>
 <CAA4eK1KM23D06_rUEsgnTFUpABoeCCoTun2TMrQzWgxWOYY7fA@mail.gmail.com>
 <DE7E13B7-DC76-416A-A98F-3BC3F80E6BE9@enterprisedb.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/3DDDA96A-D335-4122-A332-4E1D3F060CF3%40enterprisedb.com>
Precedence: bulk


> On Jun 19, 2021, at 7:44 AM, Mark Dilger =
<mark.dilger@enterprisedb.com> wrote:
>=20
> Wouldn't the user rather skip just the problematic rows?  I understand =
that on the subscriber side it is difficult to do so, but if you are =
going to implement this sort of thing, it makes more sense to allow the =
user to filter out data that is 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 the pre-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 already have the code =
catching errors for subscriptions where disable_on_error is true.  We =
could use that same code path for subscriptions that have one or more =
BEFORE or AFTER ErrorTriggers defined.  We could pass the trigger all =
the error context information along with the row and subscription =
information, and allow the trigger to either modify the data being =
replicated or make modifications to the table being changed.  I think =
having support for both BEFORE and AFTER would be important, as a common =
design pattern might be to move aside the conflicting rows in the BEFORE =
trigger, then reconcile and merge them back into the table in the AFTER =
trigger.  If the xid still cannot be replicated after one attempt using =
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 should muck about with xids =
for arbitrarily many conflicting transactions.

=E2=80=94
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company