From: Masahiro Ikeda <ikedamsh@oss.nttdata.com>
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: pgsql-hackers@lists.postgresql.org
Cc: mitsuru.hinata.5432@gmail.com
Subject: Re: Fix pg_stat_reset_single_table_counters function
Date: Tue, 15 Aug 2023 14:13:46 +0900
Message-ID: <93d14a670cf090c08994f1f4958efa25@oss.nttdata.com> (raw)
In-Reply-To: <CAD21AoDhhdicG3rYwKq3fLngn=T4qRuFiOWtqeYFwMM_-R43WQ@mail.gmail.com>
References: <7cc69f863d9b1bc677544e3accd0e4b4@oss.nttdata.com>
<9a4a6718abb47647ae83f1f4b3f9bf68@oss.nttdata.com>
<CAD21AoCnFqzzW7DUW67Art=2w_Z3gwKU1suSn0d0UtUPtdbt_g@mail.gmail.com>
<20230812191207.aerskqox7aoan3ud@awork3.anarazel.de>
<97c9f7a46681f051b1356e2987c3fd6c@oss.nttdata.com>
<CAD21AoDhhdicG3rYwKq3fLngn=T4qRuFiOWtqeYFwMM_-R43WQ@mail.gmail.com>
On 2023-08-15 11:48, Masahiko Sawada wrote:
> On Mon, Aug 14, 2023 at 5:12 PM Masahiro Ikeda
> <ikedamsh@oss.nttdata.com> wrote:
>> 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.
>
> It seems to work well.
>
> +COMMENT ON DATABASE :current_database IS 'This is a test comment';
> -- insert or update in 'pg_shdescription'
>
> I think the current_database should be quoted (see other examples
> where using current_database(), e.g. collate.linux.utf8.sql). Also it
> would be better to reset the comment after the test.
Thanks! I fixed the issues in the v4 patch.
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION
Attachments:v4-0001-Fix-pg_stat_reset_single_table_counters-function.patch (text/x-diff, 4.9K) download
From bca4c1844994be8ed80a29b8cb760e2eb865dca9 Mon Sep 17 00:00:00 2001
From: Masahiro Ikeda <mshr.ikeda@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 | 42 +++++++++++++++++++++++++++++
src/test/regress/sql/stats.sql | 26 ++++++++++++++++++
3 files changed, 75 insertions(+), 2 deletions(-)
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.cindex 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.outindex 319164a5e9..eb24a02147 100644--- a/src/test/regress/expected/stats.out+++ b/src/test/regress/expected/stats.out@@ -764,6 +764,48 @@ 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)+-----+-- store the old comment to reset+SELECT shobj_description(d.oid, 'pg_database') as description_before+FROM pg_database d WHERE datname = current_database() \gset+-- update the stats in pg_shdescription+BEGIN;+SELECT current_database() as current_database \gset+COMMENT ON DATABASE :"current_database" IS 'This is a test comment';+SELECT pg_stat_force_next_flush();+ pg_stat_force_next_flush +--------------------------+ +(1 row)++COMMIT;+-- check to reset the stats+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)++-- cleanup the comment+\if :{?description_before}+ COMMENT ON DATABASE :"current_database" IS :'description_before';+\else+ COMMENT ON DATABASE :"current_database" IS NULL;+\endif
-----
-- Test that various stats views are being properly populated
-----
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sqlindex 9a16df1c49..735118c452 100644--- a/src/test/regress/sql/stats.sql+++ b/src/test/regress/sql/stats.sql@@ -376,6 +376,32 @@ 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)+-----++-- store the old comment to reset+SELECT shobj_description(d.oid, 'pg_database') as description_before+FROM pg_database d WHERE datname = current_database() \gset++-- update the stats in pg_shdescription+BEGIN;+SELECT current_database() as current_database \gset+COMMENT ON DATABASE :"current_database" IS 'This is a test comment';+SELECT pg_stat_force_next_flush();+COMMIT;++-- check to reset the stats+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;++-- cleanup the comment+\if :{?description_before}+ COMMENT ON DATABASE :"current_database" IS :'description_before';+\else+ COMMENT ON DATABASE :"current_database" IS NULL;+\endif
-----
-- Test that various stats views are being properly populated
--
2.25.1
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: ikedamsh@oss.nttdata.com, sawada.mshk@gmail.com, andres@anarazel.de, pgsql-hackers@lists.postgresql.org, mitsuru.hinata.5432@gmail.com
Subject: Re: Fix pg_stat_reset_single_table_counters function
In-Reply-To: <93d14a670cf090c08994f1f4958efa25@oss.nttdata.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox