Re: Optionally automatically disable logical replication subscriptions on error

On Mon, Jun 21, 2021 at 7:56 AM Mark Dilger
<mark(dot)dilger(at)enterprisedb(dot)com> wrote:
>
> > On Jun 20, 2021, at 7:17 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >
> > I will submit the patch.
>
> Great, thanks!
>
> > There was a discussion that the skipping transaction patch would also
> > need to have a feature that tells users the details of the last
> > failure transaction such as its XID, timestamp, action etc. In that
> > sense, those two patches might need the common infrastructure that the
> > apply workers leave the error details somewhere so that the users can
> > see it.
>
> Right. Subscription on error triggers would need that, too, if we wrote them.
>
> > Is it really useful to write only error message to the system catalog?
> > Even if we see the error message like "duplicate key value violates
> > unique constraint “test_tab_pkey”” on the system catalog, we will end
> > up needing to check the server log for details to properly resolve the
> > conflict. If the user wants to know whether the subscription is
> > disabled manually or automatically, the error message on the system
> > catalog might not necessarily be necessary.
> >

I think the two key points are (a) to define exactly what all
information is required to be logged on error, (b) where do we want to
store the information based on requirements. I see that for (b) Mark
is inclined to use the existing catalog table. I feel that is worth
considering but not sure if that is the best way to deal with it. For
example, if we store that information in the catalog, we might need to
consider storing it both in pg_subscription and pg_subscription_rel,
otherwise, we might overwrite the errors as I think what is happening
in the currently proposed patch. The other possibilities could be to
define a new catalog table to capture the error information or log the
required information via stats collector and then the user can see
that info via some stats view.

>
> We can put more information in there. I don't feel strongly about it. I'll wait for your patch to see what infrastructure you need.
>
> > The feature discussed in that thread is meant to be a repair tool for
> > the subscription in emergency cases when something that should not
> > have happened happened. I guess that resolving row (or column) level
> > conflict should be done in another way, for example, by defining
> > policies for each type of conflict.
>
> I understand that is the idea, but I'm having trouble believing it will work that way in practice. If somebody has a subscription that has gone awry, what reason do we have to believe there will only be one transaction that will need to be manually purged?
>

Because currently, we don't proceed after an error unless it is
resolved. Why do you think there could be multiple such transactions?

--
With Regards,
Amit Kapila.