Re: Optionally automatically disable logical replication subscriptions on error

> 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.

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? It seems just as likely that there would be a million transactions that need to be purged, and creating an interface for users to manually review them and keep or discard on a case by case basis seems unworkable. Sure, you might have specific cases where the number of transactions to purge is small, but I don't like designing the feature around that assumption.

All the same, I'm looking forward to seeing your patch!

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