Hi hackers, Patch rebased to the current master -- regards, Andrei Zubkov Postgres Professional
From ff2ff96352a843d32a1213a1e953503fd1525b7b Mon Sep 17 00:00:00 2001 From: Andrei Zubkov <zub...@moonset.ru> Date: Fri, 17 Nov 2023 00:27:24 +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 | 178 +++++++++--------- 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 | 32 ++-- contrib/pg_stat_statements/sql/wal.sql | 2 +- 16 files changed, 299 insertions(+), 294 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 0b94c71c9c1..dada95e0f03 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,21 +49,21 @@ 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) @@ -125,10 +125,10 @@ 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(); - 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 $$ @@ -166,11 +166,11 @@ 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) -- immutable SQL function --- can be executed at plan time @@ -200,10 +200,10 @@ SELECT toplevel, calls, rows, query FROM pg_stat_statements ORDER BY query COLLA -- 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 @@ -246,13 +246,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) -- immutable SQL function --- can be executed at plan time @@ -287,10 +287,10 @@ SELECT toplevel, calls, rows, query FROM pg_stat_statements ORDER BY query COLLA -- 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"; @@ -310,9 +310,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 a9134212909..fa738b5c00d 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,15 +652,15 @@ 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) 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 dcd0b043580..85eb9153d3e 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'; @@ -69,7 +69,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; @@ -101,7 +101,7 @@ SELECT toplevel, calls, rows, query FROM pg_stat_statements ORDER BY query COLLA -- 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); @@ -142,10 +142,10 @@ SELECT toplevel, calls, rows, query FROM pg_stat_statements ORDER BY query COLLA -- 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 3fb8dde68e2..4f7afece1de 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,4 +328,4 @@ 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; 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 996cadb44a0f6cf64353311c553778ce156e21c4 Mon Sep 17 00:00:00 2001 From: Andrei Zubkov <zub...@moonset.ru> Date: Fri, 17 Nov 2023 00:28:47 +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 6c63acf9898..16451763c5d 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -155,9 +155,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 @@ -228,6 +228,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,6 +310,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); @@ -359,7 +362,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, @@ -654,6 +657,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 */ @@ -1416,11 +1421,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; @@ -1490,18 +1507,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(); } @@ -1514,8 +1547,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. @@ -1748,6 +1781,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)); @@ -1816,6 +1851,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); } @@ -1912,6 +1949,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 : @@ -2030,6 +2069,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; @@ -2593,11 +2634,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; @@ -2605,6 +2665,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, @@ -2614,6 +2675,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. */ @@ -2623,22 +2686,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) { @@ -2646,19 +2709,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); } } @@ -2672,7 +2733,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; @@ -2710,6 +2770,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 b66dc7fa30a..acfacf828ce 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 @@ functions, 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
