Hi Alena, On Wed, 2023-10-25 at 16:25 +0300, Alena Rybakina wrote: > Hi! Thank you for your work on the subject. > 1. I didn't understand why we first try to find pgssEntry with a > false top_level value, and later find it with a true top_level value.
In case of pg_stat_statements the top_level field is the bool field of the pgssHashKey struct used as the key for pgss_hash hashtable. When we are performing a reset only userid, dbid and queryid values are provided. We need to reset both top-level and non-top level entries. We have only one way to get them all from a hashtable - search for entries having top_level=true and with top_level=false in their keys. > 2. And honestly, I think you should change > "Remove the entry if it exists, starting with the non-top-level > entry." on > "Remove the entry or reset min/max time statistic information and > the timestamp if it exists, starting with the non-top-level entry." > And the same with the comment bellow: > "Also reset the top-level entry if it exists." > "Also remove the entry or reset min/max time statistic information > and the timestamp if it exists." There are four such places actually - every time when the SINGLE_ENTRY_RESET macro is used. The logic of reset performed is described a bit in this macro definition. It seems quite redundant to repeat this description four times. But I've noted that "remove" terms should be replaced by "reset". I've attached v24 with commits updated. regards, Andrei Zubkov Postgres Professional
From 4fadc88d5e6c1afe7558393ba99c28d070ac7244 Mon Sep 17 00:00:00 2001 From: Andrei Zubkov <zub...@moonset.ru> Date: Wed, 25 Oct 2023 17:58:57 +0300 Subject: [PATCH 1/2] pg_stat_statements tests: Add NOT NULL checking of pg_stat_statements_reset This is preliminary patch. It adds NOT NULL checking for the result of pg_stat_statements_reset() function. It is needed for upcoming patch "Track statement entry timestamp" that will change the result type of this function to the timestamp of a reset performed. Author: Andrei Zubkov (zubkov) Reviewed by: Julien Rouhaud (rjuju), Hayato Kuroda (ha-kun), Yuki Seino (seinoyu), Chengxi Sun (martin-sun), Anton Melnikov (antonmel), Darren Rush (darrenr), Michael Paquier (michael-kun), Sergei Kornilov (melkij), Alena Rybakina (a.rybakina), Andrei Lepikhov (lepikhov) Discussion: https://www.postgresql.org/message-id/flat/72e80e7b160a6eb189df9ef6f068cce3765d37f8.camel%40moonset.ru --- .../pg_stat_statements/expected/cursors.out | 28 +-- contrib/pg_stat_statements/expected/dml.out | 28 +-- .../expected/level_tracking.out | 80 ++++---- .../pg_stat_statements/expected/planning.out | 18 +- .../pg_stat_statements/expected/select.out | 44 ++-- .../expected/user_activity.out | 120 +++++------ .../pg_stat_statements/expected/utility.out | 192 +++++++++--------- contrib/pg_stat_statements/expected/wal.out | 10 +- contrib/pg_stat_statements/sql/cursors.sql | 6 +- contrib/pg_stat_statements/sql/dml.sql | 6 +- .../pg_stat_statements/sql/level_tracking.sql | 12 +- contrib/pg_stat_statements/sql/planning.sql | 4 +- contrib/pg_stat_statements/sql/select.sql | 10 +- .../pg_stat_statements/sql/user_activity.sql | 15 +- contrib/pg_stat_statements/sql/utility.sql | 34 ++-- contrib/pg_stat_statements/sql/wal.sql | 2 +- 16 files changed, 307 insertions(+), 302 deletions(-) diff --git a/contrib/pg_stat_statements/expected/cursors.out b/contrib/pg_stat_statements/expected/cursors.out index 46375ea9051..0fc4b2c098d 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 $1 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/dml.out b/contrib/pg_stat_statements/expected/dml.out index ede47a71acc..f6ac8da5ca2 100644 --- a/contrib/pg_stat_statements/expected/dml.out +++ b/contrib/pg_stat_statements/expected/dml.out @@ -81,16 +81,16 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; 1 | 12 | SELECT * FROM pgss_dml_tab ORDER BY a 2 | 4 | SELECT * FROM pgss_dml_tab WHERE a > $1 ORDER BY a 1 | 8 | SELECT * FROM pgss_dml_tab WHERE a IN ($1, $2, $3, $4, $5) - 1 | 1 | SELECT pg_stat_statements_reset() + 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t 1 | 0 | SET pg_stat_statements.track_utility = FALSE 6 | 6 | UPDATE pgss_dml_tab SET b = $1 WHERE a = $2 1 | 3 | UPDATE pgss_dml_tab SET b = $1 WHERE a > $2 (10 rows) -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t (1 row) -- MERGE @@ -136,16 +136,16 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; | | WHEN NOT MATCHED THEN INSERT (a, b) VALUES ($1, $2) 1 | 0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a) + | | WHEN NOT MATCHED THEN INSERT (b, a) VALUES ($1, $2) - 1 | 1 | SELECT pg_stat_statements_reset() + 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (10 rows) -- check that [temp] table relation extensions are tracked as writes CREATE TABLE pgss_extend_tab (a int, b text); CREATE TEMP TABLE pgss_extend_temp_tab (a int, b text); -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t (1 row) INSERT INTO pgss_extend_tab (a, b) SELECT generate_series(1, 1000), 'something'; @@ -166,9 +166,9 @@ FROM pg_stat_statements; t | t | t | t (1 row) -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/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out index d924c87b41e..bad20e7f176 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"; @@ -202,9 +202,9 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; -------+------+------- (0 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/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/select.out b/contrib/pg_stat_statements/expected/select.out index 972539b2c51..dd6c756f67d 100644 --- a/contrib/pg_stat_statements/expected/select.out +++ b/contrib/pg_stat_statements/expected/select.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) -- @@ -138,7 +138,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; 1 | 2 | SELECT $1 AS i UNION SELECT $2 ORDER BY i 1 | 1 | SELECT $1 || $2 0 | 0 | SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C" - 1 | 1 | SELECT pg_stat_statements_reset() + 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t 1 | 2 | WITH t(f) AS ( + | | VALUES ($1), ($2) + | | ) + @@ -146,10 +146,10 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; 1 | 1 | select $1::jsonb ? $2 (12 rows) -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t (1 row) -- @@ -157,10 +157,10 @@ SELECT pg_stat_statements_reset(); -- 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 @@ -236,17 +236,17 @@ 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; -- -- access to pg_stat_statements_info view -- -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t (1 row) SELECT dealloc FROM pg_stat_statements_info; @@ -406,9 +406,9 @@ SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%'; 2 (1 row) -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/user_activity.out b/contrib/pg_stat_statements/expected/user_activity.out index f3c6b6ab326..38faf18c7c5 100644 --- a/contrib/pg_stat_statements/expected/user_activity.out +++ b/contrib/pg_stat_statements/expected/user_activity.out @@ -2,10 +2,10 @@ -- 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; @@ -39,27 +39,27 @@ SELECT 1+1 AS "TWO"; RESET ROLE; SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls | rows ------------------------------------+-------+------ - CREATE ROLE regress_stats_user1 | 1 | 0 - CREATE ROLE regress_stats_user2 | 1 | 0 - RESET ROLE | 2 | 0 - SELECT $1 AS "ONE" | 1 | 1 - 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 - SET ROLE regress_stats_user1 | 1 | 0 - SET ROLE regress_stats_user2 | 1 | 0 + query | calls | rows +----------------------------------------------------+-------+------ + CREATE ROLE regress_stats_user1 | 1 | 0 + CREATE ROLE regress_stats_user2 | 1 | 0 + RESET ROLE | 2 | 0 + SELECT $1 AS "ONE" | 1 | 1 + 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() IS NOT NULL AS t | 1 | 1 + SET ROLE regress_stats_user1 | 1 | 0 + SET ROLE regress_stats_user2 | 1 | 0 (10 rows) -- -- 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"; @@ -72,8 +72,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 | 10 SET ROLE regress_stats_user1 | 1 | 0 SET ROLE regress_stats_user2 | 1 | 0 @@ -87,10 +87,11 @@ 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"; @@ -106,9 +107,10 @@ 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 | 22 SET ROLE regress_stats_user1 | 1 | 0 SET ROLE regress_stats_user2 | 1 | 0 @@ -117,12 +119,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"; @@ -136,11 +138,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() IS NOT NULL AS t | 1 | 1 SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 3 | 34 SET ROLE regress_stats_user1 | 1 | 0 SET ROLE regress_stats_user2 | 1 | 0 @@ -149,11 +152,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"; @@ -166,12 +169,13 @@ 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 | 45 SET ROLE regress_stats_user2 | 1 | 0 @@ -180,16 +184,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 + query | calls | rows +---------------------------------------------------------+-------+------ + SELECT pg_stat_statements_reset(0,0,0) IS NOT NULL AS t | 1 | 1 (1 row) -- @@ -197,9 +201,9 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- DROP ROLE regress_stats_user1; DROP ROLE regress_stats_user2; -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 cc6e898cdf5..07502231259 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) -- Two-phase transactions @@ -205,19 +205,19 @@ BEGIN; PREPARE TRANSACTION 'stat_trans2'; ROLLBACK PREPARED 'stat_trans2'; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - calls | rows | query --------+------+----------------------------------- + calls | rows | query +-------+------+---------------------------------------------------- 2 | 0 | BEGIN 1 | 0 | COMMIT PREPARED $1 2 | 0 | PREPARE TRANSACTION $1 1 | 0 | ROLLBACK PREPARED $1 - 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) -- Savepoints @@ -235,20 +235,20 @@ ROLLBACK TO sp1; RELEASE SAVEPOINT sp1; COMMIT; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - calls | rows | query --------+------+----------------------------------- + calls | rows | query +-------+------+---------------------------------------------------- 1 | 0 | BEGIN 1 | 0 | COMMIT 3 | 0 | RELEASE $1 4 | 0 | ROLLBACK TO $1 4 | 0 | SAVEPOINT $1 - 1 | 1 | SELECT pg_stat_statements_reset() + 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (6 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 @@ -284,7 +284,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,$2) 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 @@ -321,10 +321,10 @@ BEGIN i2 := i; i3 := i3 + i; 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); @@ -346,22 +346,22 @@ CALL in_out(2, 1, 2); (1 row) SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - calls | rows | query --------+------+----------------------------------- + calls | rows | query +-------+------+---------------------------------------------------- 2 | 0 | CALL in_out($1, $2, $3) 1 | 0 | CALL overload($1) 1 | 0 | CALL overload($1) 2 | 0 | CALL sum_one($1) 2 | 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 (6 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. @@ -391,14 +391,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 @@ -424,13 +424,13 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; | | FROM generate_series($2, $3) AS tab(a) WHERE a < $4 AND a > $5 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 @@ -450,13 +450,13 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; | | SELECT a AS col1, $1::int AS col2 + | | FROM generate_series($2, $3) AS tab(a) WHERE a < $4 AND a > $5 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 @@ -478,13 +478,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 @@ -499,13 +499,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) -- Execution statements @@ -534,19 +534,19 @@ DEALLOCATE PREPARE stat_select; DEALLOCATE ALL; DEALLOCATE PREPARE ALL; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - calls | rows | query --------+------+--------------------------------------- + calls | rows | query +-------+------+---------------------------------------------------- 2 | 0 | DEALLOCATE $1 2 | 0 | DEALLOCATE ALL 2 | 2 | PREPARE stat_select AS SELECT $1 AS a 1 | 1 | SELECT $1 as a - 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. @@ -572,14 +572,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 @@ -591,10 +591,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) -- @@ -652,16 +652,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, $2) 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. @@ -674,20 +674,20 @@ 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' 1 | 0 | SET work_mem = '2MB' (7 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/wal.out b/contrib/pg_stat_statements/expected/wal.out index 9896ba25363..34a2bf5b033 100644 --- a/contrib/pg_stat_statements/expected/wal.out +++ b/contrib/pg_stat_statements/expected/wal.out @@ -17,14 +17,14 @@ FROM pg_stat_statements ORDER BY query COLLATE "C"; --------------------------------------------------------------+-------+------+---------------------+-----------------------+--------------------- DELETE FROM pgss_wal_tab WHERE a > $1 | 1 | 1 | t | t | t INSERT INTO pgss_wal_tab 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 SET pg_stat_statements.track_utility = FALSE | 1 | 0 | f | f | t UPDATE pgss_wal_tab SET b = $1 WHERE a > $2 | 1 | 3 | t | t | 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) 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/dml.sql b/contrib/pg_stat_statements/sql/dml.sql index 3b5d2afb858..9986b0a22d3 100644 --- a/contrib/pg_stat_statements/sql/dml.sql +++ b/contrib/pg_stat_statements/sql/dml.sql @@ -46,7 +46,7 @@ SELECT * FROM pgss_dml_tab ORDER BY a; SELECT * FROM pgss_dml_tab WHERE a IN (1, 2, 3, 4, 5); 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; -- MERGE MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4) @@ -77,7 +77,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; -- check that [temp] table relation extensions are tracked as writes CREATE TABLE pgss_extend_tab (a int, b text); CREATE TEMP TABLE pgss_extend_temp_tab (a int, b text); -SELECT pg_stat_statements_reset(); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; INSERT INTO pgss_extend_tab (a, b) SELECT generate_series(1, 1000), 'something'; INSERT INTO pgss_extend_temp_tab (a, b) SELECT generate_series(1, 1000), 'something'; WITH sizes AS ( @@ -92,4 +92,4 @@ SELECT SUM(shared_blks_dirtied) >= (SELECT rel_size FROM sizes) AS dirtied_ok FROM pg_stat_statements; -SELECT pg_stat_statements_reset(); +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 0c20b8ce69b..f039110601a 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,10 +91,10 @@ 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"; 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/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/select.sql b/contrib/pg_stat_statements/sql/select.sql index eef7b0bbf58..eb45cb81ad2 100644 --- a/contrib/pg_stat_statements/sql/select.sql +++ b/contrib/pg_stat_statements/sql/select.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; -- -- simple and compound statements @@ -56,7 +56,7 @@ EXECUTE pgss_test(1); DEALLOCATE pgss_test; 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; -- -- queries with locking clauses @@ -64,7 +64,7 @@ SELECT pg_stat_statements_reset(); 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; @@ -92,7 +92,7 @@ DROP TABLE pgss_a, pgss_b CASCADE; -- -- access to pg_stat_statements_info view -- -SELECT pg_stat_statements_reset(); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT dealloc FROM pg_stat_statements_info; -- FROM [ONLY] @@ -146,4 +146,4 @@ SELECT ( ) FROM (VALUES(6,7)) v3(e,f) GROUP BY ROLLUP(e,f); SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%'; -SELECT pg_stat_statements_reset(); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; diff --git a/contrib/pg_stat_statements/sql/user_activity.sql b/contrib/pg_stat_statements/sql/user_activity.sql index 4b95edda890..07a5f36fc12 100644 --- a/contrib/pg_stat_statements/sql/user_activity.sql +++ b/contrib/pg_stat_statements/sql/user_activity.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; CREATE ROLE regress_stats_user1; CREATE ROLE regress_stats_user2; @@ -24,7 +24,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"; -- @@ -35,27 +35,28 @@ 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"; -- @@ -63,4 +64,4 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- DROP ROLE regress_stats_user1; DROP ROLE regress_stats_user2; -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 04598e5ae46..83d1894a092 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; -- Two-phase transactions BEGIN; @@ -123,7 +123,7 @@ BEGIN; PREPARE TRANSACTION 'stat_trans2'; ROLLBACK PREPARED 'stat_trans2'; 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; -- Savepoints BEGIN; @@ -140,7 +140,7 @@ ROLLBACK TO sp1; RELEASE SAVEPOINT sp1; 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. @@ -185,7 +185,7 @@ BEGIN i2 := i; i3 := i3 + i; 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); @@ -198,7 +198,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; @@ -210,7 +210,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. @@ -227,7 +227,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. @@ -240,7 +240,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 @@ -252,7 +252,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); @@ -260,7 +260,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; -- Execution statements SELECT 1 as a; @@ -273,7 +273,7 @@ DEALLOCATE PREPARE stat_select; DEALLOCATE ALL; DEALLOCATE PREPARE ALL; 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. @@ -299,7 +299,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 @@ -328,7 +328,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. @@ -341,4 +341,4 @@ SET enable_seqscan = on; RESET enable_seqscan; 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/wal.sql b/contrib/pg_stat_statements/sql/wal.sql index 34b21c0fa98..1dc1552a81e 100644 --- a/contrib/pg_stat_statements/sql/wal.sql +++ b/contrib/pg_stat_statements/sql/wal.sql @@ -17,4 +17,4 @@ wal_bytes > 0 as wal_bytes_generated, wal_records > 0 as wal_records_generated, wal_records >= rows as wal_records_ge_rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -SELECT pg_stat_statements_reset(); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; -- 2.39.2
From 274e16cce2365fbe3d43a86d1b7208663a8f3a25 Mon Sep 17 00:00:00 2001 From: Andrei Zubkov <zub...@moonset.ru> Date: Wed, 25 Oct 2023 18:16:57 +0300 Subject: [PATCH 2/2] 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. Author: Andrei Zubkov (zubkov) Reviewed by: Julien Rouhaud (rjuju), Hayato Kuroda (ha-kun), Yuki Seino (seinoyu), Chengxi Sun (martin-sun), Anton Melnikov (antonmel), Darren Rush (darrenr), Michael Paquier (michael-kun), Sergei Kornilov (melkij), Alena Rybakina (a.rybakina), Andrei Lepikhov (lepikhov) Discussion: https://www.postgresql.org/message-id/flat/72e80e7b160a6eb189df9ef6f068cce3765d37f8.camel%40moonset.ru --- contrib/pg_stat_statements/Makefile | 2 +- .../expected/entry_timestamp.out | 159 ++++++++++++++++++ .../expected/oldextversions.out | 116 +++++++------ contrib/pg_stat_statements/meson.build | 1 + .../pg_stat_statements--1.10--1.11.sql | 23 ++- .../pg_stat_statements/pg_stat_statements.c | 118 ++++++++++--- .../sql/entry_timestamp.sql | 114 +++++++++++++ .../pg_stat_statements/sql/oldextversions.sql | 4 +- doc/src/sgml/pgstatstatements.sgml | 69 +++++++- 9 files changed, 510 insertions(+), 96 deletions(-) create mode 100644 contrib/pg_stat_statements/expected/entry_timestamp.out 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 eba4a95d91a..aecd1d6a2a0 100644 --- a/contrib/pg_stat_statements/Makefile +++ b/contrib/pg_stat_statements/Makefile @@ -19,7 +19,7 @@ LDFLAGS_SL += $(filter -lm, $(LIBS)) REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf REGRESS = select dml cursors utility level_tracking planning \ - user_activity wal cleanup oldextversions + user_activity wal 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/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/oldextversions.out b/contrib/pg_stat_statements/expected/oldextversions.out index 5a5404bbd57..ec317b0d6be 100644 --- a/contrib/pg_stat_statements/expected/oldextversions.out +++ b/contrib/pg_stat_statements/expected/oldextversions.out @@ -250,59 +250,61 @@ SELECT count(*) > 0 AS has_data FROM pg_stat_statements; t (1 row) --- New views for pg_stat_statements in 1.11 +-- 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 | | | - shared_blk_read_time | double precision | | | - shared_blk_write_time | double precision | | | - local_blk_read_time | double precision | | | - local_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 | | | - jit_deform_count | bigint | | | - jit_deform_time | double precision | | | + 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 | | | + shared_blk_read_time | double precision | | | + shared_blk_write_time | double precision | | | + local_blk_read_time | double precision | | | + local_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 | | | + jit_deform_count | bigint | | | + jit_deform_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 @@ -310,4 +312,16 @@ SELECT count(*) > 0 AS has_data FROM pg_stat_statements; 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/meson.build b/contrib/pg_stat_statements/meson.build index 15b7c7f2b02..81fe1eb917d 100644 --- a/contrib/pg_stat_statements/meson.build +++ b/contrib/pg_stat_statements/meson.build @@ -49,6 +49,7 @@ tests += { 'planning', 'user_activity', 'wal', + '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 index 5fe211184bf..0bb2c397711 100644 --- 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 @@ -3,13 +3,10 @@ -- 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); - -/* Then we can drop them */ +/* Drop old versions */ 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, @@ -59,7 +56,9 @@ CREATE FUNCTION pg_stat_statements(IN showtext boolean, OUT jit_emission_count int8, OUT jit_emission_time float8, OUT jit_deform_count int8, - OUT jit_deform_time float8 + OUT jit_deform_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' @@ -69,3 +68,15 @@ CREATE VIEW pg_stat_statements AS SELECT * FROM pg_stat_statements(true); GRANT SELECT ON pg_stat_statements TO PUBLIC; + +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; + +-- Don't want this to be available to non-superusers. +REVOKE ALL ON FUNCTION pg_stat_statements_reset(Oid, Oid, bigint, boolean) FROM PUBLIC; diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index baff87b49e1..cb1ed1dd4ec 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -162,9 +162,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 @@ -235,6 +235,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; @@ -318,6 +320,7 @@ 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); @@ -369,7 +372,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, @@ -664,6 +667,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 */ @@ -1380,11 +1385,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; @@ -1454,18 +1471,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(); } @@ -1478,8 +1511,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_V1_11 47 -#define PG_STAT_STATEMENTS_COLS 47 /* maximum of above */ +#define PG_STAT_STATEMENTS_COLS_V1_11 49 +#define PG_STAT_STATEMENTS_COLS 49 /* maximum of above */ /* * Retrieve statement statistics. @@ -1712,6 +1745,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)); @@ -1780,6 +1815,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); } @@ -1876,6 +1913,8 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, { values[i++] = Int64GetDatumFast(tmp.jit_deform_count); values[i++] = Float8GetDatumFast(tmp.jit_deform_time); + values[i++] = TimestampTzGetDatum(stats_since); + values[i++] = TimestampTzGetDatum(minmax_stats_since); } Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 : @@ -1994,6 +2033,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; @@ -2557,11 +2598,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; @@ -2569,6 +2629,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, @@ -2578,6 +2639,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. */ @@ -2587,22 +2650,22 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid) key.queryid = queryid; /* - * Remove the key if it exists, starting with the non-top-level entry. + * Reset the entry if it exists, starting with the non-top-level entry. */ 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 the top-level entry if it exists. */ + SINGLE_ENTRY_RESET(entry); + + /* Also reset the top-level entry if it exists. */ key.toplevel = true; - 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); + + 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) { @@ -2610,19 +2673,17 @@ 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); } } } else { - /* Remove all entries. */ + /* Reset all entries. */ 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); } } @@ -2636,7 +2697,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; @@ -2674,6 +2734,8 @@ done: release_lock: LWLockRelease(pgss->lock); + + return stats_reset; } /* 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/oldextversions.sql b/contrib/pg_stat_statements/sql/oldextversions.sql index 2435c0c576e..ec06caa5ddc 100644 --- a/contrib/pg_stat_statements/sql/oldextversions.sql +++ b/contrib/pg_stat_statements/sql/oldextversions.sql @@ -48,9 +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 views for pg_stat_statements in 1.11 +-- 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/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index 6c7ca962f81..15c589432e5 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> @@ -512,6 +526,28 @@ 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 (fields <structfield>min_plan_time</structfield>, + <structfield>max_plan_time</structfield>, + <structfield>min_exec_time</structfield> and + <structfield>max_exec_time</structfield>) + </para></entry> + </row> </tbody> </tgroup> </table> @@ -713,7 +749,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> @@ -732,6 +769,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.39.2
