Hi,
On 2023-08-13 04:12, Andres Freund wrote:
On 2023-08-10 17:48:10 +0900, Masahiko Sawada wrote:
Good catch! I've confirmed that the issue has been fixed by your
patch.
Indeed.
Thanks for your responses!
However, I'm not sure the added regression tests are stable since
autovacuum workers may scan the pg_database and increment the
statistics after resetting the stats.
What about updating the table and checking the update count is reset?
That'd
not be reset by autovacuum.
Yes. I confirmed that the stats are incremented by autovacuum as you
said.
I updated the patch to v3.
* remove the code to bump the CATALOG_VERSION_NO because I misunderstood
* change the test logic to check the update count instead of scan count
I changed the table to check the stats from pg_database to
pg_shdescription
because the stats can update via the SQL interface COMMENT command.
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION
From 215ef8ef68af30753cfcd4336b1f6bd9203ac014 Mon Sep 17 00:00:00 2001
From: Masahiro Ikeda <mshr.ik...@ntt.com>
Date: Mon, 14 Aug 2023 16:48:30 +0900
Subject: [PATCH] Fix pg_stat_reset_single_table_counters function.
This commit revives the feature to reset statistics for a single
relation shared across all databases in the cluster to zero, which
was implemented by the following commit.
* Enhance pg_stat_reset_single_table_counters function(e04267844)
The following commit accidentally deleted the feature.
* pgstat: store statistics in shared memory(5891c7a8e)
Need to backpatch from 15.
Reported-by: Mitsuru Hinata
---
src/backend/utils/adt/pgstatfuncs.c | 9 +++++++--
src/test/regress/expected/stats.out | 31 +++++++++++++++++++++++++++++
src/test/regress/sql/stats.sql | 13 ++++++++++++
3 files changed, 51 insertions(+), 2 deletions(-)
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 2a4c8ef87f..2b9742ad21 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -17,6 +17,7 @@
#include "access/htup_details.h"
#include "access/xlog.h"
#include "access/xlogprefetcher.h"
+#include "catalog/catalog.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_type.h"
#include "common/ip.h"
@@ -1776,13 +1777,17 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
-/* Reset a single counter in the current database */
+/*
+ * Reset a statistics for a single object, which may be of current
+ * database or shared across all databases in the cluster.
+ */
Datum
pg_stat_reset_single_table_counters(PG_FUNCTION_ARGS)
{
Oid taboid = PG_GETARG_OID(0);
+ Oid dboid = (IsSharedRelation(taboid) ? InvalidOid : MyDatabaseId);
- pgstat_reset(PGSTAT_KIND_RELATION, MyDatabaseId, taboid);
+ pgstat_reset(PGSTAT_KIND_RELATION, dboid, taboid);
PG_RETURN_VOID();
}
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 319164a5e9..11cb841386 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -764,6 +764,37 @@ FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
2 | t | 3 | t
(1 row)
+-----
+-- Test to reset stats for a table shared across all databases (ex. pg_shdescription)
+-----
+BEGIN;
+SELECT current_database() as current_database \gset
+COMMENT ON DATABASE :current_database IS 'This is a test comment'; -- insert or update in 'pg_shdescription'
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+COMMIT;
+SELECT n_tup_ins + n_tup_upd > 0 FROM pg_stat_all_tables WHERE relid = 'pg_shdescription'::regclass;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT pg_stat_reset_single_table_counters('pg_shdescription'::regclass);
+ pg_stat_reset_single_table_counters
+-------------------------------------
+
+(1 row)
+
+SELECT n_tup_ins + n_tup_upd FROM pg_stat_all_tables WHERE relid = 'pg_shdescription'::regclass;
+ ?column?
+----------
+ 0
+(1 row)
+
-----
-- Test that various stats views are being properly populated
-----
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 9a16df1c49..d113aed257 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -376,6 +376,19 @@ COMMIT;
SELECT seq_scan, :'test_last_seq' = last_seq_scan AS seq_ok, idx_scan, :'test_last_idx' < last_idx_scan AS idx_ok
FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
+-----
+-- Test to reset stats for a table shared across all databases (ex. pg_shdescription)
+-----
+
+BEGIN;
+SELECT current_database() as current_database \gset
+COMMENT ON DATABASE :current_database IS 'This is a test comment'; -- insert or update in 'pg_shdescription'
+SELECT pg_stat_force_next_flush();
+COMMIT;
+
+SELECT n_tup_ins + n_tup_upd > 0 FROM pg_stat_all_tables WHERE relid = 'pg_shdescription'::regclass;
+SELECT pg_stat_reset_single_table_counters('pg_shdescription'::regclass);
+SELECT n_tup_ins + n_tup_upd FROM pg_stat_all_tables WHERE relid = 'pg_shdescription'::regclass;
-----
-- Test that various stats views are being properly populated
--
2.25.1
