From 561c4391c9dac30b5478637a6baf8c8689226da5 Mon Sep 17 00:00:00 2001 From: Masahiro Ikeda 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