Re: Failed transaction statistics to measure the logical replication progress

On Tue, Aug 3, 2021 at 6:11 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Tue, Aug 3, 2021 at 10:59 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >
> > 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.
> >
>
> I was trying to think based on similar counters in pg_stat_database
> but if you think there is a value in showing errored and actual
> rollbacked transactions separately then we can do that but how do you
> think one can make use of it?

I'm concerned that the value that includes both errored and actual
rollbacked transactions doesn't make sense in practice since the
number of errored transactions can easily get increased once a
conflict happens. IMO the errored transaction doesn’t not necessarily
necessary since the number of (successive) errors that happened on the
subscription is tracked by pg_stat_subscription_errors view. So it
might be enough to have actual rollbacked transactions. If this value
is high, it's likely that many rollbacked transactions are streamed,
unnecessarily consuming network bandwidth. So the user might want to
increase logical_decoding_work_mem to suppress transactions getting
streamed.

Regards,

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