Content-Length: 383715 | pFad | http://github.com/postgres/postgres/commit/8a1459f62ad16c5fee792faa83ac7ab70a291939

98 pg_stat_statements: Fix parameter number gaps in normalized queries · postgres/postgres@8a1459f · GitHub
Skip to content

Commit 8a1459f

Browse files
committed
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 <samimseih@gmail.com> Discussion: https://postgr.es/m/CAA5RZ0tzxvWXsacGyxrixdhy3tTTDfJQqxyFBRFh31nNHBQ5qA@mail.gmail.com Backpatch-through: 13
1 parent 0c09922 commit 8a1459f

File tree

3 files changed

+40
-1
lines changed

3 files changed

+40
-1
lines changed

contrib/pg_stat_statements/expected/pg_stat_statements.out

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -398,6 +398,35 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
398398
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0
399399
(6 rows)
400400

401+
-- normalization of constants and parameters, with constant locations
402+
-- recorded one or more times.
403+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
404+
t
405+
---
406+
t
407+
(1 row)
408+
409+
SELECT WHERE '1' IN ('1'::int, '3'::int::text);
410+
--
411+
(1 row)
412+
413+
SELECT WHERE (1, 2) IN ((1, 2), (2, 3));
414+
--
415+
(1 row)
416+
417+
SELECT WHERE (3, 4) IN ((5, 6), (8, 7));
418+
--
419+
(0 rows)
420+
421+
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
422+
query | calls
423+
------------------------------------------------------------------------+-------
424+
SELECT WHERE $1 IN ($2::int, $3::int::text) | 1
425+
SELECT WHERE ($1, $2) IN (($3, $4), ($5, $6)) | 2
426+
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
427+
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 0
428+
(4 rows)
429+
401430
--
402431
-- queries with locking clauses
403432
--

contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2640,6 +2640,7 @@ generate_normalized_query(JumbleState *jstate, const char *query,
26402640
n_quer_loc = 0, /* Normalized query byte location */
26412641
last_off = 0, /* Offset from start for previous tok */
26422642
last_tok_len = 0; /* Length (in bytes) of that tok */
2643+
int num_constants_replaced = 0;
26432644

26442645
/*
26452646
* Get constants' lengths (core system only gives us locations). Note
@@ -2683,7 +2684,8 @@ generate_normalized_query(JumbleState *jstate, const char *query,
26832684

26842685
/* And insert a param symbol in place of the constant token */
26852686
n_quer_loc += sprintf(norm_query + n_quer_loc, "$%d",
2686-
i + 1 + jstate->highest_extern_param_id);
2687+
num_constants_replaced + 1 + jstate->highest_extern_param_id);
2688+
num_constants_replaced++;
26872689

26882690
quer_loc = off + tok_len;
26892691
last_off = off;

contrib/pg_stat_statements/sql/pg_stat_statements.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -201,6 +201,14 @@ SELECT PLUS_ONE(1);
201201

202202
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
203203

204+
-- normalization of constants and parameters, with constant locations
205+
-- recorded one or more times.
206+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
207+
SELECT WHERE '1' IN ('1'::int, '3'::int::text);
208+
SELECT WHERE (1, 2) IN ((1, 2), (2, 3));
209+
SELECT WHERE (3, 4) IN ((5, 6), (8, 7));
210+
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
211+
204212
--
205213
-- queries with locking clauses
206214
--

0 commit comments

Comments
 (0)








ApplySandwichStrip

pFad - (p)hone/(F)rame/(a)nonymizer/(d)eclutterfier!      Saves Data!


--- a PPN by Garber Painting Akron. With Image Size Reduction included!

Fetched URL: http://github.com/postgres/postgres/commit/8a1459f62ad16c5fee792faa83ac7ab70a291939

Alternative Proxies:

Alternative Proxy

pFad Proxy

pFad v3 Proxy

pFad v4 Proxy