v16-0001-pg_stat_statements-Track-statement-entry-timestamp.patch
text/x-patch
Filename: v16-0001-pg_stat_statements-Track-statement-entry-timestamp.patch
Type: text/x-patch
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v16-0001
Subject: pg_stat_statements: Track statement entry timestamp
| File | + | − |
|---|---|---|
| contrib/pg_stat_statements/expected/oldextversions.out | 70 | 0 |
| contrib/pg_stat_statements/expected/pg_stat_statements.out | 263 | 98 |
| contrib/pg_stat_statements/Makefile | 1 | 1 |
| contrib/pg_stat_statements/meson.build | 1 | 0 |
| contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql | 81 | 0 |
| contrib/pg_stat_statements/pg_stat_statements.c | 110 | 29 |
| contrib/pg_stat_statements/pg_stat_statements.control | 1 | 1 |
| contrib/pg_stat_statements/sql/oldextversions.sql | 7 | 0 |
| contrib/pg_stat_statements/sql/pg_stat_statements.sql | 131 | 18 |
| doc/src/sgml/pgstatstatements.sgml | 57 | 9 |
From 94784bccd48a83cba58d6017253d0b8f051e159c Mon Sep 17 00:00:00 2001
From: Andrei Zubkov <zubkov@moonset.ru>
Date: Thu, 26 Jan 2023 13:18:11 +0300
Subject: [PATCH] pg_stat_statements: Track statement entry timestamp
This patch adds stats_since and minmax_stats_since columns to the
pg_stat_statements view and pg_stat_statements() function. The new min/max reset
mode for the pg_stat_stetments_reset() function is controlled by the
parameter minmax_only.
stat_since 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
stored again between samples.
Such sampling solution could derive any pg_stat_statements statistic values for
an interval between two samples with the exception of all min/max statistics. To
address this issue this patch adds the ability to reset min/max statistics
independently of the 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 now returns the timestamp of a reset as a
result.
Discussion:
https://www.postgresql.org/message-id/flat/72e80e7b160a6eb189df9ef6f068cce3765d37f8.camel%40moonset.ru
---
contrib/pg_stat_statements/Makefile | 2 +-
.../expected/oldextversions.out | 70 ++++
.../expected/pg_stat_statements.out | 361 +++++++++++++-----
contrib/pg_stat_statements/meson.build | 1 +
.../pg_stat_statements--1.10--1.11.sql | 81 ++++
.../pg_stat_statements/pg_stat_statements.c | 139 +++++--
.../pg_stat_statements.control | 2 +-
.../pg_stat_statements/sql/oldextversions.sql | 7 +
.../sql/pg_stat_statements.sql | 149 +++++++-
doc/src/sgml/pgstatstatements.sgml | 66 +++-
10 files changed, 722 insertions(+), 156 deletions(-)
create mode 100644 contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index edc40c8bbfb..0afb9060fa1 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -6,7 +6,7 @@ OBJS = \
pg_stat_statements.o
EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.4.sql \
+DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.10--1.11.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 \
diff --git a/contrib/pg_stat_statements/expected/oldextversions.out b/contrib/pg_stat_statements/expected/oldextversions.out
index efb2049ecff..1e1cc11a8e2 100644
--- a/contrib/pg_stat_statements/expected/oldextversions.out
+++ b/contrib/pg_stat_statements/expected/oldextversions.out
@@ -250,4 +250,74 @@ SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
t
(1 row)
+-- New functions and views for pg_stat_statements in 1.11
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.11';
+\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 | | |
+ temp_blk_read_time | double precision | | |
+ temp_blk_write_time | double precision | | |
+ wal_records | bigint | | |
+ wal_fpi | bigint | | |
+ wal_bytes | numeric | | |
+ jit_functions | bigint | | |
+ jit_generation_time | double precision | | |
+ jit_inlining_count | bigint | | |
+ jit_inlining_time | double precision | | |
+ jit_optimization_count | bigint | | |
+ jit_optimization_time | double precision | | |
+ jit_emission_count | bigint | | |
+ jit_emission_time | double precision | | |
+ stats_since | timestamp with time zone | | |
+ minmax_stats_since | timestamp with time zone | | |
+
+SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
+ has_data
+----------
+ t
+(1 row)
+
+-- New parameter minmax_only of pg_stat_statements_reset function
+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, minmax_only boolean DEFAULT false)+
+ RETURNS timestamp with time zone +
+ LANGUAGE c +
+ PARALLEL SAFE STRICT +
+ AS '$libdir/pg_stat_statements', $function$pg_stat_statements_reset_1_11$function$ +
+
+(1 row)
+
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 9ac5c87c3a2..1e572f1a9e3 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";
@@ -134,7 +134,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) +| |
@@ -146,10 +146,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
@@ -270,7 +270,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
@@ -279,10 +279,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
@@ -305,7 +305,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 | t
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, +| | | | |
@@ -319,10 +319,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";
@@ -346,10 +346,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 $$
@@ -399,7 +399,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)
@@ -407,10 +407,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
@@ -458,7 +458,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)
@@ -467,10 +467,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
@@ -546,7 +546,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;
@@ -554,10 +554,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;
@@ -588,7 +588,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)
@@ -597,10 +597,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;
@@ -653,17 +653,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;
@@ -706,7 +706,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
@@ -715,10 +715,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";
@@ -731,8 +731,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
@@ -746,10 +746,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";
@@ -765,9 +765,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
@@ -776,12 +776,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";
@@ -795,11 +795,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
@@ -808,11 +808,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";
@@ -825,12 +825,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
@@ -839,16 +839,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)
@@ -863,10 +863,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 ();
@@ -921,7 +921,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)
@@ -938,16 +938,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)
--
@@ -1141,4 +1150,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/meson.build b/contrib/pg_stat_statements/meson.build
index 508b53b4a27..ba7bd83bc99 100644
--- a/contrib/pg_stat_statements/meson.build
+++ b/contrib/pg_stat_statements/meson.build
@@ -21,6 +21,7 @@ contrib_targets += pg_stat_statements
install_data(
'pg_stat_statements.control',
'pg_stat_statements--1.4.sql',
+ 'pg_stat_statements--1.10--1.11.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',
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql b/contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql
new file mode 100644
index 00000000000..782029727cc
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql
@@ -0,0 +1,81 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.11'" to load this file. \quit
+
+/* 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 temp_blk_read_time float8,
+ OUT temp_blk_write_time float8,
+ OUT wal_records int8,
+ OUT wal_fpi int8,
+ OUT wal_bytes numeric,
+ OUT jit_functions int8,
+ OUT jit_generation_time float8,
+ OUT jit_inlining_count int8,
+ OUT jit_inlining_time float8,
+ OUT jit_optimization_count int8,
+ OUT jit_optimization_time float8,
+ OUT jit_emission_count int8,
+ OUT jit_emission_time float8,
+ 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_11'
+LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
+
+CREATE VIEW pg_stat_statements AS
+ SELECT * 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_11'
+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 ad1fe444969..316bec69e0a 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -84,7 +84,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 = 0x20220408;
+static const uint32 PGSS_FILE_HEADER = 0x20230124;
/* PostgreSQL major version number, changes in which invalidate all entries */
static const uint32 PGSS_PG_MAJOR_VERSION = PG_VERSION_NUM / 100;
@@ -118,7 +118,8 @@ typedef enum pgssVersion
PGSS_V1_3,
PGSS_V1_8,
PGSS_V1_9,
- PGSS_V1_10
+ PGSS_V1_10,
+ PGSS_V1_11
} pgssVersion;
typedef enum pgssStoreKind
@@ -162,9 +163,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
@@ -225,6 +226,8 @@ typedef struct pgssEntry
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;
@@ -308,11 +311,13 @@ static bool pgss_save = true; /* whether to save stats across shutdown */
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_11);
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_1_11);
PG_FUNCTION_INFO_V1(pg_stat_statements);
PG_FUNCTION_INFO_V1(pg_stat_statements_info);
@@ -358,7 +363,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,
@@ -653,6 +658,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 */
@@ -1370,11 +1377,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;
@@ -1438,18 +1457,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_11(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();
}
@@ -1462,7 +1497,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
#define PG_STAT_STATEMENTS_COLS_V1_8 32
#define PG_STAT_STATEMENTS_COLS_V1_9 33
#define PG_STAT_STATEMENTS_COLS_V1_10 43
-#define PG_STAT_STATEMENTS_COLS 43 /* maximum of above */
+#define PG_STAT_STATEMENTS_COLS_V1_11 45
+#define PG_STAT_STATEMENTS_COLS 45 /* maximum of above */
/*
* Retrieve statement statistics.
@@ -1474,6 +1510,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_11(PG_FUNCTION_ARGS)
+{
+ bool showtext = PG_GETARG_BOOL(0);
+
+ pg_stat_statements_internal(fcinfo, PGSS_V1_11, showtext);
+
+ return (Datum) 0;
+}
+
Datum
pg_stat_statements_1_10(PG_FUNCTION_ARGS)
{
@@ -1604,6 +1650,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
if (api_version != PGSS_V1_10)
elog(ERROR, "incorrect number of output arguments");
break;
+ case PG_STAT_STATEMENTS_COLS_V1_11:
+ if (api_version != PGSS_V1_11)
+ elog(ERROR, "incorrect number of output arguments");
+ break;
default:
elog(ERROR, "incorrect number of output arguments");
}
@@ -1681,6 +1731,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));
@@ -1749,6 +1801,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);
}
@@ -1836,6 +1890,11 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
values[i++] = Int64GetDatumFast(tmp.jit_emission_count);
values[i++] = Float8GetDatumFast(tmp.jit_emission_time);
}
+ if (api_version >= PGSS_V1_11)
+ {
+ 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 :
@@ -1844,6 +1903,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
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 :
+ api_version == PGSS_V1_11 ? PG_STAT_STATEMENTS_COLS_V1_11 :
-1 /* fail if you forget to update this assert */ ));
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
@@ -1952,6 +2012,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;
@@ -2515,11 +2577,30 @@ gc_fail:
record_gc_qtexts();
}
+#define SINGLE_ENTRY_RESET(e) \
+if (e) { \
+ if (minmax_only) { \
+ /* When requested reset only min/max statistics of an entry */ \
+ for (int kind = 0; kind < PGSS_NUMKIND; kind++) \
+ { \
+ e->counters.max_time[kind] = 0; \
+ e->counters.min_time[kind] = 0; \
+ } \
+ e->minmax_stats_since = stats_reset; \
+ } \
+ else \
+ { \
+ /* Remove the key otherwise */ \
+ hash_search(pgss_hash, &e->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;
@@ -2527,6 +2608,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,
@@ -2536,6 +2618,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. */
@@ -2544,23 +2628,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(entry);
+
+ /* 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(entry);
}
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)
{
@@ -2568,8 +2650,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(entry);
}
}
}
@@ -2579,8 +2660,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(entry);
}
}
@@ -2594,7 +2674,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;
@@ -2632,6 +2711,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 0747e481383..8a76106ec67 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.10'
+default_version = '1.11'
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 e2a83106d4c..ec06caa5ddc 100644
--- a/contrib/pg_stat_statements/sql/oldextversions.sql
+++ b/contrib/pg_stat_statements/sql/oldextversions.sql
@@ -48,4 +48,11 @@ AlTER EXTENSION pg_stat_statements UPDATE TO '1.10';
\d pg_stat_statements
SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
+-- New functions and views for pg_stat_statements in 1.11
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.11';
+\d pg_stat_statements
+SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
+-- New parameter minmax_only of pg_stat_statements_reset function
+SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
+
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 8f5c866225b..683a1f40244 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));
@@ -127,7 +127,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));
@@ -151,7 +151,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";
@@ -162,7 +162,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
@@ -196,7 +196,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);
@@ -229,7 +229,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;
@@ -258,7 +258,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);
@@ -277,7 +277,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;
@@ -300,7 +300,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;
@@ -321,7 +321,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";
--
@@ -332,27 +332,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";
--
@@ -367,7 +367,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;
@@ -388,8 +388,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
@@ -464,4 +468,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 efc36da6021..0d1a0ad0108 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 be 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 be 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>
@@ -473,6 +487,25 @@
Total time spent by the statement on emitting code, in milliseconds
</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>
@@ -664,7 +697,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>
@@ -683,6 +717,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.30.2