Subject: [PATCH v1] Make pg_stat_statements tests immune to prepared statements invalidation.

From: Julien Rouhaud <[email-masked]>
Date: Mon, 26 Jul 2021 09:23:57 +0800

The tests for the number of planifications have been unstable since their
introduction, but got unnoticed until now as buildfarm animals don't run them
for now.

Discussion: https://postgr.es/m/[email-masked]
---
 .../expected/pg_stat_statements.out           | 27 ++++++++++++-------
 .../sql/pg_stat_statements.sql                |  5 +++-
 2 files changed, 21 insertions(+), 11 deletions(-)

diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index 40b5109b55..ea2f8cf77f 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -850,16 +850,23 @@ SELECT 42;
        42
 (1 row)
 
-SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-                                        query                                        | plans | calls | rows 
--------------------------------------------------------------------------------------+-------+-------+------
- ALTER TABLE test ADD COLUMN x int                                                   |     0 |     1 |    0
- CREATE TABLE test ()                                                                |     0 |     1 |    0
- PREPARE prep1 AS SELECT COUNT(*) FROM test                                          |     2 |     4 |    4
- SELECT $1                                                                           |     3 |     3 |    3
- SELECT pg_stat_statements_reset()                                                   |     0 |     1 |    1
- SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" |     1 |     0 |    0
-(6 rows)
+SELECT query, plans, calls, rows FROM pg_stat_statements WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C";
+                                                    query                                                    | plans | calls | rows 
+-------------------------------------------------------------------------------------------------------------+-------+-------+------
+ ALTER TABLE test ADD COLUMN x int                                                                           |     0 |     1 |    0
+ CREATE TABLE test ()                                                                                        |     0 |     1 |    0
+ SELECT $1                                                                                                   |     3 |     3 |    3
+ SELECT pg_stat_statements_reset()                                                                           |     0 |     1 |    1
+ SELECT query, plans, calls, rows FROM pg_stat_statements WHERE query NOT LIKE $1 ORDER BY query COLLATE "C" |     1 |     0 |    0
+(5 rows)
+
+-- for the prepared statemennt we expect at least one replan, but cache
+-- invalidations could force more
+SELECT query, plans >=2 AND plans<=calls AS plans_ok, rows FROM pg_stat_statements WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C";
+                   query                    | plans_ok | rows 
+--------------------------------------------+----------+------
+ PREPARE prep1 AS SELECT COUNT(*) FROM test | t        |    4
+(1 row)
 
 --
 -- access to pg_stat_statements_info view
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index bc3b6493e6..3606a5f581 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -356,7 +356,10 @@ EXECUTE prep1;
 SELECT 42;
 SELECT 42;
 SELECT 42;
-SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT query, plans, calls, rows FROM pg_stat_statements WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C";
+-- for the prepared statemennt we expect at least one replan, but cache
+-- invalidations could force more
+SELECT query, plans >=2 AND plans<=calls AS plans_ok, rows FROM pg_stat_statements WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C";
 
 --
 -- access to pg_stat_statements_info view
-- 
2.32.0

--u7vhz6hsqudt26ss--