Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7e8b44f

Browse files
committed
pg_stat_statements: Fix parameter number gaps in normalized queries
pg_stat_statements anticipates that certain constant locations may berecorded multiple times and attempts to avoid calculating a length forthese locations in fill_in_constant_lengths().However, during generate_normalized_query() where normalized querystrings are generated, these locations are not excluded fromconsideration. This could increment the parameter number counter forevery recorded occurrence at such a location, leading to an incorrectnormalization 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 theparameter 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 thenumber of constants replaced with an $n by a separate counter instead ofthe iterator used to loop through the list of locations.The underlying query IDs are not changed, neither are the normalizedstrings for existing PGSS hash entries. New entries with freshnormalized queries would automatically get reshaped based on the newparameter numbering.Issue discovered while discussing a separate problem for HEAD, but thisaffects all the stable branches.Author: Sami Imseih <samimseih@gmail.com>Discussion:https://postgr.es/m/CAA5RZ0tzxvWXsacGyxrixdhy3tTTDfJQqxyFBRFh31nNHBQ5qA@mail.gmail.comBackpatch-through: 13
1 parent52d0862 commit7e8b44f

File tree

5 files changed

+114
-1
lines changed

5 files changed

+114
-1
lines changed

‎contrib/pg_stat_statements/expected/extended.out

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8,3 +8,61 @@ SELECT query_id IS NOT NULL AS query_id_set
88
t
99
(1 row)
1010

11+
-- Various parameter numbering patterns
12+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
13+
t
14+
---
15+
t
16+
(1 row)
17+
18+
-- Unique query IDs with parameter numbers switched.
19+
SELECT WHERE ($1::int, 7) IN ((8, $2::int), ($3::int, 9)) \bind '1' '2' '3' \g
20+
--
21+
(0 rows)
22+
23+
SELECT WHERE ($2::int, 10) IN ((11, $3::int), ($1::int, 12)) \bind '1' '2' '3' \g
24+
--
25+
(0 rows)
26+
27+
SELECT WHERE $1::int IN ($2::int, $3::int) \bind '1' '2' '3' \g
28+
--
29+
(0 rows)
30+
31+
SELECT WHERE $2::int IN ($3::int, $1::int) \bind '1' '2' '3' \g
32+
--
33+
(0 rows)
34+
35+
SELECT WHERE $3::int IN ($1::int, $2::int) \bind '1' '2' '3' \g
36+
--
37+
(0 rows)
38+
39+
-- Two groups of two queries with the same query ID.
40+
SELECT WHERE '1'::int IN ($1::int, '2'::int) \bind '1' \g
41+
--
42+
(1 row)
43+
44+
SELECT WHERE '4'::int IN ($1::int, '5'::int) \bind '2' \g
45+
--
46+
(0 rows)
47+
48+
SELECT WHERE $2::int IN ($1::int, '1'::int) \bind '1' '2' \g
49+
--
50+
(0 rows)
51+
52+
SELECT WHERE $2::int IN ($1::int, '2'::int) \bind '3' '4' \g
53+
--
54+
(0 rows)
55+
56+
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
57+
query | calls
58+
--------------------------------------------------------------+-------
59+
SELECT WHERE $1::int IN ($2::int, $3::int) | 1
60+
SELECT WHERE $2::int IN ($1::int, $3::int) | 2
61+
SELECT WHERE $2::int IN ($1::int, $3::int) | 2
62+
SELECT WHERE $2::int IN ($3::int, $1::int) | 1
63+
SELECT WHERE $3::int IN ($1::int, $2::int) | 1
64+
SELECT WHERE ($1::int, $4) IN (($5, $2::int), ($3::int, $6)) | 1
65+
SELECT WHERE ($2::int, $4) IN (($5, $3::int), ($1::int, $6)) | 1
66+
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
67+
(8 rows)
68+

‎contrib/pg_stat_statements/expected/select.out

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -152,6 +152,35 @@ SELECT pg_stat_statements_reset();
152152

153153
(1 row)
154154

