agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Julien Rouhaud <julien.rouhaud@free.fr>
Subject: [PATCH v1] Make pg_stat_statements tests immune to prepared statements invalidation.
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/42557.1627229005@sss.pgh.pa.us
---
.../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--
view thread (131+ messages) latest in thread
Message-ID: <no-message-id-1861720@localhost>
Permalink: ../no-message-id-1861720@localhost/
Also on: postgresql.org/message-id/no-message-id-1861720@localhost
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: julien.rouhaud@free.fr
Subject: Re: [PATCH v1] Make pg_stat_statements tests immune to prepared statements invalidation.
In-Reply-To: <no-message-id-1861720@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox