BUG #15801: pg_stat_database update stats_reset only by pg_stat_reset

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #15801: pg_stat_database update stats_reset only by pg_stat_reset

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      15801
Logged by:          zhang lian zhuang
Email address:      [hidden email]
PostgreSQL version: 11.1
Operating system:   linux
Description:        

pg_stat_reset_single_table_counters/pg_stat_reset_single_function_counters
only update pg_stat_database column stats_reset.
stat_reset shuld update when all the column is reset.

sample:
drop database if exists lzzhang_db;
create database lzzhang_db;
\c lzzhang_db

create table lzzhang_tab(id int);
insert into lzzhang_tab values(1);
insert into lzzhang_tab values(1);

select tup_fetched, stats_reset from pg_stat_database where
datname='lzzhang_db';
select pg_sleep(1);

select pg_stat_reset_single_table_counters('lzzhang_tab'::regclass::oid);
select tup_fetched, stats_reset from pg_stat_database where
datname='lzzhang_db';

result:
 tup_fetched |          stats_reset          
-------------+-------------------------------
         514 | 2019-05-12 03:22:55.702753+08
(1 row)
 tup_fetched |          stats_reset          
-------------+-------------------------------
         710 | 2019-05-12 03:22:56.729336+08
(1 row)
tup_fetched is not reset。

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15801: pg_stat_database update stats_reset only by pg_stat_reset

Michael Paquier-2
On Mon, May 13, 2019 at 07:15:47AM +0000, PG Bug reporting form wrote:
> pg_stat_reset_single_table_counters/pg_stat_reset_single_function_counters
> only update pg_stat_database column stats_reset.
> stat_reset shuld update when all the column is reset.

These two functions reset statistics for a single table, and what you
are looking at are the database-level statistics.
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: BUG #15801: pg_stat_database update stats_reset only by pg_stat_reset

Kuntal Ghosh
In reply to this post by PG Bug reporting form
On Mon, May 13, 2019 at 12:46 PM PG Bug reporting form <[hidden email]> wrote:

pg_stat_reset_single_table_counters/pg_stat_reset_single_function_counters
only update pg_stat_database column stats_reset.
stat_reset shuld update when all the column is reset.
pg_stat_reset_single_table_counters resets the stats of a single table. From your example, it resets the following stats:

select * from pg_stat_all_tables where relid='lzzhang_tab'::regclass::oid;
 
It'll not reset the database statistics.

--
Thanks & Regards,
Kuntal Ghosh
EnterpriseDB: http://www.enterprisedb.com
Reply | Threaded
Open this post in threaded view
|

Re: Re: BUG #15801: pg_stat_database update stats_reset only by pg_stat_reset

Kuntal Ghosh
On Mon, May 13, 2019 at 2:01 PM 张连壮 <[hidden email]> wrote:
yes, It'll not reset the database statistics, but the column stats_reset of pg_stat_database is reset.

在 2019-05-13 16:23:54,"Kuntal Ghosh" <[hidden email]> 写道:
On Mon, May 13, 2019 at 12:46 PM PG Bug reporting form <[hidden email]> wrote:

pg_stat_reset_single_table_counters/pg_stat_reset_single_function_counters
only update pg_stat_database column stats_reset.
stat_reset shuld update when all the column is reset.
pg_stat_reset_single_table_counters resets the stats of a single table. From your example, it resets the following stats:

select * from pg_stat_all_tables where relid='lzzhang_tab'::regclass::oid;
 
It'll not reset the database statistics.
It seems from the code in pgstat_recv_resetsinglecounter that whenever any individual object inside the database is reset, the stats_reset of pg_stat_database view is updated. This behaviour got introduced by the following commit:

commit 4c468b37a281941afd3bf61c782b20def8c17047
Track last time for statistics reset on databases and bgwriter
    
    Tracks one counter for each database, which is reset whenever
    the statistics for any individual object inside the database is
    reset, and one counter for the background writer.

So, the behaviour seems to be intentional. But, the documents of pg_stat_database doesn't convey this behaviour properly.
stats_reset timestamp with time zone Time at which these statistics were last reset

IMHO, the actual behaviour is stats_reset is the time at which either any pg_stat_database statistic of a database is updated or any statistic of any object inside the database is updated.

--
Thanks & Regards,
Kuntal Ghosh
EnterpriseDB: http://www.enterprisedb.com