Thank you Hamid for working on this and coming with a fix.
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.