Date: Wed, 6 Sep 2023 12:57:01 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Jobin Augustine <jobin.augustine@percona.com>
Cc: Hamid Akhtar <hamid.akhtar@percona.com>, jobinau@gmail.com,
	pgsql-bugs@lists.postgresql.org, Andres Freund <andres@anarazel.de>
Subject: Re: BUG #18046: stats collection behaviour change is affecting the
 usability of information.
Message-ID: <ZPivXYK++YnRcMZl@momjian.us>
References: <18046-9263842a9ec688e0@postgresql.org>
 <CANugjhvg-EFPc_1w1o0We8O5mUsd9DMYC6MhPOQeyPwaKFBhNQ@mail.gmail.com>
 <CAEpZXfek+O+CxbcLc+a+Ng2GxGmV4C90H___-LGXkP6ZUau60w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAEpZXfek+O+CxbcLc+a+Ng2GxGmV4C90H___-LGXkP6ZUau60w@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ZPivXYK%2B%2BYnRcMZl%40momjian.us>
Precedence: bulk


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@anarazel.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.g.johnston@gmail.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.akhtar@percona.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@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.