On Mon, Jul 26, 2021 at 01:08:08AM +0800, Julien Rouhaud wrote: > Le lun. 26 juil. 2021 à 00:59, Tom Lane <t...@sss.pgh.pa.us> a écrit : > > > Julien Rouhaud <rjuju...@gmail.com> writes: > > > On Sun, Jul 25, 2021 at 12:03:25PM -0400, Tom Lane wrote: > > > > > > Would it be worth to split the query for the prepared statement row vs > > the rest > > > to keep the full "plans" coverage when possible? > > > > +1, the same thought occurred to me later. Also, if we're making > > it specific to the one PREPARE example, we could get away with > > checking "plans >= 2 AND plans <= calls", with a comment like > > "we expect at least one replan event, but there could be more". > > > > Do you want to prepare a patch? > > > > Sure, I will work on that tomorrow!
I attach a patch that splits the test and add a comment explaining the boundaries for the new query. Checked with and without forced invalidations.
>From 225632c54ff734f7f2b5a2b0d45127e425327973 Mon Sep 17 00:00:00 2001 From: Julien Rouhaud <julien.rouh...@free.fr> Date: Mon, 26 Jul 2021 09:23:57 +0800 Subject: [PATCH v1] Make pg_stat_statements tests immune to prepared statements invalidation. 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.1627229...@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
