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.