Skip to content

Commit a6f22e8

Browse files
tglsfdctglsfdc
committed
Show ignored constants as "$N" rather than "?" in pg_stat_statements.
The trouble with the original choice here is that "?" is a valid (and indeed used) operator name, so that you could end up with ambiguous statement texts like "SELECT ? ? ?". With this patch, you instead see "SELECT $1 ? $2", which seems significantly more readable. The numbers used for this purpose begin after the last actual $N parameter in the particular query. The conflict with external parameters has its own potential for confusion of course, but it was agreed to be an improvement over the previous behavior. Lukas Fittl Discussion: https://postgr.es/m/CAP53PkxeaCuwYmF-A4J5z2-qk5fYFo5_NH3gpXGJJBxv1DMwEw@mail.gmail.com
1 parent 6462238 commit a6f22e8

File tree

4 files changed

+142
-56
lines changed

4 files changed

+142
-56
lines changed

‎contrib/pg_stat_statements/expected/pg_stat_statements.out

+76-37
Original file line numberDiff line numberDiff line change
@@ -68,6 +68,13 @@ SELECT 1 AS i UNION SELECT 2 ORDER BY i;
6868
2
6969
(2 rows)
7070

71+
-- ? operator
72+
select '{"a":1, "b":2}'::jsonb ? 'b';
73+
?column?
74+
----------
75+
t
76+
(1 row)
77+
7178
-- cte
7279
WITH t(f) AS (
7380
VALUES (1.0), (2.0)
@@ -79,24 +86,35 @@ WITH t(f) AS (
7986
2.0
8087
(2 rows)
8188

89+
-- prepared statement with parameter
90+
PREPARE pgss_test (int) AS SELECT $1, 'test' LIMIT 1;
91+
EXECUTE pgss_test(1);
92+
?column? | ?column?
93+
----------+----------
94+
1 | test
95+
(1 row)
96+
97+
DEALLOCATE pgss_test;
8298
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
83-
query | calls | rows
84-
-----------------------------------------+-------+------
85-
SELECT ? +| 4 | 4
86-
+| |
87-
AS "text" | |
88-
SELECT ? + ? | 2 | 2
89-
SELECT ? + ? + ? AS "add" | 3 | 3
90-
SELECT ? AS "float" | 1 | 1
91-
SELECT ? AS "int" | 2 | 2
92-
SELECT ? AS i UNION SELECT ? ORDER BY i | 1 | 2
93-
SELECT ? || ? | 1 | 1
94-
SELECT pg_stat_statements_reset() | 1 | 1
95-
WITH t(f) AS ( +| 1 | 2
96-
VALUES (?), (?) +| |
97-
) +| |
98-
SELECT f FROM t ORDER BY f | |
99-
(9 rows)
99+
query | calls | rows
100+
---------------------------------------------------+-------+------
101+
PREPARE pgss_test (int) AS SELECT $1, $2 LIMIT $3 | 1 | 1
102+
SELECT $1 +| 4 | 4
103+
+| |
104+
AS "text" | |
105+
SELECT $1 + $2 | 2 | 2
106+
SELECT $1 + $2 + $3 AS "add" | 3 | 3
107+
SELECT $1 AS "float" | 1 | 1
108+
SELECT $1 AS "int" | 2 | 2
109+
SELECT $1 AS i UNION SELECT $2 ORDER BY i | 1 | 2
110+
SELECT $1 || $2 | 1 | 1
111+
SELECT pg_stat_statements_reset() | 1 | 1
112+
WITH t(f) AS ( +| 1 | 2
113+
VALUES ($1), ($2) +| |
114+
) +| |
115+
SELECT f FROM t ORDER BY f | |
116+
select $1::jsonb ? $2 | 1 | 1
117+
(11 rows)
100118

101119
--
102120
-- CRUD: INSERT SELECT UPDATE DELETE on test table
@@ -108,7 +126,7 @@ SELECT pg_stat_statements_reset();
108126
(1 row)
109127

110128
-- utility "create table" should not be shown
111-
CREATE TABLE test (a int, b char(20));
129+
CREATE TEMP TABLE test (a int, b char(20));
112130
INSERT INTO test VALUES(generate_series(1, 10), 'aaa');
113131
UPDATE test SET b = 'bbb' WHERE a > 7;
114132
DELETE FROM test WHERE a > 9;
@@ -125,6 +143,8 @@ BEGIN \;
125143
UPDATE test SET b = '555' WHERE a = 5 \;
126144
UPDATE test SET b = '666' WHERE a = 6 \;
127145
COMMIT ;
146+
-- many INSERT values
147+
INSERT INTO test (a, b) VALUES (1, 'a'), (2, 'b'), (3, 'c');
128148
-- SELECT with constants
129149
SELECT * FROM test WHERE a > 5 ORDER BY a ;
130150
a | b
@@ -147,28 +167,47 @@ SELECT *
147167
SELECT * FROM test ORDER BY a;
148168
a | b
149169
---+----------------------
170+
1 | a
150171
1 | 111
172+
2 | b
151173
2 | 222
174+
3 | c
152175
3 | 333
153176
4 | 444
154177
5 | 555
155178
6 | 666
156179
7 | aaa
157180
8 | bbb
158181
9 | bbb
159-
(9 rows)
182+
(12 rows)
183+
184+
-- SELECT with IN clause
185+
SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5);
186+
a | b
187+
---+----------------------
188+
1 | 111
189+
2 | 222
190+
3 | 333
191+
4 | 444
192+
5 | 555
193+
1 | a
194+
2 | b
195+
3 | c
196+
(8 rows)
160197

161198
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
162-
query | calls | rows
163-
---------------------------------------------------+-------+------
164-
DELETE FROM test WHERE a > ? | 1 | 1
165-
INSERT INTO test VALUES(generate_series(?, ?), ?) | 1 | 10
166-
SELECT * FROM test ORDER BY a | 1 | 9
167-
SELECT * FROM test WHERE a > ? ORDER BY a | 2 | 4
168-
SELECT pg_stat_statements_reset() | 1 | 1
169-
UPDATE test SET b = ? WHERE a = ? | 6 | 6
170-
UPDATE test SET b = ? WHERE a > ? | 1 | 3
171-
(7 rows)
199+
query | calls | rows
200+
-------------------------------------------------------------+-------+------
201+
DELETE FROM test WHERE a > $1 | 1 | 1
202+
INSERT INTO test (a, b) VALUES ($1, $2), ($3, $4), ($5, $6) | 1 | 3
203+
INSERT INTO test VALUES(generate_series($1, $2), $3) | 1 | 10
204+
SELECT * FROM test ORDER BY a | 1 | 12
205+
SELECT * FROM test WHERE a > $1 ORDER BY a | 2 | 4
206+
SELECT * FROM test WHERE a IN ($1, $2, $3, $4, $5) | 1 | 8
207+
SELECT pg_stat_statements_reset() | 1 | 1
208+
UPDATE test SET b = $1 WHERE a = $2 | 6 | 6
209+
UPDATE test SET b = $1 WHERE a > $2 | 1 | 3
210+
(9 rows)
172211

173212
--
174213
-- pg_stat_statements.track = none
@@ -251,9 +290,9 @@ SELECT PLUS_ONE(10);
251290
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
252291
query | calls | rows
253292
-----------------------------------+-------+------
254-
SELECT ?::TEXT | 1 | 1
255-
SELECT PLUS_ONE(?) | 2 | 2
256-
SELECT PLUS_TWO(?) | 2 | 2
293+
SELECT $1::TEXT | 1 | 1
294+
SELECT PLUS_ONE($1) | 2 | 2
295+
SELECT PLUS_TWO($1) | 2 | 2
257296
SELECT pg_stat_statements_reset() | 1 | 1
258297
(4 rows)
259298

@@ -308,10 +347,10 @@ SELECT PLUS_ONE(1);
308347
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
309348
query | calls | rows
310349
-----------------------------------+-------+------
311-
SELECT (i + ? + ?)::INTEGER | 2 | 2
312-
SELECT (i + ?)::INTEGER LIMIT ? | 2 | 2
313-
SELECT PLUS_ONE(?) | 2 | 2
314-
SELECT PLUS_TWO(?) | 2 | 2
350+
SELECT (i + $2 + $3)::INTEGER | 2 | 2
351+
SELECT (i + $2)::INTEGER LIMIT $3 | 2 | 2
352+
SELECT PLUS_ONE($1) | 2 | 2
353+
SELECT PLUS_TWO($1) | 2 | 2
315354
SELECT pg_stat_statements_reset() | 1 | 1
316355
(5 rows)
317356

@@ -352,7 +391,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
352391
DROP FUNCTION PLUS_TWO(INTEGER) | 1 | 0
353392
DROP TABLE IF EXISTS test | 3 | 0
354393
DROP TABLE test | 1 | 0
355-
SELECT ? | 1 | 1
394+
SELECT $1 | 1 | 1
356395
SELECT pg_stat_statements_reset() | 1 | 1
357396
(8 rows)
358397

‎contrib/pg_stat_statements/pg_stat_statements.c

+30-10
Original file line numberDiff line numberDiff line change
@@ -27,10 +27,10 @@
2727
* to blame query costs on the proper queryId.
2828
*
2929
* To facilitate presenting entries to users, we create "representative" query
30-
* strings in which constants are replaced with '?' characters, to make it
31-
* clearer what a normalized entry can represent. To save on shared memory,
32-
* and to avoid having to truncate oversized query strings, we store these
33-
* strings in a temporary external query-texts file. Offsets into this
30+
* strings in which constants are replaced with parameter symbols ($n), to
31+
* make it clearer what a normalized entry can represent. To save on shared
32+
* memory, and to avoid having to truncate oversized query strings, we store
33+
* these strings in a temporary external query-texts file. Offsets into this
3434
* file are kept in shared memory.
3535
*
3636
* Note about locking issues: to create or delete an entry in the shared
@@ -219,6 +219,9 @@ typedef struct pgssJumbleState
219219

220220
/* Current number of valid entries in clocations array */
221221
int clocations_count;
222+
223+
/* highest Param id we've seen, in order to start normalization correctly */
224+
int highest_extern_param_id;
222225
} pgssJumbleState;
223226

