pg_stat_database update stats_reset only by pg_stat_reset
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 but stats_reset is reset.
Attachments:
0001-pg_stat_database-update-stats_reset-only-by-pg_stat_.patchtext/x-patch; charset=US-ASCII; name=0001-pg_stat_database-update-stats_reset-only-by-pg_stat_.patchDownload
From 4f78735ceab9a410dbd015d1c0b1bf977eddf5b7 Mon Sep 17 00:00:00 2001
From: zhang lian zhuang <zlianzhuang@163.com>
Date: Sun, 12 May 2019 18:38:55 +0800
Subject: [PATCH] pg_stat_database update stats_reset only by pg_stat_reset
pg_stat_reset_single_table_counters and pg_stat_reset_single_function_counters
only update pg_stat_database column stats_reset.
stat_reset shuld update when all the column is reset.
---
src/backend/postmaster/pgstat.c | 3 ---
1 file changed, 3 deletions(-)
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 15852fe24f..cba4450c01 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -5984,9 +5984,6 @@ pgstat_recv_resetsinglecounter(PgStat_MsgResetsinglecounter *msg, int len)
if (!dbentry)
return;
- /* Set the reset timestamp for the whole database */
- dbentry->stat_reset_timestamp = GetCurrentTimestamp();
-
/* Remove object if it exists, ignore it if not */
if (msg->m_resettype == RESET_TABLE)
(void) hash_search(dbentry->tables, (void *) &(msg->m_objectid),
--
2.21.0
it reset statistics for a single table and update the column stats_reset of
pg_stat_database.
but i think that stats_reset shoud be database-level statistics, a single
table should not update the column stats_reset.
i am monitor the xact_commit every 5 minutes, when stats_reset is reset but
ohter columns is not reset, i can't decide
if i will recount the xact_commit, because pg_stat_reset make all column to
zero. pg_stat_reset_single_table_counters
only reset the column stats_reset.
张连壮 <lianzhuangzhang@gmail.com> 于2019年5月13日周一 下午3:30写道：
Show quoted text
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_dbcreate 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 but stats_reset is reset.
张连壮 wrote:
it reset statistics for a single table and update the column stats_reset of
pg_stat_database.
but i think that stats_reset shoud be database-level statistics, a single
table should not update the column stats_reset.
This patch is a current CF entry at
https://commitfest.postgresql.org/23/2116/
The issue it addresses was submitted as bug #15801:
/messages/by-id/15801-21c7fbff08b6c10c@postgresql.org
As mentioned in the discussion on -bugs, it's not necessarily a bug
because:
* the comment in the code specifically states that it's intentional,
in pgstat_recv_resetsinglecounter():
/* Set the reset timestamp for the whole database */
dbentry->stat_reset_timestamp = GetCurrentTimestamp();
* the commit message also states the same:
commit 4c468b37a281941afd3bf61c782b20def8c17047
Author: Magnus Hagander <magnus@hagander.net>
Date: Thu Feb 10 15:09:35 2011 +0100
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.
Tomas Vondra, reviewed by Greg Smith
I can understand why you'd want that resetting the stats for a single object
would not reset the per-database timestamp, but this would revert a 8+ years
old decision that seems intentional and has apparently not been criticized
since then (based on searching for pg_stat_reset_single_table_counters in
the archives) . More opinions are probably needed in favor of this
change (or against, in which case the fate of the patch might be a
rejection).
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
On Thu, Jul 11, 2019 at 04:34:20PM +0200, Daniel Verite wrote:
I can understand why you'd want that resetting the stats for a single object
would not reset the per-database timestamp, but this would revert a 8+ years
old decision that seems intentional and has apparently not been criticized
since then (based on searching for pg_stat_reset_single_table_counters in
the archives) . More opinions are probably needed in favor of this
change (or against, in which case the fate of the patch might be a
rejection).
I agree with Daniel that breaking an 8-year-old behavior may not be of
the taste of folks relying on the current behavior, particularly
because we have not had complains about the current behavior being
bad. So -1 from me.
--
Michael
On Fri, Jul 12, 2019 at 01:51:50PM +0900, Michael Paquier wrote:
On Thu, Jul 11, 2019 at 04:34:20PM +0200, Daniel Verite wrote:
I can understand why you'd want that resetting the stats for a single object
would not reset the per-database timestamp, but this would revert a 8+ years
old decision that seems intentional and has apparently not been criticized
since then (based on searching for pg_stat_reset_single_table_counters in
the archives) . More opinions are probably needed in favor of this
change (or against, in which case the fate of the patch might be a
rejection).I agree with Daniel that breaking an 8-year-old behavior may not be of
the taste of folks relying on the current behavior, particularly
because we have not had complains about the current behavior being
bad. So -1 from me.
Yeah, I agree. There are several reasons why it's done this way:
1) overhead
Now we only store a two timestamps - for a database and for bgwriter. We
could track a timestamp for each object, of course ...
2) complexity
Updating the timestamps would be fairly simple, but what about querying
the data? Currently you fetch the data, see if the stats_reset changed
since the last snapshot, and if not you're good. If it changed, you know
some object (or the whole db) has reset counters, so you can't rely on
the data being consistent.
If we had stats_reset for each object, figuring out which data is still
valid and what has been reset would be far more complicated.
But reseting stats is not expected to be a common operation, so this
seemed like an acceptable tradeoff (and I'd argue it still is).
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Yeah, I agree. this is not necessary, i will remove the commitfest at
'2019-07-19'.
Tomas Vondra <tomas.vondra@2ndquadrant.com> 于2019年7月12日周五 下午9:07写道：
Show quoted text
On Fri, Jul 12, 2019 at 01:51:50PM +0900, Michael Paquier wrote:
On Thu, Jul 11, 2019 at 04:34:20PM +0200, Daniel Verite wrote:
I can understand why you'd want that resetting the stats for a single
object
would not reset the per-database timestamp, but this would revert a 8+
years
old decision that seems intentional and has apparently not been
criticized
since then (based on searching for pg_stat_reset_single_table_counters
in
the archives) . More opinions are probably needed in favor of this
change (or against, in which case the fate of the patch might be a
rejection).I agree with Daniel that breaking an 8-year-old behavior may not be of
the taste of folks relying on the current behavior, particularly
because we have not had complains about the current behavior being
bad. So -1 from me.Yeah, I agree. There are several reasons why it's done this way:
1) overhead
Now we only store a two timestamps - for a database and for bgwriter. We
could track a timestamp for each object, of course ...2) complexity
Updating the timestamps would be fairly simple, but what about querying
the data? Currently you fetch the data, see if the stats_reset changed
since the last snapshot, and if not you're good. If it changed, you know
some object (or the whole db) has reset counters, so you can't rely on
the data being consistent.If we had stats_reset for each object, figuring out which data is still
valid and what has been reset would be far more complicated.But reseting stats is not expected to be a common operation, so this
seemed like an acceptable tradeoff (and I'd argue it still is).regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services