Make query jumbling also squash PARAM_EXTERN params

Commit 62d712ecfd94 made query jumbling squash lists of Consts as a
single element, but there's no reason not to treat PARAM_EXTERN
parameters the same.  For these purposes, these values are indeed
constants for any particular execution of a query.

In particular, this should make list squashing more useful for
applications using extended query protocol, which would use parameters
extensively.

A complication arises: if a query has both external parameters and
squashable lists, then the parameter number used as placeholder for the
squashed list might be inconsistent with regards to the parameter
numbers used by the query literal.  To reduce the surprise factor, all
parameters are renumbered starting from 1 in that case.

Author: Sami Imseih <samimseih@gmail.com>
Author: Dmitry Dolgov <9erthalion6@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://postgr.es/m/CAA5RZ0tRXoPG2y6bMgBCWNDt0Tn=unRerbzYM=oW0syi1=C1OA@mail.gmail.com

5 files changed, 83 insertions, 28 deletions

diff --git a/contrib/pg_stat_statements/expected/extended.out b/contrib/pg_stat_statements/expected/extended.out
index 7da308ba84f..1bfd0c1ca24 100644
--- a/contrib/pg_stat_statements/expected/extended.out
+++ b/contrib/pg_stat_statements/expected/extended.out
@@ -69,13 +69,13 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
 (4 rows)
 
 -- Various parameter numbering patterns
+-- Unique query IDs with parameter numbers switched.
 SELECT pg_stat_statements_reset() IS NOT NULL AS t;
  t 
 ---
  t
 (1 row)
 
--- Unique query IDs with parameter numbers switched.
 SELECT WHERE ($1::int, 7) IN ((8, $2::int), ($3::int, 9)) \bind '1' '2' '3' \g
 --
 (0 rows)
@@ -96,7 +96,24 @@ SELECT WHERE $3::int IN ($1::int, $2::int) \bind '1' '2' '3' \g
 --
 (0 rows)
 
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                            query                             | calls 
+--------------------------------------------------------------+-------
+ SELECT WHERE $1::int IN ($2 /*, ... */)                      |     1
+ SELECT WHERE $1::int IN ($2 /*, ... */)                      |     1
+ SELECT WHERE $1::int IN ($2 /*, ... */)                      |     1
+ SELECT WHERE ($1::int, $4) IN (($5, $2::int), ($3::int, $6)) |     1
+ SELECT WHERE ($2::int, $4) IN (($5, $3::int), ($1::int, $6)) |     1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t           |     1
+(6 rows)
+
 -- Two groups of two queries with the same query ID.
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t 
+---
+ t
+(1 row)
+
 SELECT WHERE '1'::int IN ($1::int, '2'::int) \bind '1' \g
 --
 (1 row)
@@ -114,15 +131,34 @@ SELECT WHERE $2::int IN ($1::int, '2'::int) \bind '3' '4' \g
 (0 rows)
 
 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
-                            query                             | calls 
---------------------------------------------------------------+-------
- SELECT WHERE $1::int IN ($2::int, $3::int)                   |     1
- SELECT WHERE $2::int IN ($1::int, $3::int)                   |     2
- SELECT WHERE $2::int IN ($1::int, $3::int)                   |     2
- SELECT WHERE $2::int IN ($3::int, $1::int)                   |     1
- SELECT WHERE $3::int IN ($1::int, $2::int)                   |     1
- SELECT WHERE ($1::int, $4) IN (($5, $2::int), ($3::int, $6)) |     1
- SELECT WHERE ($2::int, $4) IN (($5, $3::int), ($1::int, $6)) |     1
- SELECT pg_stat_statements_reset() IS NOT NULL AS t           |     1
-(8 rows)
+                       query                        | calls 
+----------------------------------------------------+-------
+ SELECT WHERE $1::int IN ($2 /*, ... */)            |     2
+ SELECT WHERE $1::int IN ($2 /*, ... */)            |     2
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t |     1
+(3 rows)
+
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t 
+---
+ t
+(1 row)
+
+-- no squashable list, the parameters id's are kept as-is
+SELECT WHERE $3 = $1 AND $2 = $4 \bind 1 2 1 2 \g
+--
+(1 row)
+
+-- squashable list, so the parameter IDs will be re-assigned
+SELECT WHERE 1 IN (1, 2, 3) AND $3 = $1 AND $2 = $4 \bind 1 2 1 2 \g
+--
+(1 row)
+
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+                           query                            | calls 
+------------------------------------------------------------+-------
+ SELECT WHERE $1 IN ($2 /*, ... */) AND $3 = $4 AND $5 = $6 |     1
+ SELECT WHERE $3 = $1 AND $2 = $4                           |     1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t         |     1
+(3 rows)
 
diff --git a/contrib/pg_stat_statements/expected/squashing.out b/contrib/pg_stat_statements/expected/squashing.out
index e978564ad72..f952f47ef7b 100644
--- a/contrib/pg_stat_statements/expected/squashing.out
+++ b/contrib/pg_stat_statements/expected/squashing.out
@@ -103,7 +103,7 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
  SELECT pg_stat_statements_reset() IS NOT NULL AS t |     1
 (2 rows)
 
--- external parameters will not be squashed
+-- external parameters will be squashed
 SELECT pg_stat_statements_reset() IS NOT NULL AS t;
  t 
 ---
@@ -123,14 +123,14 @@ SELECT * FROM test_squash WHERE id::text = ANY(ARRAY[$1, $2, $3, $4, $5]) \bind
 (0 rows)
 
 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
