Re: Failed transaction statistics to measure the logical replication progress

On Tue, Aug 3, 2021 at 11:47 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Mon, Aug 2, 2021 at 1:13 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >
> > On Mon, Aug 2, 2021 at 2:52 PM osumi(dot)takamichi(at)fujitsu(dot)com
> > <osumi(dot)takamichi(at)fujitsu(dot)com> wrote:
> > >
> > >
> > > Accordingly, I'm thinking to have unsuccessful and successful stats on the sub side.
> > > Sawada-san is now implementing a new view in [1].
> > > Do you think that I should write a patch to introduce a new separate view
> > > or write a patch to add more columns to the new view "pg_stat_subscription_errors" that is added at [1] ?
> >
> > pg_stat_subscriptions_errors view I'm proposing is a view showing the
> > details of error happening during logical replication. So I think a
> > separate view or pg_stat_subscription view would be a more appropriate
> > place.
> >
>
> +1 for having these stats in pg_stat_subscription. Do we want to add
> two columns (xact_commit: number of transactions successfully applied
> in this subscription, xact_rollback: number of transactions that have
> been rolled back in this subscription)

Sounds good. We might want to have separate counters for the number of
transactions failed due to an error and transactions rolled back by
stream_abort.

pg_stat_subscription currently shows logical replication worker stats
on the shared memory. I think xact_commit and xact_rollback should
survive beyond the server restarts, so it would be better to be
collected by the stats collector. My skipping transaction patch adds a
hash table of which entry represents a subscription stats. I guess we
can use the hash table so that one subscription stats entry has both
transaction stats and errors.

> or do you guys have something else in mind?

Osumi-san was originally concerned that there is no way to grasp the
exact number and size of successful and unsuccessful transactions. The
above idea covers only the number of successful and unsuccessful
transactions but not the size. What do you think, Osumi-san?

Regards,

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