224227
/*---- Local variables ----*/
@@ -803,6 +806,7 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query)
803806
jstate.clocations = (pgssLocationLen *)
804807
palloc(jstate.clocations_buf_size * sizeof(pgssLocationLen));
805808
jstate.clocations_count = 0;
809+
jstate.highest_extern_param_id = 0;
806810

807811
/* Compute query ID and mark the Query node with it */
808812
JumbleQuery(&jstate, query);
@@ -2482,6 +2486,10 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
24822486
APP_JUMB(p->paramkind);
24832487
APP_JUMB(p->paramid);
24842488
APP_JUMB(p->paramtype);
2489+
/* Also, track the highest external Param id */
2490+
if (p->paramkind == PARAM_EXTERN &&
2491+
p->paramid > jstate->highest_extern_param_id)
2492+
jstate->highest_extern_param_id = p->paramid;
24852493
}
24862494
break;
24872495
case T_Aggref:
@@ -2874,7 +2882,7 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
28742882
break;
28752883
case T_TableFunc:
28762884
{
2877-
TableFunc *tablefunc = (TableFunc *) node;
2885+
TableFunc *tablefunc = (TableFunc *) node;
28782886

28792887
JumbleExpr(jstate, tablefunc->docexpr);
28802888
JumbleExpr(jstate, tablefunc->rowexpr);
@@ -2938,7 +2946,8 @@ RecordConstLocation(pgssJumbleState *jstate, int location)
29382946
* of interest, so it's worth doing.)
29392947
*
29402948
* *query_len_p contains the input string length, and is updated with
2941-
* the result string length (which cannot be longer) on exit.
2949+
* the result string length on exit. The resulting string might be longer
2950+
* or shorter depending on what happens with replacement of constants.
29422951
*
29432952
* Returns a palloc'd string.
29442953
*/
@@ -2949,6 +2958,7 @@ generate_normalized_query(pgssJumbleState *jstate, const char *query,
29492958
char *norm_query;
29502959
int query_len = *query_len_p;
29512960
int i,
2961+
norm_query_buflen, /* Space allowed for norm_query */
29522962
len_to_wrt, /* Length (in bytes) to write */
29532963
quer_loc = 0, /* Source query byte location */
29542964
n_quer_loc = 0, /* Normalized query byte location */
@@ -2961,8 +2971,17 @@ generate_normalized_query(pgssJumbleState *jstate, const char *query,
29612971
*/
29622972
fill_in_constant_lengths(jstate, query, query_loc);
29632973

2974+
/*
2975+
* Allow for $n symbols to be longer than the constants they replace.
2976+
* Constants must take at least one byte in text form, while a $n symbol
2977+
* certainly isn't more than 11 bytes, even if n reaches INT_MAX. We
2978+
* could refine that limit based on the max value of n for the current
2979+
* query, but it hardly seems worth any extra effort to do so.
2980+
*/
2981+
norm_query_buflen = query_len + jstate->clocations_count * 10;
2982+
29642983
/* Allocate result buffer */
2965-
norm_query = palloc(query_len + 1);
2984+
norm_query = palloc(norm_query_buflen + 1);
29662985

29672986
for (i = 0; i < jstate->clocations_count; i++)
29682987
{
@@ -2986,8 +3005,9 @@ generate_normalized_query(pgssJumbleState *jstate, const char *query,
29863005
memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt);
29873006
n_quer_loc += len_to_wrt;
29883007

2989-
/* And insert a '?' in place of the constant token */
2990-
norm_query[n_quer_loc++] = '?';
3008+
/* And insert a param symbol in place of the constant token */
3009+
n_quer_loc += sprintf(norm_query + n_quer_loc, "$%d",
3010+
i + 1 + jstate->highest_extern_param_id);
29913011

29923012
quer_loc = off + tok_len;
29933013
last_off = off;
@@ -3004,7 +3024,7 @@ generate_normalized_query(pgssJumbleState *jstate, const char *query,
30043024
memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt);
30053025
n_quer_loc += len_to_wrt;
30063026

3007-
Assert(n_quer_loc <= query_len);
3027+
Assert(n_quer_loc <= norm_query_buflen);
30083028
norm_query[n_quer_loc] = '\0';
30093029

30103030
*query_len_p = n_quer_loc;

‎contrib/pg_stat_statements/sql/pg_stat_statements.sql

+15-1
Original file line numberDiff line numberDiff line change
@@ -37,12 +37,20 @@ SELECT :add + 1 + 1 AS "add" \gset
3737
-- set operator
3838
SELECT 1 AS i UNION SELECT 2 ORDER BY i;
3939

40+
-- ? operator
41+
select '{"a":1, "b":2}'::jsonb ? 'b';
42+
4043
-- cte
4144
WITH t(f) AS (
4245
VALUES (1.0), (2.0)
4346
)
4447
SELECT f FROM t ORDER BY f;
4548

49+
-- prepared statement with parameter
50+
PREPARE pgss_test (int) AS SELECT $1, 'test' LIMIT 1;
51+
EXECUTE pgss_test(1);
52+
DEALLOCATE pgss_test;
53+
4654
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
4755

4856
--
@@ -51,7 +59,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
5159
SELECT pg_stat_statements_reset();
5260

5361
-- utility "create table" should not be shown
54-
CREATE TABLE test (a int, b char(20));
62+
CREATE TEMP TABLE test (a int, b char(20));
5563

5664
INSERT INTO test VALUES(generate_series(1, 10), 'aaa');
5765
UPDATE test SET b = 'bbb' WHERE a > 7;
@@ -74,6 +82,9 @@ UPDATE test SET b = '555' WHERE a = 5 \;
7482
UPDATE test SET b = '666' WHERE a = 6 \;
7583
COMMIT ;
7684

85+
-- many INSERT values
86+
INSERT INTO test (a, b) VALUES (1, 'a'), (2, 'b'), (3, 'c');
87+
7788
-- SELECT with constants
7889
SELECT * FROM test WHERE a > 5 ORDER BY a ;
7990

@@ -85,6 +96,9 @@ SELECT *
8596
-- SELECT without constants
8697
SELECT * FROM test ORDER BY a;
8798

99+
-- SELECT with IN clause
100+
SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5);
101+
88102
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
89103

90104
--

0 commit comments

Comments
 (0)