Re: Enhance statistics reset functions to return reset timestamp

On Sat, Aug 9, 2025 at 7:37 AM Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> Hi,
>
> On 2025-08-08 13:18:39 +0900, Shinya Kato wrote:
> > I would like to propose a series of patches that enhance the behavior
> > of various statistics reset functions by making them return the reset
> > timestamp. This change improves usability and aligns with the behavior
> > introduced in commit dc9f8a798[1], where pg_stat_statements_reset()
> > was updated to return the reset time.
> >
> > The following functions have been modified to return a TIMESTAMP WITH
> > TIME ZONE value indicating when the statistics were reset:
> > - pg_stat_reset()
> > - pg_stat_reset_shared()
> > - pg_stat_reset_single_table_counters()
> > - pg_stat_reset_backend_stats()
> > - pg_stat_reset_single_function_counters()
> > - pg_stat_reset_slru()
> > - pg_stat_reset_replication_slot()
> > - pg_stat_reset_subscription_stats()
> > - pg_stat_clear_snapshot()
>
> -1 - I think it was a mistake to introduce support for granular resets, we
> shouldn't bury ourselves deeper. If anything we should rip out everything
> other than 1) a global reset b) a per-database reset.
>
> Leaving that aside, I just don't see a convincing use case for returning the
> timestamp here.

As I mentioned earlier, the use case is to obtain an audit trail of
the statistics reset time from the function’s return value.

Also, how should we think about consistency with
pg_stat_statements_reset()? The reset timestamp for pg_stat_statements
can be obtained from the pg_stat_statements_info view, which returns a
TIMESTAMPTZ. Of course, pg_stat_statements is a contrib module, so we
don’t necessarily have to match its behavior. However, when running a
script that resets various statistics before tests, if only
pg_stat_statements_reset() returns a TIMESTAMPTZ, it might look as
though the other statistics reset functions failed.

What these patches do is simply return the TIMESTAMPTZ that is already
computed for the pg_stat_* views, which seems to me a reasonable
change.

--
Best regards,
Shinya Kato
NTT OSS Center