Re: Higher level questions around shared memory stats

Hi,

On 2022-03-30 14:08:41 -0700, Andres Freund wrote:
> On 2022-03-30 14:42:23 -0400, Robert Haas wrote:
> > On Tue, Mar 29, 2022 at 5:01 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> > > I can think of these different times:
> > >
> > > - Last time stats were removed due to starting up in crash recovery
> > > - Last time stats were created from scratch, because no stats data file was
> > > present at startup
> > > - Last time stats were thrown away due to corruption
> > > - Last time a subset of stats were reset using one of the pg_reset* functions
> > >
> > > Makes sense?
> >
> > Yes. Possibly that last could be broken in to two: when all stats were
> > last reset, when some stats were last reset.
>
> Believe it or not, we don't currently have a function to reset all stats. We
> should definitely add that though, because the invocation to reset all stats
> gets more ridiculous^Wcomplicated with each release.

I assume we'd want all of these to be persistent (until the next time stats
are lost, of course)?

We currently have the following SQL functions showing reset times:
pg_stat_get_bgwriter_stat_reset_time
pg_stat_get_db_stat_reset_time
and kind of related:
pg_stat_get_snapshot_timestamp

There's also a few functions showing the last time something has happened,
like pg_stat_get_last_analyze_time().

Trying to fit those patterns we could add functions like:

pg_stat_get_stats_last_cleared_recovery_time
pg_stat_get_stats_last_cleared_corrupted_time
pg_stat_get_stats_last_not_present_time
pg_stat_get_stats_last_partially_reset_time

Not great, but I don't immediately have a better idea.

Maybe it'd look better as a view / SRF? pg_stat_stats / pg_stat_get_stats()?
Potential column names:
- cleared_recovery_time
- cleared_corrupted_time
- not_preset_time
- partially_reset_time

It's not a lot of time to code these up. However, it's late in cycle, and
they're not suddenly needed due to shared memory stats, so I have a few doubts
about adding them now?

Greetings,

Andres Freund