Extend pg_stat_statements_reset to reset statistics specific to a

particular user/db/query.

The function pg_stat_statements_reset() is extended to accept userid, dbid,
and queryid as input parameters.  Now, it can discard the statistics
gathered so far by pg_stat_statements corresponding to the specified
userid, dbid, and queryid.  If no parameter is specified or all the
specified parameters have default value aka 0, it will discard all
statistics as per the old behavior.

The new behavior is useful to get the fresh statistics for a specific
user/database/query without resetting all the existing statistics.

Author: Haribabu Kommi, with few additional changes by me
Reviewed-by: Michael Paquier, Amit Kapila and Fujii Masao
Discussion: https://postgr.es/m/CAJrrPGcyh-gkFswyc6C661K6cknL0XkNqVT0sQt2mFNMR4HRKA@mail.gmail.com

1 files changed, 64 insertions, 0 deletions

diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index a8361fd1bff..aef890d893d 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -195,4 +195,68 @@ DROP FUNCTION PLUS_TWO(INTEGER);
 
 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
 
+--
+-- Track user activity and reset them
+--
+SELECT pg_stat_statements_reset();
+CREATE ROLE regress_stats_user1;
+CREATE ROLE regress_stats_user2;
+
+SET ROLE regress_stats_user1;
+
+SELECT 1 AS "ONE";
+SELECT 1+1 AS "TWO";
+
+RESET ROLE;
+SET ROLE regress_stats_user2;
+
+SELECT 1 AS "ONE";
+SELECT 1+1 AS "TWO";
+
+RESET ROLE;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+--
+-- Don't reset anything if any of the parameter is NULL
+--
+SELECT pg_stat_statements_reset(NULL);
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+--
+-- remove query ('SELECT $1+$2 AS "TWO"') executed by regress_stats_user2
+-- in the current_database
+--
+SELECT pg_stat_statements_reset(
+	(SELECT r.oid FROM pg_roles AS r WHERE r.rolname = 'regress_stats_user2'),
+	(SELECT d.oid FROM pg_database As d where datname = current_database()),
+	(SELECT s.queryid FROM pg_stat_statements AS s
+				WHERE s.query = 'SELECT $1+$2 AS "TWO"' LIMIT 1));
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+--
+-- remove query ('SELECT $1 AS "ONE"') executed by two users
+--
+SELECT pg_stat_statements_reset(0,0,s.queryid)
+	FROM pg_stat_statements AS s WHERE s.query = 'SELECT $1 AS "ONE"';
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+--
+-- remove query of a user (regress_stats_user1)
+--
+SELECT pg_stat_statements_reset(r.oid)
+		FROM pg_roles AS r WHERE r.rolname = 'regress_stats_user1';
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+--
+-- reset all
+--
+SELECT pg_stat_statements_reset(0,0,0);
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+--
+-- cleanup
+--
+DROP ROLE regress_stats_user1;
+DROP ROLE regress_stats_user2;
+
 DROP EXTENSION pg_stat_statements;