Re: BUG #18046: stats collection behaviour change is affecting the usability of information.

On Tue, 1 Aug 2023 at 22:01, PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 18046
> Logged by: Jobin Augustine
> Email address: jobinau(at)gmail(dot)com
> PostgreSQL version: 15.3
> Operating system: CentOS/RHEL
> Description:
>
> After stats collection changes in PG 15, The behaviour stats_reset
> information is changed. which is adversely affecting the usability of data
> presented in the view.
> ```
> select stats_reset from pg_stat_database;
> ```
> Before PG15, stats_reset was always populated so that the user knows the
> cumulative values presented are of how many days.
> But unfortunately, PG 15 keeps it null unless there is an explicit reset
> (pg_stat_reset). The value goes again null if there is a reset due to a
> crash.
> So on a regular system, the User can't understand the cumulative values
> presented in the view.
>
>
Thank you for the bug report Jobin.

IMHO, this is a valid concern. As per the documentation, the "stats_reset"
column tracks the last time the stats were reset. There is no mention of
this being timestamp for manual reset only.

Internally, the server calls pgstat_reset_after_failure whenever it cannot
find the stats file or if the server is recovering from a crash. In case of
a crash, the stats may no longer be valid. Therefore, internally the stats
are dropped, and a new file is written.

Attach is a fix for PG16 and PG15 that resolves this issue. It ensures that
when the database stats are being written to disk and the stats_reset is
not set, it adds the current timestamp to it. Since a new file is written
at initdb and when the server is recovering from a crash, this works as
expected.