Skip to content

Commit 92c77ef

Browse files
kelvichkelvich
committed
Merge branch 'master' into logical_twophase
2 parents 942d916 + a6f22e8 commit 92c77ef

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

Lines changed: 76 additions & 37 deletions
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

Lines changed: 30 additions & 10 deletions
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

Lines changed: 15 additions & 1 deletion
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)