MIME-Version: 1.0
References: <DB35438F-9356-4841-89A0-412709EBD3AB@enterprisedb.com>
In-Reply-To: <DB35438F-9356-4841-89A0-412709EBD3AB@enterprisedb.com>
From: Amit Kapila <amit.kapila16@gmail.com>
Date: Fri, 18 Jun 2021 10:17:36 +0530
Message-ID: 
 <CAA4eK1KkhsNAW3=XxOdVRD9+RcQG5PRRFBFzAMibMG0YFFmAmg@mail.gmail.com>
Subject: Re: Optionally automatically disable logical replication
 subscriptions on error
To: Mark Dilger <mark.dilger@enterprisedb.com>,
 Sawada Masahiko <sawada.mshk@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAA4eK1KkhsNAW3%3DXxOdVRD9%2BRcQG5PRRFBFzAMibMG0YFFmAmg%40mail.gmail.com>
Precedence: bulk

On Fri, Jun 18, 2021 at 1:48 AM Mark Dilger
<mark.dilger@enterprisedb.com> wrote:
>
> Hackers,
>
> Logical replication apply workers for a subscription can easily get stuck=
 in an infinite loop of attempting to apply a change, triggering an error (=
such as a constraint violation), exiting with an error written to the subsc=
ription worker log, and restarting.
>
> As things currently stand, only superusers can create subscriptions.  Ong=
oing work to delegate superuser tasks to non-superusers creates the potenti=
al for even more errors to be triggered, specifically, errors where the app=
ly worker does not have permission to make changes to the target table.
>
> The attached patch makes it possible to create a subscription using a new=
 subscription_parameter, "disable_on_error", such that rather than going in=
to an infinite loop, the apply worker will catch errors and automatically d=
isable the subscription, breaking the loop.  The new parameter defaults to =
false.  When false, the PG_TRY/PG_CATCH overhead is avoided, so for subscri=
ptions which do not use the feature, there shouldn't be any change.  Users =
can manually clear the error after fixing the underlying issue with an ALTE=
R SUBSCRIPTION .. ENABLE command.
>

I see this idea has merits and it will help users to repair failing
subscriptions. Few points on a quick look at the patch: (a) The patch
seem to be assuming that the error can happen only by the apply worker
but I think the constraint violation can happen via one of the table
sync workers as well, (b) What happens if the error happens when you
are updating the error information in the catalog table. I think
instead of seeing the actual apply time error, the user might see some
other for which it won't be clear what is an appropriate action.

We are also discussing another action like skipping the apply of the
transaction on an error [1]. I think it is better to evaluate both the
proposals as one seems to be an extension of another. Adding
Sawada-San, as he is working on the other proposal.

[1] - https://www.postgresql.org/message-id/CAD21AoDeScrsHhLyEPYqN3sydg6PxA=
PVBboK%3D30xJfUVihNZDA%40mail.gmail.com

--=20
With Regards,
Amit Kapila.