pg_stat_statements: Fix parameter number gaps in normalized queries
authorMichael Paquier
Thu, 29 May 2025 02:26:03 +0000 (11:26 +0900)
committerMichael Paquier
Thu, 29 May 2025 02:26:03 +0000 (11:26 +0900)
commit35a428f30b15a3ab0c9a0cc26ade3b4cc3e47d8e
treee01c7c9a59753e58f3e1ed07fa38df6285fb9eef
parent089f27cf8aa4716acc5601abce4b14a372c8569e
pg_stat_statements: Fix parameter number gaps in normalized queries

pg_stat_statements anticipates that certain constant locations may be
recorded multiple times and attempts to avoid calculating a length for
these locations in fill_in_constant_lengths().

However, during generate_normalized_query() where normalized query
strings are generated, these locations are not excluded from
consideration.  This could increment the parameter number counter for
every recorded occurrence at such a location, leading to an incorrect
normalization in certain cases with gaps in the numbers reported.

For example, take this query:
SELECT WHERE '1' IN ('2'::int, '3'::int::text)
Before this commit, it would be normalized like that, with gaps in the
parameter numbers:
SELECT WHERE $1 IN ($3::int, $4::int::text)
However the correct, less confusing one should be like that:
SELECT WHERE $1 IN ($2::int, $3::int::text)

This commit fixes the computation of the parameter numbers to track the
number of constants replaced with an $n by a separate counter instead of
the iterator used to loop through the list of locations.

The underlying query IDs are not changed, neither are the normalized
strings for existing PGSS hash entries.  New entries with fresh
normalized queries would automatically get reshaped based on the new
parameter numbering.

Issue discovered while discussing a separate problem for HEAD, but this
affects all the stable branches.

Author: Sami Imseih 
Discussion: https://postgr.es/m/CAA5RZ0tzxvWXsacGyxrixdhy3tTTDfJQqxyFBRFh31nNHBQ5qA@mail.gmail.com
Backpatch-through: 13
contrib/pg_stat_statements/expected/extended.out
contrib/pg_stat_statements/expected/select.out
contrib/pg_stat_statements/pg_stat_statements.c
contrib/pg_stat_statements/sql/extended.sql
contrib/pg_stat_statements/sql/select.sql