Re: Skipping logical replication transactions on subscriber side

On Tue, Jul 6, 2021 at 11:28 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> On Mon, Jul 5, 2021 at 7:33 PM Alexey Lesovsky <lesovsky(at)gmail(dot)com> wrote:
> >
> > Hi,
> > Have a few notes about pg_stat_logical_replication_error from the DBA point of view (which will use this view in the future).
>
> Thank you for the comments!
>
> > 1. As I understand it, this view might contain many errors related to different subscriptions. It is better to name "pg_stat_logical_replication_errors" using the plural form (like this done for stat views for tables, indexes, functions).
>
> Agreed.
>
> > Also, I'd like to suggest thinking twice about the view name (and function used in view DDL) - "pg_stat_logical_replication_error" contains very common "logical replication" words, but the view contains errors related to subscriptions only. In the future there could be other kinds of errors related to logical replication, but not related to subscriptions - what will you do?
>
> Is pg_stat_subscription_errors or
> pg_stat_logical_replication_apply_errors better?
>

Few more to consider: pg_stat_apply_failures,
pg_stat_subscription_failures, pg_stat_apply_conflicts,
pg_stat_subscription_conflicts.

> > 2. Add a field with database name or id - it helps to quickly understand to which database the subscription belongs.
>
> Agreed.
>
> > 3. Add a counter field with total number of errors - it helps to calculate errors rates and aggregations (sum), and don't lose information about errors between view checks.
>
> Do you mean to increment the error count if the error (command, xid,
> and relid) is the same as the previous one? or to have the total
> number of errors per subscription?
>

I would prefer the total number of errors per subscription.

> And what can we infer from the
> error rates and aggregations?
>

Say, if we add a column like failure_type/conflict_type as well and
one would be interested in knowing how many conflicts are due to
primary key conflicts vs. update/delete conflicts.

You might want to consider keeping this view patch before the skip_xid
patch in your patch series as this will be base for the skip_xid
patch.

--
With Regards,
Amit Kapila.