Thread

[PATCH] Make pg_stat_statement's entry_timestamp test less flaky.

Michael Banck <mbanck@gmx.net> — 2025-10-29T22:10:30Z

On systems with relatively low timer precision (such as buildfarm animal
fruitcrow), the combined min/max_plan_time and/or min/max_exec_time could be 0
as the executed query (SELECT 1 and similar) is very simple.

Change this by making the query more complex, including call to pg_sleep and a
join to a system table.
---
 .../expected/entry_timestamp.out              | 32 +++++++++----------
 .../sql/entry_timestamp.sql                   |  6 ++--
 2 files changed, 19 insertions(+), 19 deletions(-)

diff --git a/contrib/pg_stat_statements/expected/entry_timestamp.out b/contrib/pg_stat_statements/expected/entry_timestamp.out
index a10c4be6bac..d8cb712cb4e 100644
--- a/contrib/pg_stat_statements/expected/entry_timestamp.out
+++ b/contrib/pg_stat_statements/expected/entry_timestamp.out
@@ -3,17 +3,17 @@
 --
 -- planning time is needed during tests
 SET pg_stat_statements.track_planning = TRUE;
-SELECT 1 AS "STMTTS1";
- STMTTS1 
----------
-       1
+SELECT pg_sleep(1e-6), 1 AS "STMTTS1" FROM pg_database GROUP BY "STMTTS1" ORDER BY "STMTTS1";
+ pg_sleep | STMTTS1 
+----------+---------
+          |       1
 (1 row)
 
 SELECT now() AS ref_ts \gset
-SELECT 1,2 AS "STMTTS2";
- ?column? | STMTTS2 
-----------+---------
-        1 |       2
+SELECT pg_sleep(1e-6), 1, 2 AS "STMTTS2" FROM pg_database GROUP BY "STMTTS2" ORDER BY "STMTTS2";
+ pg_sleep | ?column? | STMTTS2 
+----------+----------+---------
+          |        1 |       2
 (1 row)
 
 SELECT stats_since >= :'ref_ts', count(*) FROM pg_stat_statements
@@ -80,10 +80,10 @@ 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
+                                             query                                              | reset_ts_match 
+------------------------------------------------------------------------------------------------+----------------
+ SELECT pg_sleep($1), $2, 2 AS "STMTTS2" FROM pg_database GROUP BY "STMTTS2" ORDER BY "STMTTS2" | f
+ SELECT pg_sleep($1), 1 AS "STMTTS1" FROM pg_database GROUP BY "STMTTS1" ORDER BY "STMTTS1"     | t
 (2 rows)
 
 -- check that minmax reset does not set stats_reset
@@ -123,10 +123,10 @@ WHERE query LIKE '%STMTTS%';
 (1 row)
 
 -- Execute first query once more to check stats update
-SELECT 1 AS "STMTTS1";
- STMTTS1 
----------
-       1
+SELECT pg_sleep(1e-6), 1 AS "STMTTS1" FROM pg_database GROUP BY "STMTTS1" ORDER BY "STMTTS1";
+ pg_sleep | STMTTS1 
+----------+---------
+          |       1
 (1 row)
 
 -- check
diff --git a/contrib/pg_stat_statements/sql/entry_timestamp.sql b/contrib/pg_stat_statements/sql/entry_timestamp.sql
index d6d3027ab4f..61f7f39b9dd 100644
--- a/contrib/pg_stat_statements/sql/entry_timestamp.sql
+++ b/contrib/pg_stat_statements/sql/entry_timestamp.sql
@@ -5,9 +5,9 @@
 -- planning time is needed during tests
 SET pg_stat_statements.track_planning = TRUE;
 
-SELECT 1 AS "STMTTS1";
+SELECT pg_sleep(1e-6), 1 AS "STMTTS1" FROM pg_database GROUP BY "STMTTS1" ORDER BY "STMTTS1";
 SELECT now() AS ref_ts \gset
-SELECT 1,2 AS "STMTTS2";
+SELECT pg_sleep(1e-6), 1, 2 AS "STMTTS2" FROM pg_database GROUP BY "STMTTS2" ORDER BY "STMTTS2";
 SELECT stats_since >= :'ref_ts', count(*) FROM pg_stat_statements
 WHERE query LIKE '%STMTTS%'
 GROUP BY stats_since >= :'ref_ts'
@@ -91,7 +91,7 @@ FROM pg_stat_statements
 WHERE query LIKE '%STMTTS%';
 
 -- Execute first query once more to check stats update
-SELECT 1 AS "STMTTS1";
+SELECT pg_sleep(1e-6), 1 AS "STMTTS1" FROM pg_database GROUP BY "STMTTS1" ORDER BY "STMTTS1";
 
 -- check
 -- we don't check planing times here to be independent of
-- 
2.39.5


--eRtJSFbw+EEWtPj3--