Re: Design of pg_stat_subscription_workers vs pgstats

On Tue, Feb 15, 2022 at 11:56 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> On 2022-02-03 13:33:08 +0900, Masahiko Sawada wrote:
> > I see that important information such as error-XID that can be used
> > for ALTER SUBSCRIPTION SKIP needs to be stored in a reliable way, and
> > using system catalogs is a reasonable way for this purpose. But it's
> > still unclear to me why all error information that is currently shown
> > in pg_stat_subscription_workers view, including error-XID and the
> > error message, relation OID, action, etc., need to be stored in the
> > catalog. The information other than error-XID doesn't necessarily need
> > to be reliable compared to error-XID. I think we can have
> > error-XID/LSN in the pg_subscription catalog and have other error
> > information in pg_stat_subscription_workers view. After the user
> > checks the current status of logical replication by checking
> > error-XID/LSN, they can check pg_stat_subscription_workers for
> > details.
>
> The stats system isn't geared towards storing error messages and
> such. Generally it is about storing counts of events etc, not about
> information about a single event. Obviously there are a few cases where that
> boundary isn't that clear...
>

True, in the beginning, we discussed this information to be stored in
system catalog [1] (See .... Also, I am thinking that instead of a
stat view, do we need to consider having a system table .. ) but later
discussion led us to store this as stats.

> IOW, storing information like:
> - subscription oid
> - retryable error count
> - hard error count
> - #replicated inserts
> - #replicated updates
> - #replicated deletes
>
> is what pgstats is for.
>

Some of this and similar ((like error count, last_error_time)) is
present in stats and something on the lines of the other information
is proposed in [2] (xacts successfully replicated (committed),
aborted, etc) to be stored along with it.

> But not
> - subscription oid
> - error message
> - xid of error
> - ...
>

I think from the current set of things we are capturing, the other
thing in this list will be error_command (insert/update/delete..) and
or probably error_code. So, we can keep this information in a system
table.

Based on this discussion, it appears to me what we want here is to
store the error info in some persistent way (system table) and the
counters (both error and success) of logical replication in stats. If
we can't achieve this work (separation) in the next few weeks (before
the feature freeze) then I'll revert the work related to stats.

[1] - https://www.postgresql.org/message-id/CAA4eK1LTE-AYtwatvLzAw%2BVy53C92QHoB7-rVbX-9nf8ws2Vag%40mail.gmail.com
[2] - https://commitfest.postgresql.org/37/3504/

--
With Regards,
Amit Kapila.