155+
-- normalization of constants and parameters, with constant locations
156+
-- recorded one or more times.
157+
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
158+
t
159+
---
160+
t
161+
(1 row)
162+
163+
SELECT WHERE '1' IN ('1'::int, '3'::int::text);
164+
--
165+
(1 row)
166+
167+
SELECT WHERE (1, 2) IN ((1, 2), (2, 3));
168+
--
169+
(1 row)
170+
171+
SELECT WHERE (3, 4) IN ((5, 6), (8, 7));
172+
--
173+
(0 rows)
174+
175+
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C";
176+
query | calls
177+
------------------------------------------------------------------------+-------
178+
SELECT WHERE $1 IN ($2::int, $3::int::text) | 1
179+
SELECT WHERE ($1, $2) IN (($3, $4), ($5, $6)) | 2
180+
SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1
181+
SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 0
182+
(4 rows)
183+
155184
--
156185
-- queries with locking clauses
157186
--

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2668,6 +2668,7 @@ generate_normalized_query(JumbleState *jstate, const char *query,
26682668
n_quer_loc=0,/* Normalized query byte location */
26692669
last_off=0,/* Offset from start for previous tok */
26702670
last_tok_len=0;/* Length (in bytes) of that tok */
2671+
intnum_constants_replaced=0;
26712672

26722673
/*
26732674
* Get constants' lengths (core system only gives us locations). Note
@@ -2711,7 +2712,8 @@ generate_normalized_query(JumbleState *jstate, const char *query,
27112712

27122713
/* And insert a param symbol in place of the constant token */
27132714
n_quer_loc+=sprintf(norm_query+n_quer_loc,"$%d",
2714-
i+1+jstate->highest_extern_param_id);
2715+
num_constants_replaced+1+jstate->highest_extern_param_id);
2716+
num_constants_replaced++;
27152717

27162718
quer_loc=off+tok_len;
27172719
last_off=off;

‎contrib/pg_stat_statements/sql/extended.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,3 +5,19 @@ SET pg_stat_statements.track_utility = FALSE;
55
-- This test checks that an execute message sets a query ID.
66
SELECT query_idIS NOT NULLAS query_id_set
77
FROM pg_stat_activityWHERE pid= pg_backend_pid() \bind \g
8+
9+
-- Various parameter numbering patterns
10+
SELECT pg_stat_statements_reset()IS NOT NULLAS t;
11+
-- Unique query IDs with parameter numbers switched.
12+
SELECTWHERE ($1::int,7)IN ((8, $2::int), ($3::int,9)) \bind'1''2''3' \g
13+
SELECTWHERE ($2::int,10)IN ((11, $3::int), ($1::int,12)) \bind'1''2''3' \g
14+
SELECTWHERE $1::intIN ($2::int, $3::int) \bind'1''2''3' \g
15+
SELECTWHERE $2::intIN ($3::int, $1::int) \bind'1''2''3' \g
16+
SELECTWHERE $3::intIN ($1::int, $2::int) \bind'1''2''3' \g
17+
-- Two groups of two queries with the same query ID.
18+
SELECTWHERE'1'::intIN ($1::int,'2'::int) \bind'1' \g
19+
SELECTWHERE'4'::intIN ($1::int,'5'::int) \bind'2' \g
20+
SELECTWHERE $2::intIN ($1::int,'1'::int) \bind'1''2' \g
21+
SELECTWHERE $2::intIN ($1::int,'2'::int) \bind'3''4' \g
22+
23+
SELECT query, callsFROM pg_stat_statementsORDER BY query COLLATE"C";

‎contrib/pg_stat_statements/sql/select.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -58,6 +58,14 @@ DEALLOCATE pgss_test;
5858
SELECT calls, rows, queryFROM pg_stat_statementsORDER BY query COLLATE"C";
5959
SELECT pg_stat_statements_reset();
6060

61+
-- normalization of constants and parameters, with constant locations
62+
-- recorded one or more times.
63+
SELECT pg_stat_statements_reset()IS NOT NULLAS t;
64+
SELECTWHERE'1'IN ('1'::int,'3'::int::text);
65+
SELECTWHERE (1,2)IN ((1,2), (2,3));
66+
SELECTWHERE (3,4)IN ((5,6), (8,7));
67+
SELECT query, callsFROM pg_stat_statementsORDER BY query COLLATE"C";
68+
6169
--
6270
-- queries with locking clauses
6371
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp