v18-0001-pg_stat_statements-Track-statement-entry-timestamp.patch
text/x-patch
Filename: v18-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 v18-0001
Subject: pg_stat_statements: Track statement entry timestamp
| File | + | − |
|---|---|---|
| contrib/pg_stat_statements/expected/cursors.out | 14 | 14 |
| contrib/pg_stat_statements/expected/entry_timestamp.out | 159 | 0 |
| contrib/pg_stat_statements/expected/level_tracking.out | 36 | 36 |
| contrib/pg_stat_statements/expected/oldextversions.out | 70 | 0 |
| contrib/pg_stat_statements/expected/pg_stat_statements.out | 78 | 69 |
| contrib/pg_stat_statements/expected/planning.out | 9 | 9 |
| contrib/pg_stat_statements/expected/utility.out | 71 | 71 |
| contrib/pg_stat_statements/Makefile | 2 | 2 |
| contrib/pg_stat_statements/meson.build | 2 | 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/cursors.sql | 3 | 3 |
| contrib/pg_stat_statements/sql/entry_timestamp.sql | 114 | 0 |
| contrib/pg_stat_statements/sql/level_tracking.sql | 5 | 5 |
| contrib/pg_stat_statements/sql/oldextversions.sql | 7 | 0 |
| contrib/pg_stat_statements/sql/pg_stat_statements.sql | 16 | 12 |
| contrib/pg_stat_statements/sql/planning.sql | 2 | 2 |
| contrib/pg_stat_statements/sql/utility.sql | 13 | 13 |
| doc/src/sgml/pgstatstatements.sgml | 57 | 9 |
From 0d97c3d3bc0f00c3a1c2bf00cbe9d1aab7e90a32 Mon Sep 17 00:00:00 2001
From: Andrei Zubkov <zubkov@moonset.ru>
Date: Wed, 1 Mar 2023 23:07:00 +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 | 4 +-
.../pg_stat_statements/expected/cursors.out | 28 +--
.../expected/entry_timestamp.out | 159 ++++++++++++++++++
.../expected/level_tracking.out | 72 ++++----
.../expected/oldextversions.out | 70 ++++++++
.../expected/pg_stat_statements.out | 147 ++++++++--------
.../pg_stat_statements/expected/planning.out | 18 +-
.../pg_stat_statements/expected/utility.out | 142 ++++++++--------
contrib/pg_stat_statements/meson.build | 2 +
.../pg_stat_statements--1.10--1.11.sql | 81 +++++++++
.../pg_stat_statements/pg_stat_statements.c | 139 +++++++++++----
.../pg_stat_statements.control | 2 +-
contrib/pg_stat_statements/sql/cursors.sql | 6 +-
.../sql/entry_timestamp.sql | 114 +++++++++++++
.../pg_stat_statements/sql/level_tracking.sql | 10 +-
.../pg_stat_statements/sql/oldextversions.sql | 7 +
.../sql/pg_stat_statements.sql | 28 +--
contrib/pg_stat_statements/sql/planning.sql | 4 +-
contrib/pg_stat_statements/sql/utility.sql | 26 +--
doc/src/sgml/pgstatstatements.sgml | 66 +++++++-
20 files changed, 850 insertions(+), 275 deletions(-)
create mode 100644 contrib/pg_stat_statements/expected/entry_timestamp.out
create mode 100644 contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql
create mode 100644 contrib/pg_stat_statements/sql/entry_timestamp.sql
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 69fbc6a8580..89b9f2dda85 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 \
@@ -18,7 +18,7 @@ LDFLAGS_SL += $(filter -lm, $(LIBS))
REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf
REGRESS = pg_stat_statements cursors utility level_tracking planning \
- cleanup oldextversions
+ entry_timestamp cleanup oldextversions
# Disabled because these tests require "shared_preload_libraries=pg_stat_statements",
# which typical installcheck users do not have (e.g. buildfarm clients).
NO_INSTALLCHECK = 1
diff --git a/contrib/pg_stat_statements/expected/cursors.out b/contrib/pg_stat_statements/expected/cursors.out
index 5d0dc196f97..b709bff830a 100644
--- a/contrib/pg_stat_statements/expected/cursors.out
+++ b/contrib/pg_stat_statements/expected/cursors.out
@@ -3,10 +3,10 @@
--
-- These tests require track_utility to be enabled.
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)
-- DECLARE
@@ -20,13 +20,13 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
-------+------+------------------------------------------------------
2 | 0 | CLOSE cursor_stats_1
2 | 0 | DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 1
- 1 | 1 | SELECT pg_stat_statements_reset()
+ 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(3 rows)
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
-- FETCH
@@ -59,12 +59,12 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
1 | 0 | DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT 3
1 | 1 | FETCH 1 IN cursor_stats_1
1 | 1 | FETCH 1 IN cursor_stats_2
- 1 | 1 | SELECT pg_stat_statements_reset()
+ 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(9 rows)
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
diff --git a/contrib/pg_stat_statements/expected/entry_timestamp.out b/contrib/pg_stat_statements/expected/entry_timestamp.out
new file mode 100644
index 00000000000..a10c4be6bac
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/entry_timestamp.out
@@ -0,0 +1,159 @@
+--
+-- statement timestamps
+--
+-- planning time is needed during tests
+SET pg_stat_statements.track_planning = TRUE;
+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)
+
+-- Cleanup
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out
index c824ebdac5d..75f7edfe797 100644
--- a/contrib/pg_stat_statements/expected/level_tracking.out
+++ b/contrib/pg_stat_statements/expected/level_tracking.out
@@ -2,10 +2,10 @@
-- Statement level tracking
--
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)
-- DO block - top-level tracking.
@@ -29,10 +29,10 @@ SELECT toplevel, calls, query FROM pg_stat_statements
| | $$ LANGUAGE plpgsql
(2 rows)
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
-- DO block - all-level tracking.
@@ -49,31 +49,31 @@ BEGIN
END; $$;
SELECT toplevel, calls, query FROM pg_stat_statements
ORDER BY query COLLATE "C", toplevel;
- toplevel | calls | query
-----------+-------+--------------------------------------
+ toplevel | calls | query
+----------+-------+----------------------------------------------------
f | 1 | DELETE FROM stats_track_tab
t | 1 | DELETE FROM stats_track_tab
- t | 1 | DO $$ +
- | | BEGIN +
- | | DELETE FROM stats_track_tab; +
+ t | 1 | DO $$ +
+ | | BEGIN +
+ | | DELETE FROM stats_track_tab; +
| | END; $$
- t | 1 | DO LANGUAGE plpgsql $$ +
- | | BEGIN +
- | | -- this is a SELECT +
- | | PERFORM 'hello world'::TEXT; +
+ t | 1 | DO LANGUAGE plpgsql $$ +
+ | | BEGIN +
+ | | -- this is a SELECT +
+ | | PERFORM 'hello world'::TEXT; +
| | END; $$
f | 1 | SELECT $1::TEXT
- t | 1 | SELECT pg_stat_statements_reset()
+ t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
t | 1 | SET pg_stat_statements.track = 'all'
(7 rows)
-- PL/pgSQL function - top-level tracking.
SET pg_stat_statements.track = 'top';
SET pg_stat_statements.track_utility = FALSE;
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
@@ -111,19 +111,19 @@ SELECT PLUS_ONE(10);
(1 row)
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows | query
--------+------+-----------------------------------
+ calls | rows | query
+-------+------+----------------------------------------------------
2 | 2 | SELECT PLUS_ONE($1)
2 | 2 | SELECT PLUS_TWO($1)
- 1 | 1 | SELECT pg_stat_statements_reset()
+ 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(3 rows)
-- PL/pgSQL function - all-level tracking.
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
@@ -165,13 +165,13 @@ SELECT PLUS_ONE(1);
(1 row)
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows | query
--------+------+-----------------------------------
+ calls | rows | query
+-------+------+----------------------------------------------------
2 | 2 | SELECT (i + $2 + $3)::INTEGER
2 | 2 | SELECT (i + $2)::INTEGER LIMIT $3
2 | 2 | SELECT PLUS_ONE($1)
2 | 2 | SELECT PLUS_TWO($1)
- 1 | 1 | SELECT pg_stat_statements_reset()
+ 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(5 rows)
DROP FUNCTION PLUS_ONE(INTEGER);
@@ -179,10 +179,10 @@ DROP FUNCTION PLUS_ONE(INTEGER);
-- 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";
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 2c5bed841af..d2d21675c5e 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
@@ -302,7 +302,7 @@ FROM pg_stat_statements ORDER BY query COLLATE "C";
-----------------------------------------------------------+-------+------+---------------------+-----------------------+---------------------
DELETE FROM pgss_test WHERE a > $1 | 1 | 1 | 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, +| | | | |
@@ -316,10 +316,10 @@ 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
@@ -395,7 +395,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;
@@ -403,10 +403,10 @@ DROP TABLE pgss_a, pgss_b CASCADE;
-- Track user activity and reset them
--
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)
CREATE ROLE regress_stats_user1;
@@ -449,7 +449,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
@@ -458,10 +458,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";
@@ -474,8 +474,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
@@ -489,10 +489,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";
@@ -508,9 +508,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
@@ -519,12 +519,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";
@@ -538,11 +538,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
@@ -551,11 +551,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";
@@ -568,12 +568,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
@@ -582,16 +582,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)
@@ -603,16 +603,25 @@ DROP ROLE regress_stats_user2;
--
-- 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)
-- FROM [ONLY]
diff --git a/contrib/pg_stat_statements/expected/planning.out b/contrib/pg_stat_statements/expected/planning.out
index c3561dd7da3..9effd11fdc8 100644
--- a/contrib/pg_stat_statements/expected/planning.out
+++ b/contrib/pg_stat_statements/expected/planning.out
@@ -3,10 +3,10 @@
--
-- These tests require track_planning to be enabled.
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)
--
@@ -64,7 +64,7 @@ SELECT plans, calls, rows, query FROM pg_stat_statements
0 | 1 | 0 | ALTER TABLE stats_plan_test ADD COLUMN x int
0 | 1 | 0 | CREATE TABLE stats_plan_test ()
3 | 3 | 3 | SELECT $1
- 0 | 1 | 1 | SELECT pg_stat_statements_reset()
+ 0 | 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
1 | 0 | 0 | SELECT plans, calls, rows, query FROM pg_stat_statements+
| | | WHERE query NOT LIKE $1 ORDER BY query COLLATE "C"
(5 rows)
@@ -80,9 +80,9 @@ SELECT plans >= 2 AND plans <= calls AS plans_ok, calls, rows, query FROM pg_sta
-- Cleanup
DROP TABLE stats_plan_test;
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out
index dbb8f661c01..e89c8e9a3c9 100644
--- a/contrib/pg_stat_statements/expected/utility.out
+++ b/contrib/pg_stat_statements/expected/utility.out
@@ -3,10 +3,10 @@
--
-- These tests require track_utility to be enabled.
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)
-- Tables, indexes, triggers
@@ -33,13 +33,13 @@ NOTICE: table "tab_stats" does not exist, skipping
1 | 0 | CREATE TEMP TABLE tab_stats (a int, b char(20))
3 | 0 | DROP TABLE IF EXISTS tab_stats
1 | 0 | DROP TABLE tab_stats
- 1 | 1 | SELECT pg_stat_statements_reset()
+ 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(8 rows)
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
-- Partitions
@@ -142,13 +142,13 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
1 | 0 | DROP TABLE trigger_tab_stats
1 | 0 | DROP TYPE stats_type
1 | 0 | DROP VIEW view_stats
- 1 | 1 | SELECT pg_stat_statements_reset()
+ 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(39 rows)
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
-- Transaction statements
@@ -188,13 +188,13 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
1 | 0 | BEGIN TRANSACTION NOT DEFERRABLE, READ ONLY, READ WRITE, DEFERRABLE
1 | 0 | BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE
7 | 0 | COMMIT WORK
- 1 | 1 | SELECT pg_stat_statements_reset()
+ 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(8 rows)
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
-- EXPLAIN statements
@@ -230,7 +230,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
-------+------+-------------------------------------------------------------------------------
2 | 0 | EXPLAIN (costs off) SELECT 1
2 | 0 | EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 3
- 1 | 1 | SELECT pg_stat_statements_reset()
+ 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(3 rows)
-- CALL
@@ -246,10 +246,10 @@ DECLARE
BEGIN
SELECT (i + j)::int INTO r;
END; $$ LANGUAGE plpgsql;
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
CALL sum_one(3);
@@ -257,21 +257,21 @@ CALL sum_one(199);
CALL sum_two(1,1);
CALL sum_two(1,2);
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows | query
--------+------+-----------------------------------
+ calls | rows | query
+-------+------+----------------------------------------------------
1 | 0 | CALL sum_one(199)
1 | 0 | CALL sum_one(3)
1 | 0 | CALL sum_two(1,1)
1 | 0 | CALL sum_two(1,2)
- 1 | 1 | SELECT pg_stat_statements_reset()
+ 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(5 rows)
-- COPY
CREATE TABLE copy_stats (a int, b int);
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
-- Some queries with A_Const nodes.
@@ -301,14 +301,14 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
1 | 1 | COPY (SELECT 2) TO STDOUT
1 | 2 | COPY (UPDATE copy_stats SET b = b + 1 RETURNING *) TO STDOUT
1 | 2 | COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT
- 1 | 1 | SELECT pg_stat_statements_reset()
+ 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(8 rows)
DROP TABLE copy_stats;
-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 AS
@@ -334,13 +334,13 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
| | FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2
2 | 0 | DROP TABLE ctas_stats_1
2 | 0 | DROP TABLE ctas_stats_2
- 1 | 1 | SELECT pg_stat_statements_reset()
+ 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(5 rows)
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
-- CREATE MATERIALIZED VIEW
@@ -360,13 +360,13 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
| | SELECT a AS col1, 2::int AS col2 +
| | FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2
2 | 0 | DROP MATERIALIZED VIEW matview_stats_1
- 1 | 1 | SELECT pg_stat_statements_reset()
+ 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(3 rows)
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
-- CREATE VIEW
@@ -388,13 +388,13 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
| | SELECT a AS col1, 4::int AS col2 +
| | FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3
2 | 0 | DROP VIEW view_stats_1
- 1 | 1 | SELECT pg_stat_statements_reset()
+ 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(4 rows)
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
-- Domains
@@ -409,13 +409,13 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
1 | 0 | ALTER DOMAIN domain_stats SET DEFAULT '3'
1 | 0 | CREATE DOMAIN domain_stats AS int CHECK (VALUE > 0)
1 | 0 | DROP DOMAIN domain_stats
- 1 | 1 | SELECT pg_stat_statements_reset()
+ 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(5 rows)
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
-- SET statements.
@@ -441,14 +441,14 @@ SET LOCAL SESSION AUTHORIZATION DEFAULT;
RESET SESSION AUTHORIZATION;
COMMIT;
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows | query
--------+------+-------------------------------------------------
+ calls | rows | query
+-------+------+----------------------------------------------------
2 | 0 | BEGIN
2 | 0 | COMMIT
2 | 0 | RESET SESSION AUTHORIZATION
1 | 0 | RESET enable_seqscan
1 | 0 | RESET work_mem
- 1 | 1 | SELECT pg_stat_statements_reset()
+ 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
1 | 0 | SET LOCAL SESSION AUTHORIZATION DEFAULT
1 | 0 | SET SESSION SESSION AUTHORIZATION DEFAULT
1 | 0 | SET TRANSACTION ISOLATION LEVEL READ COMMITTED
@@ -460,10 +460,10 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
1 | 0 | SET work_mem = '2MB'
(15 rows)
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
--
@@ -521,16 +521,16 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
1 | 1 | FETCH NEXT pgss_cursor
1 | 13 | REFRESH MATERIALIZED VIEW pgss_matv
1 | 10 | SELECT generate_series(1, 10) c INTO pgss_select_into
- 1 | 1 | SELECT pg_stat_statements_reset()
+ 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
(12 rows)
DROP MATERIALIZED VIEW pgss_matv;
DROP TABLE pgss_ctas;
DROP TABLE pgss_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)
-- SET statements.
@@ -543,11 +543,11 @@ SET enable_seqscan = off;
SET enable_seqscan = on;
RESET enable_seqscan;
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
- calls | rows | query
--------+------+-----------------------------------
+ calls | rows | query
+-------+------+----------------------------------------------------
1 | 0 | RESET enable_seqscan
1 | 0 | RESET work_mem
- 1 | 1 | SELECT pg_stat_statements_reset()
+ 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
1 | 0 | SET enable_seqscan = off
1 | 0 | SET enable_seqscan = on
2 | 0 | SET work_mem = '1MB'
diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build
index 10ccc263000..f7426a6b61e 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',
@@ -45,6 +46,7 @@ tests += {
'utility',
'level_tracking',
'planning',
+ 'entry_timestamp',
'cleanup',
'oldextversions',
],
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/cursors.sql b/contrib/pg_stat_statements/sql/cursors.sql
index cef6dc9e1b8..61738ac470e 100644
--- a/contrib/pg_stat_statements/sql/cursors.sql
+++ b/contrib/pg_stat_statements/sql/cursors.sql
@@ -4,7 +4,7 @@
-- These tests require track_utility to be enabled.
SET pg_stat_statements.track_utility = TRUE;
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- DECLARE
-- SELECT is normalized.
@@ -14,7 +14,7 @@ DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2;
CLOSE cursor_stats_1;
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- FETCH
BEGIN;
@@ -27,4 +27,4 @@ CLOSE cursor_stats_2;
COMMIT;
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
diff --git a/contrib/pg_stat_statements/sql/entry_timestamp.sql b/contrib/pg_stat_statements/sql/entry_timestamp.sql
new file mode 100644
index 00000000000..d6d3027ab4f
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/entry_timestamp.sql
@@ -0,0 +1,114 @@
+--
+-- statement timestamps
+--
+
+-- planning time is needed during tests
+SET pg_stat_statements.track_planning = TRUE;
+
+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%';
+
+-- Cleanup
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
diff --git a/contrib/pg_stat_statements/sql/level_tracking.sql b/contrib/pg_stat_statements/sql/level_tracking.sql
index 2f95bf98e5c..43a4d7b8c45 100644
--- a/contrib/pg_stat_statements/sql/level_tracking.sql
+++ b/contrib/pg_stat_statements/sql/level_tracking.sql
@@ -3,7 +3,7 @@
--
SET pg_stat_statements.track_utility = TRUE;
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- DO block - top-level tracking.
CREATE TABLE stats_track_tab (x int);
@@ -16,7 +16,7 @@ END;
$$ LANGUAGE plpgsql;
SELECT toplevel, calls, query FROM pg_stat_statements
WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- DO block - all-level tracking.
SET pg_stat_statements.track = 'all';
@@ -36,7 +36,7 @@ SELECT toplevel, calls, query FROM pg_stat_statements
-- PL/pgSQL function - top-level tracking.
SET pg_stat_statements.track = 'top';
SET pg_stat_statements.track_utility = FALSE;
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
DECLARE
r INTEGER;
@@ -59,7 +59,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
-- PL/pgSQL function - all-level tracking.
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);
@@ -91,7 +91,7 @@ DROP FUNCTION PLUS_ONE(INTEGER);
-- 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";
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 3a3d2350667..d31055fd38f 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";
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;
@@ -180,7 +180,7 @@ DROP TABLE pgss_a, pgss_b CASCADE;
-- Track user activity and reset them
--
SET pg_stat_statements.track_utility = TRUE;
-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;
@@ -201,7 +201,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";
--
@@ -212,27 +212,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";
--
@@ -244,8 +244,12 @@ DROP ROLE regress_stats_user2;
--
-- 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;
-- FROM [ONLY]
CREATE TABLE tbl_inh(id integer);
diff --git a/contrib/pg_stat_statements/sql/planning.sql b/contrib/pg_stat_statements/sql/planning.sql
index a59b9363c4b..46f5d9b951c 100644
--- a/contrib/pg_stat_statements/sql/planning.sql
+++ b/contrib/pg_stat_statements/sql/planning.sql
@@ -4,7 +4,7 @@
-- These tests require track_planning to be enabled.
SET pg_stat_statements.track_planning = TRUE;
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
--
-- [re]plan counting
@@ -28,4 +28,4 @@ SELECT plans >= 2 AND plans <= calls AS plans_ok, calls, rows, query FROM pg_sta
-- Cleanup
DROP TABLE stats_plan_test;
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
diff --git a/contrib/pg_stat_statements/sql/utility.sql b/contrib/pg_stat_statements/sql/utility.sql
index 4c58a4c978e..a7f97c9a7d6 100644
--- a/contrib/pg_stat_statements/sql/utility.sql
+++ b/contrib/pg_stat_statements/sql/utility.sql
@@ -4,7 +4,7 @@
-- These tests require track_utility to be enabled.
SET pg_stat_statements.track_utility = TRUE;
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- Tables, indexes, triggers
CREATE TEMP TABLE tab_stats (a int, b char(20));
@@ -18,7 +18,7 @@ DROP TABLE IF EXISTS tab_stats \;
DROP TABLE IF EXISTS tab_stats \;
Drop Table If Exists tab_stats \;
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- Partitions
CREATE TABLE pt_stats (a int, b int) PARTITION BY range (a);
@@ -83,7 +83,7 @@ CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats;
DROP TABLE tab_expr_stats;
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- Transaction statements
BEGIN;
@@ -113,7 +113,7 @@ COMMIT;
BEGIN TRANSACTION NOT DEFERRABLE, READ ONLY, READ WRITE, DEFERRABLE;
COMMIT;
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- EXPLAIN statements
-- A Query is used, normalized by the query jumbling.
@@ -137,7 +137,7 @@ DECLARE
BEGIN
SELECT (i + j)::int INTO r;
END; $$ LANGUAGE plpgsql;
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
CALL sum_one(3);
CALL sum_one(199);
CALL sum_two(1,1);
@@ -146,7 +146,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
-- COPY
CREATE TABLE copy_stats (a int, b int);
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- Some queries with A_Const nodes.
COPY (SELECT 1) TO STDOUT;
COPY (SELECT 2) TO STDOUT;
@@ -158,7 +158,7 @@ COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT;
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
DROP TABLE copy_stats;
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- CREATE TABLE AS
-- SELECT queries are normalized, creating matching query IDs.
@@ -175,7 +175,7 @@ CREATE TABLE ctas_stats_2 AS
FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 1;
DROP TABLE ctas_stats_2;
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- CREATE MATERIALIZED VIEW
-- SELECT queries are normalized, creating matching query IDs.
@@ -188,7 +188,7 @@ CREATE MATERIALIZED VIEW matview_stats_1 AS
FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
DROP MATERIALIZED VIEW matview_stats_1;
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- CREATE VIEW
CREATE VIEW view_stats_1 AS
@@ -200,7 +200,7 @@ CREATE VIEW view_stats_1 AS
FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
DROP VIEW view_stats_1;
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- Domains
CREATE DOMAIN domain_stats AS int CHECK (VALUE > 0);
@@ -208,7 +208,7 @@ ALTER DOMAIN domain_stats SET DEFAULT '3';
ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1);
DROP DOMAIN domain_stats;
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- SET statements.
-- These use two different strings, still they count as one entry.
@@ -234,7 +234,7 @@ RESET SESSION AUTHORIZATION;
COMMIT;
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
--
-- Track the total number of rows retrieved or affected by the utility
@@ -263,7 +263,7 @@ DROP MATERIALIZED VIEW pgss_matv;
DROP TABLE pgss_ctas;
DROP TABLE pgss_select_into;
-SELECT pg_stat_statements_reset();
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
-- SET statements.
-- These use two different strings, still they count as one entry.
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index b1214ee6453..0354bb682c0 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>
@@ -674,7 +707,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>
@@ -693,6 +727,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