Hi,
My colleague, Mitsuru Hinata (in CC), found the following issue.
The documentation of pg_stat_reset_single_table_counters() says
pg_stat_reset_single_table_counters ( oid ) → void
Resets statistics for a single table or index in the current database
or shared across all databases in the cluster to zero.
This function is restricted to superusers by default, but other users
can be granted EXECUTE to run the function.
https://www.postgresql.org/docs/devel/monitoring-stats.html
But, the stats will not be reset if the table shared across all
databases is specified. IIUC, 5891c7a8e seemed to have mistakenly
removed the feature implemented in e04267844. What do you think?
* reproduce procedure
SELECT COUNT(*) FROM pg_stat_database;
SELECT pg_stat_reset_single_table_counters('pg_database'::regclass);
SELECT seq_scan FROM pg_stat_all_tables WHERE relid =
'pg_database'::regclass;
* unexpected result
* Rename OverrideSearchPath to SearchPathMatcher (current HEAD:
d3a38318a)
* pgstat: store statistics in shared memory (5891c7a8e)
psql=# SELECT seq_scan FROM pg_stat_all_tables WHERE relid =
'pg_database'::regclass;
seq_scan
----------
11
(1 row)
* expected result
* Enhance pg_stat_reset_single_table_counters function (e04267844)
* pgstat: normalize function naming (be902e2651), which is previous
commit of 5891c7a8e.
psql=# SELECT seq_scan FROM pg_stat_all_tables WHERE relid =
'pg_database'::regclass;
seq_scan
----------
0
(1 row)
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATIONFrom 561c4391c9dac30b5478637a6baf8c8689226da5 Mon Sep 17 00:00:00 2001
From: Masahiro Ikeda <mshr.ik...@ntt.com>
Date: Tue, 1 Aug 2023 13:46:00 +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)
Bump catalog version.
Need to backpatch from 15.
Reported-by: Mitsuru Hinata
---
src/backend/utils/adt/pgstatfuncs.c | 9 ++++-
src/include/catalog/catversion.h | 2 +-
src/test/regress/expected/stats.out | 60 +++++++++++++++++++++++++++++
src/test/regress/sql/stats.sql | 18 +++++++++
4 files changed, 86 insertions(+), 3 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/include/catalog/catversion.h b/src/include/catalog/catversion.h
index f507b49bb2..5a534771ed 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202307261
+#define CATALOG_VERSION_NO 202308011
#endif
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 8e63340782..23450d28a8 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -592,6 +592,66 @@ SELECT seq_scan, idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'
0 | 0
(1 row)
+-- ensure to reset statistics for a table and a index shared across all databases
+BEGIN;
+SET LOCAL enable_seqscan TO on;
+SET LOCAL enable_indexscan TO off;
+SET LOCAL enable_indexonlyscan TO off;
+EXPLAIN (COSTS off) SELECT count(*) FROM pg_database;
+ QUERY PLAN
+-------------------------------
+ Aggregate
+ -> Seq Scan on pg_database
+(2 rows)
+
+SELECT count(*) FROM pg_database; -- increment stats for the table
+ count
+-------
+ 4
+(1 row)
+
+SET LOCAL enable_seqscan TO off;
+SET LOCAL enable_indexscan TO on;
+SET LOCAL enable_indexonlyscan TO off;
+EXPLAIN (COSTS off) SELECT count(*) FROM pg_database WHERE oid = 1;
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Index Scan using pg_database_oid_index on pg_database
+ Index Cond: (oid = '1'::oid)
+(3 rows)
+
+SELECT count(*) FROM pg_database WHERE oid = 1; -- increment stats for the index
+ count
+-------
+ 1
+(1 row)
+
+COMMIT;
+SELECT pg_stat_reset_single_table_counters('pg_database'::regclass);
+ pg_stat_reset_single_table_counters
+-------------------------------------
+
+(1 row)
+
+SELECT pg_stat_reset_single_table_counters('pg_database_oid_index'::regclass);
+ pg_stat_reset_single_table_counters
+-------------------------------------
+
+(1 row)
+
+SELECT pg_stat_reset_single_table_counters('pg_database_datname_index'::regclass);
+ pg_stat_reset_single_table_counters
+-------------------------------------
+
+(1 row)
+
+SELECT seq_scan, idx_scan FROM pg_stat_all_tables WHERE relid = 'pg_database'::regclass;
+ seq_scan | idx_scan
+----------+----------
+ 0 | 0
+(1 row)
+
-- ensure we start out with exactly one index and sequential scan
BEGIN;
SET LOCAL enable_seqscan TO on;
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index fddf5a8277..1e45944e9c 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -309,6 +309,24 @@ COMMIT;
SELECT pg_stat_reset_single_table_counters('test_last_scan'::regclass);
SELECT seq_scan, idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
+-- ensure to reset statistics for a table and a index shared across all databases
+BEGIN;
+SET LOCAL enable_seqscan TO on;
+SET LOCAL enable_indexscan TO off;
+SET LOCAL enable_indexonlyscan TO off;
+EXPLAIN (COSTS off) SELECT count(*) FROM pg_database;
+SELECT count(*) FROM pg_database; -- increment stats for the table
+SET LOCAL enable_seqscan TO off;
+SET LOCAL enable_indexscan TO on;
+SET LOCAL enable_indexonlyscan TO off;
+EXPLAIN (COSTS off) SELECT count(*) FROM pg_database WHERE oid = 1;
+SELECT count(*) FROM pg_database WHERE oid = 1; -- increment stats for the index
+COMMIT;
+SELECT pg_stat_reset_single_table_counters('pg_database'::regclass);
+SELECT pg_stat_reset_single_table_counters('pg_database_oid_index'::regclass);
+SELECT pg_stat_reset_single_table_counters('pg_database_datname_index'::regclass);
+SELECT seq_scan, idx_scan FROM pg_stat_all_tables WHERE relid = 'pg_database'::regclass;
+
-- ensure we start out with exactly one index and sequential scan
BEGIN;
SET LOCAL enable_seqscan TO on;
--
2.25.1
