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

(This email has Andres properly in the TO field.)

Andres, can you comment on this thread? I see you had a commit to PG
15 in this area:

commit 5cd1c40b3c
Author: Andres Freund <andres(at)anarazel(dot)de>
Date: Thu Apr 14 17:40:25 2022 -0700

pgstat: set timestamps of fixed-numbered stats after a crash.

When not loading stats at startup (i.e. pgstat_discard_stats() getting
called), reset timestamps of fixed numbered stats would be left at
0. Oversight in 5891c7a8ed8.

Instead use pgstat_reset_after_failure() and add tests verifying that
fixed-numbered reset timestamps are set appropriately.

Reported-By: "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Discussion: https://postgr.es/m/CAKFQuwamFuaQHKdhcMt4Gbw5+Hca2UE741B8gOOXoA=TtAd2Yw@mail.gmail.com

Thanks.

---------------------------------------------------------------------------

On Fri, Aug 4, 2023 at 10:40:46PM +0530, Jobin Augustine wrote:
> 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.
>
>

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.