Re: Design of pg_stat_subscription_workers vs pgstats

On Sat, Feb 19, 2022 at 10:37 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Sat, Feb 19, 2022 at 6:51 PM David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> >
> > On Saturday, February 19, 2022, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >>
> >> On Sat, Feb 19, 2022 at 1:17 AM David G. Johnston
> >> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> >> >
> >> > On Fri, Feb 18, 2022 at 1:26 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >> >>
> >> >>
> >> >> Here is the summary of the discussion, changes, and plan.
> >> >>
> >> >> 1. Move some error information such as the error message to a new
> >> >> system catalog, pg_subscription_error. The pg_subscription_error table
> >> >> would have the following columns:
> >> >>
> >> >> * sesubid : subscription Oid.
> >> >> * serelid : relation Oid (NULL for apply worker).
> >> >> * seerrlsn : commit-LSN or the error transaction.
> >> >> * seerrcmd : command (INSERT, UPDATE, etc.) of the error transaction.
> >> >> * seerrmsg : error message
> >> >
> >> >
> >> > Not a fan of the "se" prefix but overall yes. We should include a timestamp.
> >> >
> >>
> >> How about naming it pg_subscription_worker_error as Peter E. has
> >> suggested in one of his emails? I find pg_subscription_error slightly
> >> odd as one could imagine that even the errors related to subscription
> >> commands like Alter Subscription.
> >>
> >
> > Adding worker makes sense.

Agreed.

> >
> >>
> >> >>
> >> >> The tuple is inserted or updated when an apply worker or a tablesync
> >> >> worker raises an error. If the same error occurs in a row, the update
> >> >> is skipped.
> >> >
> >>
> >> Are you going to query table to check if it is same error?
> >
> >
> > I don’t get the question, the quoted text is your which I disagree with.
> >
>
> It was Sawada-San's email and this question was for him.

What I wanted to say about how to insert/update the tuple to
pg_subscription_worker_error is that we insert a new tuple for the
first time, then, when the next error occurred, the worker fetches the
tuple and checks if the error (i.e., error-LSN, error-cmd, and
error-message) is exactly the same as previous one. If it is, we can
skip updating it. But if we include the error-timestamp in the tuple,
we need to update it every time.

Regards,

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/