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

Thank you Hamid for working on this and coming with a fix.

On Fri, Aug 4, 2023 at 4:53 PM Hamid Akhtar <hamid(dot)akhtar(at)percona(dot)com>
wrote:

>
> 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.
>
> Without this base info, users don't have the option to understand the
cumulative statistics in the stats view

>
> 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.
>
>

I can confirm that this patch fixes the problem.
I could find simple steps to reproduce the original problem independently.

Step 1 : Create a new database
CREATE DATABASE db1;

Step 2. Create a table in the database
\c db1
CREATE TABLE t1 (id INT);

Step 3. Check the timestamp of the start of database-level statistics
db1=# SELECT datname,stats_reset FROM pg_stat_database;

Expected behaviour(works in all versions upto and including PostgreSQL 14)

datname | stats_reset
-----------+-------------------------------
| 2023-08-02 06:41:15.777135+00
postgres | 2023-08-02 06:41:15.777108+00
template1 |
template0 |
db1 | 2023-08-02 11:02:54.954363+00
(5 rows)

The problem in PostgreSQL 15 and above

datname | stats_reset
-----------+------------------------------
|
postgres |
db1 |
template1 |
template0 |
(5 rows)

Once again, Thank you for the fix.

Jobin Augustine.