Re: Resetting spilled txn statistics in pg_stat_replication

On Wed, Jul 8, 2020 at 11:28 AM Masahiko Sawada
<masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
>
> On Tue, 7 Jul 2020 at 17:50, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> >
> >
> >
> > On Tue, Jul 7, 2020 at 5:10 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >>
> >> On Tue, Jul 7, 2020 at 7:07 AM Masahiko Sawada
> >> <masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
> >> >
> >> > On Mon, 6 Jul 2020 at 20:45, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >> > >
> >> > > > Second, as long as the unique identifier is the slot name there is no
> >> > > > convenient way to distinguish between the same name old and new
> >> > > > replication slots, so the backend process or wal sender process sends
> >> > > > a message to the stats collector to drop the replication slot at
> >> > > > ReplicationSlotDropPtr(). This strategy differs from what we do for
> >> > > > table, index, and function statistics. It might not be a problem but
> >> > > > I’m thinking a better way.
> >> > > >
> >> > >
> >> > > Can we rely on message ordering in the transmission mechanism (UDP)
> >> > > for stats? The wiki suggests [1] we can't. If so, then this might
> >> > > not work.
> >> >
> >> > Yeah, I'm also concerned about this. Another idea would be to have
> >> > another unique identifier to distinguish old and new replication slots
> >> > with the same name. For example, creation timestamp. And then we
> >> > reclaim the stats of unused slots later like table and function
> >> > statistics.
> >> >
> >>
> >> So, we need to have 'creation timestamp' as persistent data for slots
> >> to achieve this? I am not sure of adding creation_time as a parameter
> >> to identify for this case because users can change timings on systems
> >> so it might not be a bullet-proof method but I agree that it can work
> >> in general.
> >
> >
> > If we need them to be persistent across time like that, perhaps we simply need to assign oids to replication slots? That might simplify this problem quite a bit?
>
> Yeah, I guess assigning oids to replication slots in the same way of
> oids in system catalogs might not work because physical replication
> slot can be created even during recovery. But using a
> monotonically-increasing integer as id seems better and straight
> forward.
>

But don't we need to make it WAL logged as well similar to what we do
in GetNewObjectId? I am thinking do we really need Oids for slots or
is it okay to have some approximate stats in boundary cases?

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com