I've attached v12 of a patch. The only unsolved issue now is the
following:
On Sun, 2022-04-03 at 15:07 +0800, Julien Rouhaud wrote:
> +ALTER EXTENSION pg_stat_statements UPDATE TO '1.9';
> +\d pg_stat_statements
> +\d pg_stat_statements_info
> +SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
>
> I don't think this bring any useful coverage.
It is a little bit unclear to me what is the best solution here.
--
regards, Andrei
From 9359b7dfdadeb7a672c146030995626150acf231 Mon Sep 17 00:00:00 2001
From: Andrei Zubkov <[email protected]>
Date: Sun, 3 Apr 2022 12:21:47 +0300
Subject: [PATCH] pg_stat_statements: Track statement entry timestamp
This patch adds stats_since column to the pg_stat_statements view. This column
is populated with the current timestamp when a new statement is added to the
pg_stat_statements hashtable. It provides clean information about statistics
collection time interval for each statement. Besides it can be used
by sampling solutions to detect situations when a statement was evicted and
returned back between samples.
Such sampling solution could derive any pg_stat_statements statistic value for
an interval between two samples with except of all min/max statistics. To
address this issue this patch adds the ability to reset min/max
statistics independently of statement reset using the new minmax_only parameter
of the pg_stat_statements_reset(userid oid, dbid oid, queryid bigint,
minmax_only boolean) function.
Timestamp of such reset is stored in the minmax_stats_since field for
each statement. pg_stat_statements_reset() function is now returns
this timestamp as a result.
Discussion:
https://www.postgresql.org/message-id/flat/72e80e7b160a6eb189df9ef6f068cce3765d37f8.camel%40moonset.ru
---
contrib/pg_stat_statements/Makefile | 3 +-
.../expected/oldextversions.out | 61 +++
.../expected/pg_stat_statements.out | 361 +++++++++++++-----
.../pg_stat_statements--1.9--1.10.sql | 108 ++++++
.../pg_stat_statements/pg_stat_statements.c | 151 ++++++--
.../pg_stat_statements.control | 2 +-
.../pg_stat_statements/sql/oldextversions.sql | 8 +
.../sql/pg_stat_statements.sql | 149 +++++++-
doc/src/sgml/pgstatstatements.sgml | 66 +++-
9 files changed, 747 insertions(+), 162 deletions(-)
create mode 100644 contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 7fabd96f38d..edc40c8bbfb 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -6,7 +6,8 @@ OBJS = \
pg_stat_statements.o
EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.8--1.9.sql \
+DATA = pg_stat_statements--1.4.sql \
+ pg_stat_statements--1.9--1.10.sql pg_stat_statements--1.8--1.9.sql \
pg_stat_statements--1.7--1.8.sql pg_stat_statements--1.6--1.7.sql \
pg_stat_statements--1.5--1.6.sql pg_stat_statements--1.4--1.5.sql \
pg_stat_statements--1.3--1.4.sql pg_stat_statements--1.2--1.3.sql \
diff --git a/contrib/pg_stat_statements/expected/oldextversions.out b/contrib/pg_stat_statements/expected/oldextversions.out
index f18c08838f5..70877948491 100644
--- a/contrib/pg_stat_statements/expected/oldextversions.out
+++ b/contrib/pg_stat_statements/expected/oldextversions.out
@@ -136,4 +136,65 @@ SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
(1 row)
+ALTER EXTENSION pg_stat_statements UPDATE TO '1.9';
+\d pg_stat_statements
+ View "public.pg_stat_statements"
+ Column | Type | Collation | Nullable | Default
+---------------------+------------------+-----------+----------+---------
+ userid | oid | | |
+ dbid | oid | | |
+ toplevel | boolean | | |
+ queryid | bigint | | |
+ query | text | | |
+ plans | bigint | | |
+ total_plan_time | double precision | | |
+ min_plan_time | double precision | | |
+ max_plan_time | double precision | | |
+ mean_plan_time | double precision | | |
+ stddev_plan_time | double precision | | |
+ calls | bigint | | |
+ total_exec_time | double precision | | |
+ min_exec_time | double precision | | |
+ max_exec_time | double precision | | |
+ mean_exec_time | double precision | | |
+ stddev_exec_time | double precision | | |
+ rows | bigint | | |
+ shared_blks_hit | bigint | | |
+ shared_blks_read | bigint | | |
+ shared_blks_dirtied | bigint | | |
+ shared_blks_written | bigint | | |
+ local_blks_hit | bigint | | |
+ local_blks_read | bigint | | |
+ local_blks_dirtied | bigint | | |
+ local_blks_written | bigint | | |
+ temp_blks_read | bigint | | |
+ temp_blks_written | bigint | | |
+ blk_read_time | double precision | | |
+ blk_write_time | double precision | | |
+ wal_records | bigint | | |
+ wal_fpi | bigint | | |
+ wal_bytes | numeric | | |
+
+\d pg_stat_statements_info
+ View "public.pg_stat_statements_info"
+ Column | Type | Collation | Nullable | Default
+-------------+--------------------------+-----------+----------+---------
+ dealloc | bigint | | |
+ stats_reset | timestamp with time zone | | |
+
+SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
+ pg_get_functiondef
+--------------------------------------------------------------------------------------------------------------------------------
+ CREATE OR REPLACE FUNCTION public.pg_stat_statements_reset(userid oid DEFAULT 0, dbid oid DEFAULT 0, queryid bigint DEFAULT 0)+
+ RETURNS void +
+ LANGUAGE c +
+ PARALLEL SAFE STRICT +
+ AS '$libdir/pg_stat_statements', $function$pg_stat_statements_reset_1_7$function$ +
+
+(1 row)
+
+SET SESSION AUTHORIZATION pg_read_all_stats;
+SELECT pg_stat_statements_reset();
+ERROR: permission denied for function pg_stat_statements_reset
+RESET SESSION AUTHORIZATION;
DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index e0abe34bb6a..f5ff4031c59 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -4,10 +4,10 @@ CREATE EXTENSION pg_stat_statements;
--
SET pg_stat_statements.track_utility = FALSE;
SET pg_stat_statements.track_planning = TRUE;
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT 1 AS "int";
@@ -109,7 +109,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
SELECT $1 AS "int" | 2 | 2
SELECT $1 AS i UNION SELECT $2 ORDER BY i | 1 | 2
SELECT $1 || $2 | 1 | 1
- SELECT pg_stat_statements_reset() | 1 | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0
WITH t(f) AS ( +| 1 | 2
VALUES ($1), ($2) +| |
@@ -121,10 +121,10 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
--
-- CRUD: INSERT SELECT UPDATE DELETE on test table
--
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
-- utility "create table" should not be shown
@@ -206,7 +206,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
SELECT * FROM test ORDER BY a | 1 | 12
SELECT * FROM test WHERE a > $1 ORDER BY a | 2 | 4
SELECT * FROM test WHERE a IN ($1, $2, $3, $4, $5) | 1 | 8
- SELECT pg_stat_statements_reset() | 1 | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0
UPDATE test SET b = $1 WHERE a = $2 | 6 | 6
UPDATE test SET b = $1 WHERE a > $2 | 1 | 3
@@ -215,10 +215,10 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
--
-- INSERT, UPDATE, DELETE on test table to validate WAL generation metrics
--
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
-- utility "create table" should not be shown
@@ -241,7 +241,7 @@ FROM pg_stat_statements ORDER BY query COLLATE "C";
DELETE FROM pgss_test WHERE a > $1 | 1 | 1 | t | t | t
DROP TABLE pgss_test | 1 | 0 | t | t | f
INSERT INTO pgss_test VALUES(generate_series($1, $2), $3) | 1 | 10 | t | t | t
- SELECT pg_stat_statements_reset() | 1 | 1 | f | f | f
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1 | f | f | f
SELECT query, calls, rows, +| 0 | 0 | f | f | t
wal_bytes > $1 as wal_bytes_generated, +| | | | |
wal_records > $2 as wal_records_generated, +| | | | |
@@ -255,10 +255,10 @@ FROM pg_stat_statements ORDER BY query COLLATE "C";
-- pg_stat_statements.track = none
--
SET pg_stat_statements.track = 'none';
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT 1 AS "one";
@@ -282,10 +282,10 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-- pg_stat_statements.track = top
--
SET pg_stat_statements.track = 'top';
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
DO LANGUAGE plpgsql $$
@@ -335,7 +335,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
SELECT $1::TEXT | 1 | 1
SELECT PLUS_ONE($1) | 2 | 2
SELECT PLUS_TWO($1) | 2 | 2
- SELECT pg_stat_statements_reset() | 1 | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0
(5 rows)
@@ -343,10 +343,10 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-- pg_stat_statements.track = all
--
SET pg_stat_statements.track = 'all';
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
-- we drop and recreate the functions to avoid any caching funnies
@@ -394,7 +394,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
SELECT (i + $2)::INTEGER LIMIT $3 | 2 | 2
SELECT PLUS_ONE($1) | 2 | 2
SELECT PLUS_TWO($1) | 2 | 2
- SELECT pg_stat_statements_reset() | 1 | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0
(6 rows)
@@ -403,10 +403,10 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
--
CREATE TABLE pgss_a (id integer PRIMARY KEY);
CREATE TABLE pgss_b (id integer PRIMARY KEY, a_id integer REFERENCES pgss_a);
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
-- control query
@@ -482,7 +482,7 @@ SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE OF pgss_b, pgss_a
1 | SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id FOR UPDATE SKIP LOCKED
0 | SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"
- 1 | SELECT pg_stat_statements_reset()
+ 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(12 rows)
DROP TABLE pgss_a, pgss_b CASCADE;
@@ -490,10 +490,10 @@ DROP TABLE pgss_a, pgss_b CASCADE;
-- utility commands
--
SET pg_stat_statements.track_utility = TRUE;
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT 1;
@@ -524,7 +524,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
DROP TABLE IF EXISTS test | 3 | 0
DROP TABLE test | 1 | 0
SELECT $1 | 1 | 1
- SELECT pg_stat_statements_reset() | 1 | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0
(9 rows)
@@ -533,10 +533,10 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW,
-- REFRESH MATERIALIZED VIEW and SELECT INTO
--
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a;
@@ -589,17 +589,17 @@ SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE
FETCH NEXT pgss_cursor | 0 | 1 | 1
REFRESH MATERIALIZED VIEW pgss_matv | 0 | 1 | 13
SELECT generate_series(1, 10) c INTO pgss_select_into | 0 | 1 | 10
- SELECT pg_stat_statements_reset() | 0 | 1 | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 0 | 1 | 1
SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 | 0 | 0
(13 rows)
--
-- Track user activity and reset them
--
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
CREATE ROLE regress_stats_user1;
@@ -642,7 +642,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
SELECT $1 AS "ONE" | 1 | 1
SELECT $1+$2 AS "TWO" | 1 | 1
SELECT $1+$2 AS "TWO" | 1 | 1
- SELECT pg_stat_statements_reset() | 1 | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0
SET ROLE regress_stats_user1 | 1 | 0
SET ROLE regress_stats_user2 | 1 | 0
@@ -651,10 +651,10 @@ 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);
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset(NULL) IS NOT NULL AS t;
+ t
+---
+ f
(1 row)
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
@@ -667,8 +667,8 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
SELECT $1 AS "ONE" | 1 | 1
SELECT $1+$2 AS "TWO" | 1 | 1
SELECT $1+$2 AS "TWO" | 1 | 1
- SELECT pg_stat_statements_reset($1) | 1 | 1
- SELECT pg_stat_statements_reset() | 1 | 1
+ SELECT pg_stat_statements_reset($1) IS NOT NULL AS t | 1 | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 | 11
SET ROLE regress_stats_user1 | 1 | 0
SET ROLE regress_stats_user2 | 1 | 0
@@ -682,10 +682,10 @@ 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));
- pg_stat_statements_reset
---------------------------
-
+ WHERE s.query = 'SELECT $1+$2 AS "TWO"' LIMIT 1)) IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
@@ -701,9 +701,9 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
(SELECT r.oid FROM pg_roles AS r WHERE r.rolname = $1), +| |
(SELECT d.oid FROM pg_database As d where datname = current_database()),+| |
(SELECT s.queryid FROM pg_stat_statements AS s +| |
- WHERE s.query = $2 LIMIT $3)) | |
- SELECT pg_stat_statements_reset($1) | 1 | 1
- SELECT pg_stat_statements_reset() | 1 | 1
+ WHERE s.query = $2 LIMIT $3)) IS NOT NULL AS t | |
+ SELECT pg_stat_statements_reset($1) IS NOT NULL AS t | 1 | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 2 | 23
SET ROLE regress_stats_user1 | 1 | 0
SET ROLE regress_stats_user2 | 1 | 0
@@ -712,12 +712,12 @@ 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)
+SELECT pg_stat_statements_reset(0,0,s.queryid) IS NOT NULL AS t
FROM pg_stat_statements AS s WHERE s.query = 'SELECT $1 AS "ONE"';
- pg_stat_statements_reset
---------------------------
-
-
+ t
+---
+ t
+ t
(2 rows)
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
@@ -731,11 +731,11 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
(SELECT r.oid FROM pg_roles AS r WHERE r.rolname = $1), +| |
(SELECT d.oid FROM pg_database As d where datname = current_database()),+| |
(SELECT s.queryid FROM pg_stat_statements AS s +| |
- WHERE s.query = $2 LIMIT $3)) | |
- SELECT pg_stat_statements_reset($1) | 1 | 1
- SELECT pg_stat_statements_reset($1,$2,s.queryid) +| 1 | 2
+ WHERE s.query = $2 LIMIT $3)) IS NOT NULL AS t | |
+ SELECT pg_stat_statements_reset($1) IS NOT NULL AS t | 1 | 1
+ SELECT pg_stat_statements_reset($1,$2,s.queryid) IS NOT NULL AS t +| 1 | 2
FROM pg_stat_statements AS s WHERE s.query = $3 | |
- SELECT pg_stat_statements_reset() | 1 | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 3 | 35
SET ROLE regress_stats_user1 | 1 | 0
SET ROLE regress_stats_user2 | 1 | 0
@@ -744,11 +744,11 @@ 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)
+SELECT pg_stat_statements_reset(r.oid) IS NOT NULL AS t
FROM pg_roles AS r WHERE r.rolname = 'regress_stats_user1';
- pg_stat_statements_reset
---------------------------
-
+ t
+---
+ t
(1 row)
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
@@ -761,12 +761,12 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
(SELECT r.oid FROM pg_roles AS r WHERE r.rolname = $1), +| |
(SELECT d.oid FROM pg_database As d where datname = current_database()),+| |
(SELECT s.queryid FROM pg_stat_statements AS s +| |
- WHERE s.query = $2 LIMIT $3)) | |
- SELECT pg_stat_statements_reset($1) | 1 | 1
- SELECT pg_stat_statements_reset($1,$2,s.queryid) +| 1 | 2
+ WHERE s.query = $2 LIMIT $3)) IS NOT NULL AS t | |
+ SELECT pg_stat_statements_reset($1) IS NOT NULL AS t | 1 | 1
+ SELECT pg_stat_statements_reset($1,$2,s.queryid) IS NOT NULL AS t +| 1 | 2
FROM pg_stat_statements AS s WHERE s.query = $3 | |
- SELECT pg_stat_statements_reset() | 1 | 1
- SELECT pg_stat_statements_reset(r.oid) +| 1 | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 | 1
+ SELECT pg_stat_statements_reset(r.oid) IS NOT NULL AS t +| 1 | 1
FROM pg_roles AS r WHERE r.rolname = $1 | |
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 4 | 46
SET ROLE regress_stats_user2 | 1 | 0
@@ -775,16 +775,16 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
--
-- reset all
--
-SELECT pg_stat_statements_reset(0,0,0);
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset(0,0,0) IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls | rows
------------------------------------------------------------------------------+-------+------
- SELECT pg_stat_statements_reset(0,0,0) | 1 | 1
+ SELECT pg_stat_statements_reset(0,0,0) IS NOT NULL AS t | 1 | 1
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0
(2 rows)
@@ -799,10 +799,10 @@ DROP TABLE pgss_select_into;
--
-- [re]plan counting
--
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
CREATE TABLE test ();
@@ -857,7 +857,7 @@ SELECT query, plans, calls, rows FROM pg_stat_statements
ALTER TABLE test ADD COLUMN x int | 0 | 1 | 0
CREATE TABLE test () | 0 | 1 | 0
SELECT $1 | 3 | 3 | 3
- SELECT pg_stat_statements_reset() | 0 | 1 | 1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t | 0 | 1 | 1
SELECT query, plans, calls, rows FROM pg_stat_statements+| 1 | 0 | 0
WHERE query NOT LIKE $1 ORDER BY query COLLATE "C" | | |
(5 rows)
@@ -874,16 +874,25 @@ SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_sta
--
-- access to pg_stat_statements_info view
--
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT now() AS ref_ts \gset
+SELECT dealloc, stats_reset >= :'ref_ts' AS reset_after_ref FROM pg_stat_statements_info;
+ dealloc | reset_after_ref
+---------+-----------------
+ 0 | f
(1 row)
-SELECT dealloc FROM pg_stat_statements_info;
- dealloc
----------
- 0
+SELECT pg_stat_statements_reset() AS stats_reset_ts \gset
+SELECT dealloc, stats_reset >= :'ref_ts' AS reset_after_ref FROM pg_stat_statements_info;
+ dealloc | reset_after_ref
+---------+-----------------
+ 0 | t
+(1 row)
+
+-- check stats_reset timestamp
+SELECT stats_reset = :'stats_reset_ts' AS reset_ts_match FROM pg_stat_statements_info;
+ reset_ts_match
+----------------
+ t
(1 row)
--
@@ -1077,4 +1086,160 @@ SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
2
(1 row)
+--
+-- statement timestamps
+--
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 1 AS "STMTTS1";
+ STMTTS1
+---------
+ 1
+(1 row)
+
+SELECT now() AS ref_ts \gset
+SELECT 1,2 AS "STMTTS2";
+ ?column? | STMTTS2
+----------+---------
+ 1 | 2
+(1 row)
+
+SELECT stats_since >= :'ref_ts', count(*) FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%'
+GROUP BY stats_since >= :'ref_ts'
+ORDER BY stats_since >= :'ref_ts';
+ ?column? | count
+----------+-------
+ f | 1
+ t | 1
+(2 rows)
+
+SELECT now() AS ref_ts \gset
+SELECT
+ count(*) as total,
+ count(*) FILTER (
+ WHERE min_plan_time + max_plan_time = 0
+ ) as minmax_plan_zero,
+ count(*) FILTER (
+ WHERE min_exec_time + max_exec_time = 0
+ ) as minmax_exec_zero,
+ count(*) FILTER (
+ WHERE minmax_stats_since >= :'ref_ts'
+ ) as minmax_stats_since_after_ref,
+ count(*) FILTER (
+ WHERE stats_since >= :'ref_ts'
+ ) as stats_since_after_ref
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%';
+ total | minmax_plan_zero | minmax_exec_zero | minmax_stats_since_after_ref | stats_since_after_ref
+-------+------------------+------------------+------------------------------+-----------------------
+ 2 | 0 | 0 | 0 | 0
+(1 row)
+
+-- Perform single min/max reset
+SELECT pg_stat_statements_reset(0, 0, queryid, true) AS minmax_reset_ts
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS1%' \gset
+-- check
+SELECT
+ count(*) as total,
+ count(*) FILTER (
+ WHERE min_plan_time + max_plan_time = 0
+ ) as minmax_plan_zero,
+ count(*) FILTER (
+ WHERE min_exec_time + max_exec_time = 0
+ ) as minmax_exec_zero,
+ count(*) FILTER (
+ WHERE minmax_stats_since >= :'ref_ts'
+ ) as minmax_stats_since_after_ref,
+ count(*) FILTER (
+ WHERE stats_since >= :'ref_ts'
+ ) as stats_since_after_ref
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%';
+ total | minmax_plan_zero | minmax_exec_zero | minmax_stats_since_after_ref | stats_since_after_ref
+-------+------------------+------------------+------------------------------+-----------------------
+ 2 | 1 | 1 | 1 | 0
+(1 row)
+
+-- check minmax reset timestamps
+SELECT
+query, minmax_stats_since = :'minmax_reset_ts' AS reset_ts_match
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%'
+ORDER BY query COLLATE "C";
+ query | reset_ts_match
+---------------------------+----------------
+ SELECT $1 AS "STMTTS1" | t
+ SELECT $1,$2 AS "STMTTS2" | f
+(2 rows)
+
+-- check that minmax reset does not set stats_reset
+SELECT
+stats_reset = :'minmax_reset_ts' AS stats_reset_ts_match
+FROM pg_stat_statements_info;
+ stats_reset_ts_match
+----------------------
+ f
+(1 row)
+
+-- Perform common min/max reset
+SELECT pg_stat_statements_reset(0, 0, 0, true) AS minmax_reset_ts \gset
+-- check again
+SELECT
+ count(*) as total,
+ count(*) FILTER (
+ WHERE min_plan_time + max_plan_time = 0
+ ) as minmax_plan_zero,
+ count(*) FILTER (
+ WHERE min_exec_time + max_exec_time = 0
+ ) as minmax_exec_zero,
+ count(*) FILTER (
+ WHERE minmax_stats_since >= :'ref_ts'
+ ) as minmax_ts_after_ref,
+ count(*) FILTER (
+ WHERE minmax_stats_since = :'minmax_reset_ts'
+ ) as minmax_ts_match,
+ count(*) FILTER (
+ WHERE stats_since >= :'ref_ts'
+ ) as stats_since_after_ref
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%';
+ total | minmax_plan_zero | minmax_exec_zero | minmax_ts_after_ref | minmax_ts_match | stats_since_after_ref
+-------+------------------+------------------+---------------------+-----------------+-----------------------
+ 2 | 2 | 2 | 2 | 2 | 0
+(1 row)
+
+-- Execute first query once more to check stats update
+SELECT 1 AS "STMTTS1";
+ STMTTS1
+---------
+ 1
+(1 row)
+
+-- check
+-- we don't check planing times here to be independent of
+-- plan caching approach
+SELECT
+ count(*) as total,
+ count(*) FILTER (
+ WHERE min_exec_time + max_exec_time = 0
+ ) as minmax_exec_zero,
+ count(*) FILTER (
+ WHERE minmax_stats_since >= :'ref_ts'
+ ) as minmax_ts_after_ref,
+ count(*) FILTER (
+ WHERE stats_since >= :'ref_ts'
+ ) as stats_since_after_ref
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%';
+ total | minmax_exec_zero | minmax_ts_after_ref | stats_since_after_ref
+-------+------------------+---------------------+-----------------------
+ 2 | 1 | 2 | 0
+(1 row)
+
DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql b/contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql
new file mode 100644
index 00000000000..9adc1a4d872
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql
@@ -0,0 +1,108 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.10'" to load this file. \quit
+
+/* We need to redefine a view and a function */
+/* First we have to remove them from the extension */
+ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements;
+ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements(boolean);
+ALTER EXTENSION pg_stat_statements DROP FUNCTION
+ pg_stat_statements_reset(Oid, Oid, bigint);
+
+/* Then we can drop them */
+DROP VIEW pg_stat_statements;
+DROP FUNCTION pg_stat_statements(boolean);
+DROP FUNCTION pg_stat_statements_reset(Oid, Oid, bigint);
+
+/* Now redefine */
+CREATE FUNCTION pg_stat_statements(IN showtext boolean,
+ OUT userid oid,
+ OUT dbid oid,
+ OUT toplevel bool,
+ OUT queryid bigint,
+ OUT query text,
+ OUT plans int8,
+ OUT total_plan_time float8,
+ OUT min_plan_time float8,
+ OUT max_plan_time float8,
+ OUT mean_plan_time float8,
+ OUT stddev_plan_time float8,
+ OUT calls int8,
+ OUT total_exec_time float8,
+ OUT min_exec_time float8,
+ OUT max_exec_time float8,
+ OUT mean_exec_time float8,
+ OUT stddev_exec_time float8,
+ OUT rows int8,
+ OUT shared_blks_hit int8,
+ OUT shared_blks_read int8,
+ OUT shared_blks_dirtied int8,
+ OUT shared_blks_written int8,
+ OUT local_blks_hit int8,
+ OUT local_blks_read int8,
+ OUT local_blks_dirtied int8,
+ OUT local_blks_written int8,
+ OUT temp_blks_read int8,
+ OUT temp_blks_written int8,
+ OUT blk_read_time float8,
+ OUT blk_write_time float8,
+ OUT wal_records int8,
+ OUT wal_fpi int8,
+ OUT wal_bytes numeric,
+ OUT stats_since timestamp with time zone,
+ OUT minmax_stats_since timestamp with time zone
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_stat_statements_1_10'
+LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
+
+CREATE VIEW pg_stat_statements AS
+ SELECT
+ userid,
+ dbid,
+ toplevel,
+ queryid,
+ query,
+ plans,
+ total_plan_time,
+ min_plan_time,
+ max_plan_time,
+ mean_plan_time,
+ stddev_plan_time,
+ calls,
+ total_exec_time,
+ min_exec_time,
+ max_exec_time,
+ mean_exec_time,
+ stddev_exec_time,
+ rows,
+ shared_blks_hit,
+ shared_blks_read,
+ shared_blks_dirtied,
+ shared_blks_written,
+ local_blks_hit,
+ local_blks_read,
+ local_blks_dirtied,
+ local_blks_written,
+ temp_blks_read,
+ temp_blks_written,
+ blk_read_time,
+ blk_write_time,
+ wal_records,
+ wal_fpi,
+ wal_bytes,
+ stats_since,
+ minmax_stats_since
+ FROM pg_stat_statements(true);
+
+CREATE FUNCTION pg_stat_statements_reset(IN userid Oid DEFAULT 0,
+ IN dbid Oid DEFAULT 0,
+ IN queryid bigint DEFAULT 0,
+ IN minmax_only boolean DEFAULT false
+)
+RETURNS timestamp with time zone
+AS 'MODULE_PATHNAME', 'pg_stat_statements_reset_1_10'
+LANGUAGE C STRICT PARALLEL SAFE;
+
+GRANT SELECT ON pg_stat_statements TO PUBLIC;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 55786ae84f2..441841f2851 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -88,7 +88,7 @@ PG_MODULE_MAGIC;
#define PGSS_TEXT_FILE PG_STAT_TMP_DIR "/pgss_query_texts.stat"
/* Magic number identifying the stats file format */
-static const uint32 PGSS_FILE_HEADER = 0x20201227;
+static const uint32 PGSS_FILE_HEADER = 0x20210322;
/* PostgreSQL major version number, changes in which invalidate all entries */
static const uint32 PGSS_PG_MAJOR_VERSION = PG_VERSION_NUM / 100;
@@ -121,7 +121,8 @@ typedef enum pgssVersion
PGSS_V1_2,
PGSS_V1_3,
PGSS_V1_8,
- PGSS_V1_9
+ PGSS_V1_9,
+ PGSS_V1_10
} pgssVersion;
typedef enum pgssStoreKind
@@ -165,9 +166,9 @@ typedef struct Counters
double total_time[PGSS_NUMKIND]; /* total planning/execution time,
* in msec */
double min_time[PGSS_NUMKIND]; /* minimum planning/execution time in
- * msec */
+ * msec since min/max reset */
double max_time[PGSS_NUMKIND]; /* maximum planning/execution time in
- * msec */
+ * msec since min/max reset */
double mean_time[PGSS_NUMKIND]; /* mean planning/execution time in
* msec */
double sum_var_time[PGSS_NUMKIND]; /* sum of variances in
@@ -209,12 +210,14 @@ typedef struct pgssGlobalStats
*/
typedef struct pgssEntry
{
- pgssHashKey key; /* hash key of entry - MUST BE FIRST */
- Counters counters; /* the statistics for this query */
- Size query_offset; /* query text offset in external file */
- int query_len; /* # of valid bytes in query string, or -1 */
- int encoding; /* query text encoding */
- slock_t mutex; /* protects the counters only */
+ pgssHashKey key; /* hash key of entry - MUST BE FIRST */
+ Counters counters; /* the statistics for this query */
+ Size query_offset; /* query text offset in external file */
+ int query_len; /* # of valid bytes in query string, or -1 */
+ int encoding; /* query text encoding */
+ TimestampTz stats_since; /* timestamp of entry allocation */
+ TimestampTz minmax_stats_since; /* timestamp of last min/max values reset */
+ slock_t mutex; /* protects the counters only */
} pgssEntry;
/*
@@ -298,10 +301,12 @@ void _PG_fini(void);
PG_FUNCTION_INFO_V1(pg_stat_statements_reset);
PG_FUNCTION_INFO_V1(pg_stat_statements_reset_1_7);
+PG_FUNCTION_INFO_V1(pg_stat_statements_reset_1_10);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_2);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_3);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_8);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_9);
+PG_FUNCTION_INFO_V1(pg_stat_statements_1_10);
PG_FUNCTION_INFO_V1(pg_stat_statements);
PG_FUNCTION_INFO_V1(pg_stat_statements_info);
@@ -345,7 +350,7 @@ static char *qtext_fetch(Size query_offset, int query_len,
char *buffer, Size buffer_size);
static bool need_gc_qtexts(void);
static void gc_qtexts(void);
-static void entry_reset(Oid userid, Oid dbid, uint64 queryid);
+static TimestampTz entry_reset(Oid userid, Oid dbid, uint64 queryid, bool minmax_only);
static char *generate_normalized_query(JumbleState *jstate, const char *query,
int query_loc, int *query_len_p);
static void fill_in_constant_lengths(JumbleState *jstate, const char *query,
@@ -649,6 +654,8 @@ pgss_shmem_startup(void)
/* copy in the actual stats */
entry->counters = temp.counters;
+ entry->stats_since = temp.stats_since;
+ entry->minmax_stats_since = temp.minmax_stats_since;
}
/* Read global statistics for pg_stat_statements */
@@ -1350,11 +1357,23 @@ pgss_store(const char *query, uint64 queryId,
e->counters.sum_var_time[kind] +=
(total_time - old_mean) * (total_time - e->counters.mean_time[kind]);
- /* calculate min and max time */
- if (e->counters.min_time[kind] > total_time)
+ /*
+ * Calculate min and max time. min = 0 and max = 0
+ * means that the min/max statistics were reset
+ */
+ if (e->counters.min_time[kind] == 0
+ && e->counters.max_time[kind] == 0)
+ {
e->counters.min_time[kind] = total_time;
- if (e->counters.max_time[kind] < total_time)
e->counters.max_time[kind] = total_time;
+ }
+ else
+ {
+ if (e->counters.min_time[kind] > total_time)
+ e->counters.min_time[kind] = total_time;
+ if (e->counters.max_time[kind] < total_time)
+ e->counters.max_time[kind] = total_time;
+ }
}
e->counters.rows += rows;
e->counters.shared_blks_hit += bufusage->shared_blks_hit;
@@ -1399,18 +1418,34 @@ pg_stat_statements_reset_1_7(PG_FUNCTION_ARGS)
dbid = PG_GETARG_OID(1);
queryid = (uint64) PG_GETARG_INT64(2);
- entry_reset(userid, dbid, queryid);
+ entry_reset(userid, dbid, queryid, false);
PG_RETURN_VOID();
}
+Datum
+pg_stat_statements_reset_1_10(PG_FUNCTION_ARGS)
+{
+ Oid userid;
+ Oid dbid;
+ uint64 queryid;
+ bool minmax_only;
+
+ userid = PG_GETARG_OID(0);
+ dbid = PG_GETARG_OID(1);
+ queryid = (uint64) PG_GETARG_INT64(2);
+ minmax_only = PG_GETARG_BOOL(3);
+
+ PG_RETURN_TIMESTAMPTZ(entry_reset(userid, dbid, queryid, minmax_only));
+}
+
/*
* Reset statement statistics.
*/
Datum
pg_stat_statements_reset(PG_FUNCTION_ARGS)
{
- entry_reset(0, 0, 0);
+ entry_reset(0, 0, 0, false);
PG_RETURN_VOID();
}
@@ -1422,7 +1457,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
#define PG_STAT_STATEMENTS_COLS_V1_3 23
#define PG_STAT_STATEMENTS_COLS_V1_8 32
#define PG_STAT_STATEMENTS_COLS_V1_9 33
-#define PG_STAT_STATEMENTS_COLS 33 /* maximum of above */
+#define PG_STAT_STATEMENTS_COLS_V1_10 35
+#define PG_STAT_STATEMENTS_COLS 35 /* maximum of above */
/*
* Retrieve statement statistics.
@@ -1434,6 +1470,16 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
* expected API version is identified by embedding it in the C name of the
* function. Unfortunately we weren't bright enough to do that for 1.1.
*/
+Datum
+pg_stat_statements_1_10(PG_FUNCTION_ARGS)
+{
+ bool showtext = PG_GETARG_BOOL(0);
+
+ pg_stat_statements_internal(fcinfo, PGSS_V1_10, showtext);
+
+ return (Datum) 0;
+}
+
Datum
pg_stat_statements_1_9(PG_FUNCTION_ARGS)
{
@@ -1547,6 +1593,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
if (api_version != PGSS_V1_9)
elog(ERROR, "incorrect number of output arguments");
break;
+ case PG_STAT_STATEMENTS_COLS_V1_10:
+ if (api_version != PGSS_V1_10)
+ elog(ERROR, "incorrect number of output arguments");
+ break;
default:
elog(ERROR, "incorrect number of output arguments");
}
@@ -1625,6 +1675,8 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
Counters tmp;
double stddev;
int64 queryid = entry->key.queryid;
+ TimestampTz stats_since;
+ TimestampTz minmax_stats_since;
memset(values, 0, sizeof(values));
memset(nulls, 0, sizeof(nulls));
@@ -1693,6 +1745,8 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
SpinLockAcquire(&e->mutex);
tmp = e->counters;
+ stats_since = e->stats_since;
+ minmax_stats_since = e->minmax_stats_since;
SpinLockRelease(&e->mutex);
}
@@ -1764,6 +1818,11 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
Int32GetDatum(-1));
values[i++] = wal_bytes;
}
+ if (api_version >= PGSS_V1_10)
+ {
+ values[i++] = TimestampTzGetDatum(stats_since);
+ values[i++] = TimestampTzGetDatum(minmax_stats_since);
+ }
Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 :
api_version == PGSS_V1_1 ? PG_STAT_STATEMENTS_COLS_V1_1 :
@@ -1771,6 +1830,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
api_version == PGSS_V1_3 ? PG_STAT_STATEMENTS_COLS_V1_3 :
api_version == PGSS_V1_8 ? PG_STAT_STATEMENTS_COLS_V1_8 :
api_version == PGSS_V1_9 ? PG_STAT_STATEMENTS_COLS_V1_9 :
+ api_version == PGSS_V1_10 ? PG_STAT_STATEMENTS_COLS_V1_10 :
-1 /* fail if you forget to update this assert */ ));
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
@@ -1884,6 +1944,8 @@ entry_alloc(pgssHashKey *key, Size query_offset, int query_len, int encoding,
entry->query_offset = query_offset;
entry->query_len = query_len;
entry->encoding = encoding;
+ entry->stats_since = GetCurrentTimestamp();
+ entry->minmax_stats_since = entry->stats_since;
}
return entry;
@@ -2430,11 +2492,30 @@ gc_fail:
record_gc_qtexts();
}
+#define SINGLE_ENTRY_RESET() \
+if (entry) { \
+ if (minmax_only) { \
+ /* When requested reset only min/max statistics of an entry */ \
+ for (int kind = 0; kind < PGSS_NUMKIND; kind++) \
+ { \
+ entry->counters.max_time[kind] = 0; \
+ entry->counters.min_time[kind] = 0; \
+ } \
+ entry->minmax_stats_since = stats_reset; \
+ } \
+ else \
+ { \
+ /* Remove the key otherwise */ \
+ hash_search(pgss_hash, &entry->key, HASH_REMOVE, NULL); \
+ num_remove++; \
+ } \
+}
+
/*
- * Release entries corresponding to parameters passed.
+ * Reset entries corresponding to parameters passed.
*/
-static void
-entry_reset(Oid userid, Oid dbid, uint64 queryid)
+static TimestampTz
+entry_reset(Oid userid, Oid dbid, uint64 queryid, bool minmax_only)
{
HASH_SEQ_STATUS hash_seq;
pgssEntry *entry;
@@ -2442,6 +2523,7 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid)
long num_entries;
long num_remove = 0;
pgssHashKey key;
+ TimestampTz stats_reset;
if (!pgss || !pgss_hash)
ereport(ERROR,
@@ -2451,6 +2533,8 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid)
LWLockAcquire(pgss->lock, LW_EXCLUSIVE);
num_entries = hash_get_num_entries(pgss_hash);
+ stats_reset = GetCurrentTimestamp();
+
if (userid != 0 && dbid != 0 && queryid != UINT64CONST(0))
{
/* If all the parameters are available, use the fast path. */
@@ -2459,23 +2543,21 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid)
key.dbid = dbid;
key.queryid = queryid;
- /* Remove the key if it exists, starting with the top-level entry */
+ /* Reset is started from nested-level */
key.toplevel = false;
- entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_REMOVE, NULL);
- if (entry) /* found */
- num_remove++;
+ entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_FIND, NULL);
- /* Also remove entries for top level statements */
+ SINGLE_ENTRY_RESET();
+
+ /* Reset entries for top level statements */
key.toplevel = true;
+ entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_FIND, NULL);
- /* Remove the key if exists */
- entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_REMOVE, NULL);
- if (entry) /* found */
- num_remove++;
+ SINGLE_ENTRY_RESET();
}
else if (userid != 0 || dbid != 0 || queryid != UINT64CONST(0))
{
- /* Remove entries corresponding to valid parameters. */
+ /* Reset entries corresponding to valid parameters. */
hash_seq_init(&hash_seq, pgss_hash);
while ((entry = hash_seq_search(&hash_seq)) != NULL)
{
@@ -2483,8 +2565,7 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid)
(!dbid || entry->key.dbid == dbid) &&
(!queryid || entry->key.queryid == queryid))
{
- hash_search(pgss_hash, &entry->key, HASH_REMOVE, NULL);
- num_remove++;
+ SINGLE_ENTRY_RESET();
}
}
}
@@ -2494,8 +2575,7 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid)
hash_seq_init(&hash_seq, pgss_hash);
while ((entry = hash_seq_search(&hash_seq)) != NULL)
{
- hash_search(pgss_hash, &entry->key, HASH_REMOVE, NULL);
- num_remove++;
+ SINGLE_ENTRY_RESET();
}
}
@@ -2509,7 +2589,6 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid)
*/
{
volatile pgssSharedState *s = (volatile pgssSharedState *) pgss;
- TimestampTz stats_reset = GetCurrentTimestamp();
SpinLockAcquire(&s->mutex);
s->stats.dealloc = 0;
@@ -2547,6 +2626,8 @@ done:
release_lock:
LWLockRelease(pgss->lock);
+
+ return stats_reset;
}
/*
diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control
index 2f1ce6ed507..0747e481383 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.control
+++ b/contrib/pg_stat_statements/pg_stat_statements.control
@@ -1,5 +1,5 @@
# pg_stat_statements extension
comment = 'track planning and execution statistics of all SQL statements executed'
-default_version = '1.9'
+default_version = '1.10'
module_pathname = '$libdir/pg_stat_statements'
relocatable = true
diff --git a/contrib/pg_stat_statements/sql/oldextversions.sql b/contrib/pg_stat_statements/sql/oldextversions.sql
index f2e822acd3e..c2af29866ba 100644
--- a/contrib/pg_stat_statements/sql/oldextversions.sql
+++ b/contrib/pg_stat_statements/sql/oldextversions.sql
@@ -36,4 +36,12 @@ AlTER EXTENSION pg_stat_statements UPDATE TO '1.8';
\d pg_stat_statements
SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
+ALTER EXTENSION pg_stat_statements UPDATE TO '1.9';
+\d pg_stat_statements
+\d pg_stat_statements_info
+SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
+SET SESSION AUTHORIZATION pg_read_all_stats;
+SELECT pg_stat_statements_reset();
+RESET SESSION AUTHORIZATION;
+
DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index dffd2c8c187..9d294a053ac 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -5,7 +5,7 @@ CREATE EXTENSION pg_stat_statements;
--
SET pg_stat_statements.track_utility = FALSE;
SET pg_stat_statements.track_planning = TRUE;
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
SELECT 1 AS "int";
@@ -57,7 +57,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
--
-- CRUD: INSERT SELECT UPDATE DELETE on test table
--
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- utility "create table" should not be shown
CREATE TEMP TABLE test (a int, b char(20));
@@ -105,7 +105,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
--
-- INSERT, UPDATE, DELETE on test table to validate WAL generation metrics
--
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- utility "create table" should not be shown
CREATE TABLE pgss_test (a int, b char(20));
@@ -129,7 +129,7 @@ FROM pg_stat_statements ORDER BY query COLLATE "C";
-- pg_stat_statements.track = none
--
SET pg_stat_statements.track = 'none';
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
SELECT 1 AS "one";
SELECT 1 + 1 AS "two";
@@ -140,7 +140,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-- pg_stat_statements.track = top
--
SET pg_stat_statements.track = 'top';
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
DO LANGUAGE plpgsql $$
BEGIN
@@ -174,7 +174,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-- pg_stat_statements.track = all
--
SET pg_stat_statements.track = 'all';
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- we drop and recreate the functions to avoid any caching funnies
DROP FUNCTION PLUS_ONE(INTEGER);
@@ -207,7 +207,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
CREATE TABLE pgss_a (id integer PRIMARY KEY);
CREATE TABLE pgss_b (id integer PRIMARY KEY, a_id integer REFERENCES pgss_a);
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- control query
SELECT * FROM pgss_a JOIN pgss_b ON pgss_b.a_id = pgss_a.id;
@@ -236,7 +236,7 @@ DROP TABLE pgss_a, pgss_b CASCADE;
-- utility commands
--
SET pg_stat_statements.track_utility = TRUE;
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
SELECT 1;
CREATE INDEX test_b ON test(b);
@@ -255,7 +255,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW,
-- REFRESH MATERIALIZED VIEW and SELECT INTO
--
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a;
SELECT generate_series(1, 10) c INTO pgss_select_into;
@@ -278,7 +278,7 @@ SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE
--
-- Track user activity and reset them
--
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
CREATE ROLE regress_stats_user1;
CREATE ROLE regress_stats_user2;
@@ -299,7 +299,7 @@ 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 pg_stat_statements_reset(NULL) IS NOT NULL AS t;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
--
@@ -310,27 +310,27 @@ 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));
+ WHERE s.query = 'SELECT $1+$2 AS "TWO"' LIMIT 1)) IS NOT NULL AS t;
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)
+SELECT pg_stat_statements_reset(0,0,s.queryid) IS NOT NULL AS t
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)
+SELECT pg_stat_statements_reset(r.oid) IS NOT NULL AS t
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 pg_stat_statements_reset(0,0,0) IS NOT NULL AS t;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
--
@@ -345,7 +345,7 @@ DROP TABLE pgss_select_into;
--
-- [re]plan counting
--
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
CREATE TABLE test ();
PREPARE prep1 AS SELECT COUNT(*) FROM test;
EXECUTE prep1;
@@ -366,8 +366,12 @@ SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_sta
--
-- access to pg_stat_statements_info view
--
-SELECT pg_stat_statements_reset();
-SELECT dealloc FROM pg_stat_statements_info;
+SELECT now() AS ref_ts \gset
+SELECT dealloc, stats_reset >= :'ref_ts' AS reset_after_ref FROM pg_stat_statements_info;
+SELECT pg_stat_statements_reset() AS stats_reset_ts \gset
+SELECT dealloc, stats_reset >= :'ref_ts' AS reset_after_ref FROM pg_stat_statements_info;
+-- check stats_reset timestamp
+SELECT stats_reset = :'stats_reset_ts' AS reset_ts_match FROM pg_stat_statements_info;
--
-- top level handling
@@ -442,4 +446,113 @@ SELECT (
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
+--
+-- statement timestamps
+--
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT 1 AS "STMTTS1";
+SELECT now() AS ref_ts \gset
+SELECT 1,2 AS "STMTTS2";
+SELECT stats_since >= :'ref_ts', count(*) FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%'
+GROUP BY stats_since >= :'ref_ts'
+ORDER BY stats_since >= :'ref_ts';
+
+SELECT now() AS ref_ts \gset
+SELECT
+ count(*) as total,
+ count(*) FILTER (
+ WHERE min_plan_time + max_plan_time = 0
+ ) as minmax_plan_zero,
+ count(*) FILTER (
+ WHERE min_exec_time + max_exec_time = 0
+ ) as minmax_exec_zero,
+ count(*) FILTER (
+ WHERE minmax_stats_since >= :'ref_ts'
+ ) as minmax_stats_since_after_ref,
+ count(*) FILTER (
+ WHERE stats_since >= :'ref_ts'
+ ) as stats_since_after_ref
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%';
+
+-- Perform single min/max reset
+SELECT pg_stat_statements_reset(0, 0, queryid, true) AS minmax_reset_ts
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS1%' \gset
+
+-- check
+SELECT
+ count(*) as total,
+ count(*) FILTER (
+ WHERE min_plan_time + max_plan_time = 0
+ ) as minmax_plan_zero,
+ count(*) FILTER (
+ WHERE min_exec_time + max_exec_time = 0
+ ) as minmax_exec_zero,
+ count(*) FILTER (
+ WHERE minmax_stats_since >= :'ref_ts'
+ ) as minmax_stats_since_after_ref,
+ count(*) FILTER (
+ WHERE stats_since >= :'ref_ts'
+ ) as stats_since_after_ref
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%';
+
+-- check minmax reset timestamps
+SELECT
+query, minmax_stats_since = :'minmax_reset_ts' AS reset_ts_match
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%'
+ORDER BY query COLLATE "C";
+
+-- check that minmax reset does not set stats_reset
+SELECT
+stats_reset = :'minmax_reset_ts' AS stats_reset_ts_match
+FROM pg_stat_statements_info;
+
+-- Perform common min/max reset
+SELECT pg_stat_statements_reset(0, 0, 0, true) AS minmax_reset_ts \gset
+
+-- check again
+SELECT
+ count(*) as total,
+ count(*) FILTER (
+ WHERE min_plan_time + max_plan_time = 0
+ ) as minmax_plan_zero,
+ count(*) FILTER (
+ WHERE min_exec_time + max_exec_time = 0
+ ) as minmax_exec_zero,
+ count(*) FILTER (
+ WHERE minmax_stats_since >= :'ref_ts'
+ ) as minmax_ts_after_ref,
+ count(*) FILTER (
+ WHERE minmax_stats_since = :'minmax_reset_ts'
+ ) as minmax_ts_match,
+ count(*) FILTER (
+ WHERE stats_since >= :'ref_ts'
+ ) as stats_since_after_ref
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%';
+
+-- Execute first query once more to check stats update
+SELECT 1 AS "STMTTS1";
+
+-- check
+-- we don't check planing times here to be independent of
+-- plan caching approach
+SELECT
+ count(*) as total,
+ count(*) FILTER (
+ WHERE min_exec_time + max_exec_time = 0
+ ) as minmax_exec_zero,
+ count(*) FILTER (
+ WHERE minmax_stats_since >= :'ref_ts'
+ ) as minmax_ts_after_ref,
+ count(*) FILTER (
+ WHERE stats_since >= :'ref_ts'
+ ) as stats_since_after_ref
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%';
+
DROP EXTENSION pg_stat_statements;
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index 3a7e36bd13c..f4b4697dc93 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -140,9 +140,12 @@
<structfield>min_plan_time</structfield> <type>double precision</type>
</para>
<para>
- Minimum time spent planning the statement, in milliseconds
- (if <varname>pg_stat_statements.track_planning</varname> is enabled,
- otherwise zero)
+ Minimum time spent planning the statement, in milliseconds.
+ This field will be zero if <varname>pg_stat_statements.track_planning</varname>
+ is disabled, or if the counter has been reset using the
+ <function>pg_stat_statements_reset</function> function with the
+ <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
+ and never been planned since.
</para></entry>
</row>
@@ -151,9 +154,12 @@
<structfield>max_plan_time</structfield> <type>double precision</type>
</para>
<para>
- Maximum time spent planning the statement, in milliseconds
- (if <varname>pg_stat_statements.track_planning</varname> is enabled,
- otherwise zero)
+ Maximum time spent planning the statement, in milliseconds.
+ This field will be zero if <varname>pg_stat_statements.track_planning</varname>
+ is disabled, or if the counter has been reset using the
+ <function>pg_stat_statements_reset</function> function with the
+ <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
+ and never been planned since.
</para></entry>
</row>
@@ -203,7 +209,11 @@
<structfield>min_exec_time</structfield> <type>double precision</type>
</para>
<para>
- Minimum time spent executing the statement, in milliseconds
+ Minimum time spent executing the statement, in milliseconds,
+ this field will contain zero until this statement
+ is executed first time after reset performed by the
+ <function>pg_stat_statements_reset</function> function with the
+ <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
</para></entry>
</row>
@@ -212,7 +222,11 @@
<structfield>max_exec_time</structfield> <type>double precision</type>
</para>
<para>
- Maximum time spent executing the statement, in milliseconds
+ Maximum time spent executing the statement, in milliseconds,
+ this field will contain zero until this statement
+ is executed first time after reset performed by the
+ <function>pg_stat_statements_reset</function> function with the
+ <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
</para></entry>
</row>
@@ -379,6 +393,25 @@
Total amount of WAL generated by the statement in bytes
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>stats_since</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Time at which statistics gathering started for this statement
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>minmax_stats_since</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Time at which min/max statistics gathering started for this
+ statement
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -570,7 +603,8 @@
<variablelist>
<varlistentry>
<term>
- <function>pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void</function>
+ <function>pg_stat_statements_reset(userid Oid, dbid Oid, queryid
+ bigint, minmax_only boolean) returns timestamp with time zone</function>
<indexterm>
<primary>pg_stat_statements_reset</primary>
</indexterm>
@@ -589,6 +623,20 @@
If all statistics in the <filename>pg_stat_statements</filename>
view are discarded, it will also reset the statistics in the
<structname>pg_stat_statements_info</structname> view.
+ When <structfield>minmax_only</structfield> is <literal>true</literal> only the
+ values of minimun and maximum planning and execution time will be reset (i.e.
+ <structfield>min_plan_time</structfield>, <structfield>max_plan_time</structfield>,
+ <structfield>min_exec_time</structfield> and <structfield>max_exec_time</structfield>
+ fields). The default value for <structfield>minmax_only</structfield> parameter is
+ <literal>false</literal>. Time of last min/max reset performed is shown in
+ <structfield>minmax_stats_since</structfield> field of the
+ <structname>pg_stat_statements</structname> view.
+ This function returns the time of a reset. This time is saved to
+ <structfield>stats_reset</structfield> field of
+ <structname>pg_stat_statements_info</structname> view or to
+ <structfield>minmax_stats_since</structfield> field of the
+ <structname>pg_stat_statements</structname> view if the corresponding reset was
+ actually performed.
By default, this function can only be executed by superusers.
Access may be granted to others using <command>GRANT</command>.
</para>
--
2.31.1