-                                   query                                   | calls 
----------------------------------------------------------------------------+-------
- SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5)                |     1
- SELECT * FROM test_squash WHERE id::text = ANY(ARRAY[$1, $2, $3, $4, $5]) |     1
- SELECT pg_stat_statements_reset() IS NOT NULL AS t                        |     1
+                                query                                 | calls 
+----------------------------------------------------------------------+-------
+ SELECT * FROM test_squash WHERE id IN ($1 /*, ... */)                |     1
+ SELECT * FROM test_squash WHERE id::text = ANY(ARRAY[$1 /*, ... */]) |     1
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t                   |     1
 (3 rows)
 
--- neither are prepared statements
+-- prepared statements will also be squashed
 -- the IN and ARRAY forms of this statement will have the same queryId
 SELECT pg_stat_statements_reset() IS NOT NULL AS t;
  t 
@@ -155,12 +155,12 @@ EXECUTE p1(1, 2, 3, 4, 5);
 
 DEALLOCATE p1;
 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
-                           query                            | calls 
-------------------------------------------------------------+-------
- DEALLOCATE $1                                              |     2
- PREPARE p1(int, int, int, int, int) AS                    +|     2
- SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5) | 
- SELECT pg_stat_statements_reset() IS NOT NULL AS t         |     1
+                         query                         | calls 
+-------------------------------------------------------+-------
+ DEALLOCATE $1                                         |     2
+ PREPARE p1(int, int, int, int, int) AS               +|     2
+ SELECT * FROM test_squash WHERE id IN ($1 /*, ... */) | 
+ SELECT pg_stat_statements_reset() IS NOT NULL AS t    |     1
 (3 rows)
 
 -- More conditions in the query
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index ecc7f2fb266..5597fcaaa05 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -2841,6 +2841,16 @@ generate_normalized_query(JumbleState *jstate, const char *query,
 		int			off,		/* Offset from start for cur tok */
 					tok_len;	/* Length (in bytes) of that tok */
 
+		/*
+		 * If we have an external param at this location, but no lists are
+		 * being squashed across the query, then we skip here; this will make
+		 * us print print the characters found in the original query that
+		 * represent the parameter in the next iteration (or after the loop is
+		 * done), which is a bit odd but seems to work okay in most cases.
+		 */
+		if (jstate->clocations[i].extern_param && !jstate->has_squashed_lists)
+			continue;
+
 		off = jstate->clocations[i].location;
 
 		/* Adjust recorded location if we're dealing with partial string */
diff --git a/contrib/pg_stat_statements/sql/extended.sql b/contrib/pg_stat_statements/sql/extended.sql
index a366658a53a..9a6518e2f04 100644
--- a/contrib/pg_stat_statements/sql/extended.sql
+++ b/contrib/pg_stat_statements/sql/extended.sql
@@ -21,17 +21,26 @@ SELECT $1 \bind 'unnamed_val1' \g
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
 
 -- Various parameter numbering patterns
-SELECT pg_stat_statements_reset() IS NOT NULL AS t;
 -- Unique query IDs with parameter numbers switched.
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
 SELECT WHERE ($1::int, 7) IN ((8, $2::int), ($3::int, 9)) \bind '1' '2' '3' \g
 SELECT WHERE ($2::int, 10) IN ((11, $3::int), ($1::int, 12)) \bind '1' '2' '3' \g
 SELECT WHERE $1::int IN ($2::int, $3::int) \bind '1' '2' '3' \g
 SELECT WHERE $2::int IN ($3::int, $1::int) \bind '1' '2' '3' \g
 SELECT WHERE $3::int IN ($1::int, $2::int) \bind '1' '2' '3' \g
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
 -- Two groups of two queries with the same query ID.
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
 SELECT WHERE '1'::int IN ($1::int, '2'::int) \bind '1' \g
 SELECT WHERE '4'::int IN ($1::int, '5'::int) \bind '2' \g
 SELECT WHERE $2::int IN ($1::int, '1'::int) \bind '1' '2' \g
 SELECT WHERE $2::int IN ($1::int, '2'::int) \bind '3' '4' \g
+SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+
+-- no squashable list, the parameters id's are kept as-is
+SELECT WHERE $3 = $1 AND $2 = $4 \bind 1 2 1 2 \g
+-- squashable list, so the parameter IDs will be re-assigned
+SELECT WHERE 1 IN (1, 2, 3) AND $3 = $1 AND $2 = $4 \bind 1 2 1 2 \g
 
 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
diff --git a/contrib/pg_stat_statements/sql/squashing.sql b/contrib/pg_stat_statements/sql/squashing.sql
index 946e149831c..53138d125a9 100644
--- a/contrib/pg_stat_statements/sql/squashing.sql
+++ b/contrib/pg_stat_statements/sql/squashing.sql
@@ -32,7 +32,7 @@ SELECT WHERE 1 IN (1, int4(1), int4(2), 2);
 SELECT WHERE 1 = ANY (ARRAY[1, int4(1), int4(2), 2]);
 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
 
--- external parameters will not be squashed
+-- external parameters will be squashed
 SELECT pg_stat_statements_reset() IS NOT NULL AS t;
 SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5)  \bind 1 2 3 4 5
 ;
@@ -40,7 +40,7 @@ SELECT * FROM test_squash WHERE id::text = ANY(ARRAY[$1, $2, $3, $4, $5]) \bind
 ;
 SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
 
--- neither are prepared statements
+-- prepared statements will also be squashed
 -- the IN and ARRAY forms of this statement will have the same queryId
 SELECT pg_stat_statements_reset() IS NOT NULL AS t;
 PREPARE p1(int, int, int, int, int) AS