Skip to content

Commit 72abf03

Browse files
alvherrealvherre
committed
Fix pg_stat_statements for MERGE
We weren't jumbling the merge action list, so wildly different commands would be considered to use the same query ID. Add that, mention it in the docs, and some test lines. Backpatch to 15. Author: Tatsu <bt22nakamorit@oss.nttdata.com> Reviewed-by: Julien Rouhaud <rjuju123@gmail.com> Discussion: https://postgr.es/m/d87e391694db75a038abc3b2597828e8@oss.nttdata.com
1 parent d1f95fa commit 72abf03

File tree

5 files changed

+77
-5
lines changed

5 files changed

+77
-5
lines changed

‎contrib/pg_stat_statements/expected/pg_stat_statements.out‎

Lines changed: 40 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -222,20 +222,59 @@ SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5);
222222
3 | c
223223
(8 rows)
224224

225+
-- MERGE
226+
MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
227+
WHEN MATCHED THEN UPDATE SET b = st.b || st.a::text;
228+
MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
229+
WHEN MATCHED THEN UPDATE SET b = test.b || st.a::text;
230+
MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
231+
WHEN MATCHED AND length(st.b) > 1 THEN UPDATE SET b = test.b || st.a::text;
232+
MERGE INTO test USING test st ON (st.a = test.a)
233+
WHEN NOT MATCHED THEN INSERT (a, b) VALUES (0, NULL);
234+
MERGE INTO test USING test st ON (st.a = test.a)
235+
WHEN NOT MATCHED THEN INSERT VALUES (0, NULL); -- same as above
236+
MERGE INTO test USING test st ON (st.a = test.a)
237+
WHEN NOT MATCHED THEN INSERT (b, a) VALUES (NULL, 0);
238+
MERGE INTO test USING test st ON (st.a = test.a)
239+
WHEN NOT MATCHED THEN INSERT (a) VALUES (0);
240+
MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
241+
WHEN MATCHED THEN DELETE;
242+
MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
243+
WHEN MATCHED THEN DO NOTHING;
244+
MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
245+
WHEN NOT MATCHED THEN DO NOTHING;
225246
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
226247
query | calls | rows
227248
------------------------------------------------------------------------------+-------+------
228249
DELETE FROM test WHERE a > $1 | 1 | 1
229250
INSERT INTO test (a, b) VALUES ($1, $2), ($3, $4), ($5, $6) | 1 | 3
230251
INSERT INTO test VALUES(generate_series($1, $2), $3) | 1 | 10
252+
MERGE INTO test USING test st ON (st.a = test.a AND st.a >= $1) +| 1 | 6
253+
WHEN MATCHED AND length(st.b) > $2 THEN UPDATE SET b = test.b || st.a::text | |
254+
MERGE INTO test USING test st ON (st.a = test.a AND st.a >= $1) +| 1 | 6
255+
WHEN MATCHED THEN DELETE | |
256+
MERGE INTO test USING test st ON (st.a = test.a AND st.a >= $1) +| 1 | 0
257+
WHEN MATCHED THEN DO NOTHING | |
258+
MERGE INTO test USING test st ON (st.a = test.a AND st.a >= $1) +| 1 | 6
259+
WHEN MATCHED THEN UPDATE SET b = st.b || st.a::text | |
260+
MERGE INTO test USING test st ON (st.a = test.a AND st.a >= $1) +| 1 | 6
261+
WHEN MATCHED THEN UPDATE SET b = test.b || st.a::text | |
262+
MERGE INTO test USING test st ON (st.a = test.a AND st.a >= $1) +| 1 | 0
263+
WHEN NOT MATCHED THEN DO NOTHING | |
264+
MERGE INTO test USING test st ON (st.a = test.a) +| 1 | 0
265+
WHEN NOT MATCHED THEN INSERT (a) VALUES ($1) | |
266+
MERGE INTO test USING test st ON (st.a = test.a) +| 2 | 0
267+
WHEN NOT MATCHED THEN INSERT (a, b) VALUES ($1, $2) | |
268+
MERGE INTO test USING test st ON (st.a = test.a) +| 1 | 0
269+
WHEN NOT MATCHED THEN INSERT (b, a) VALUES ($1, $2) | |
231270
SELECT * FROM test ORDER BY a | 1 | 12
232271
SELECT * FROM test WHERE a > $1 ORDER BY a | 2 | 4
233272
SELECT * FROM test WHERE a IN ($1, $2, $3, $4, $5) | 1 | 8
234273
SELECT pg_stat_statements_reset() | 1 | 1
235274
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0
236275
UPDATE test SET b = $1 WHERE a = $2 | 6 | 6
237276
UPDATE test SET b = $1 WHERE a > $2 | 1 | 3
238-
(10 rows)
277+
(19 rows)
239278

240279
--
241280
-- INSERT, UPDATE, DELETE on test table to validate WAL generation metrics

‎contrib/pg_stat_statements/sql/pg_stat_statements.sql‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -100,6 +100,28 @@ SELECT * FROM test ORDER BY a;
100100
-- SELECT with IN clause
101101
SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5);
102102

103+
-- MERGE
104+
MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
105+
WHEN MATCHED THEN UPDATE SET b = st.b || st.a::text;
106+
MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
107+
WHEN MATCHED THEN UPDATE SET b = test.b || st.a::text;
108+
MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
109+
WHEN MATCHED AND length(st.b) > 1 THEN UPDATE SET b = test.b || st.a::text;
110+
MERGE INTO test USING test st ON (st.a = test.a)
111+
WHEN NOT MATCHED THEN INSERT (a, b) VALUES (0, NULL);
112+
MERGE INTO test USING test st ON (st.a = test.a)
113+
WHEN NOT MATCHED THEN INSERT VALUES (0, NULL); -- same as above
114+
MERGE INTO test USING test st ON (st.a = test.a)
115+
WHEN NOT MATCHED THEN INSERT (b, a) VALUES (NULL, 0);
116+
MERGE INTO test USING test st ON (st.a = test.a)
117+
WHEN NOT MATCHED THEN INSERT (a) VALUES (0);
118+
MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
119+
WHEN MATCHED THEN DELETE;
120+
MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
121+
WHEN MATCHED THEN DO NOTHING;
122+
MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
123+
WHEN NOT MATCHED THEN DO NOTHING;
124+
103125
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
104126

105127
--

‎doc/src/sgml/pgstatstatements.sgml‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -487,7 +487,7 @@
487487

488488
<para>
489489
Plannable queries (that is, <command>SELECT</command>, <command>INSERT</command>,
490-
<command>UPDATE</command>, and <command>DELETE</command>) are combined into a single
490+
<command>UPDATE</command>, <command>DELETE</command>, and <command>MERGE</command>) are combined into a single
491491
<structname>pg_stat_statements</structname> entry whenever they have identical query
492492
structures according to an internal hash calculation. Typically, two
493493
queries will be considered the same for this purpose if they are
@@ -783,7 +783,7 @@
783783
<varname>pg_stat_statements.track_utility</varname> controls whether
784784
utility commands are tracked by the module. Utility commands are
785785
all those other than <command>SELECT</command>, <command>INSERT</command>,
786-
<command>UPDATE</command> and <command>DELETE</command>.
786+
<command>UPDATE</command>, <command>DELETE</command>, and <command>MERGE</command>.
787787
The default value is <literal>on</literal>.
788788
Only superusers can change this setting.
789789
</para>

‎src/backend/nodes/nodeFuncs.c‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2266,10 +2266,10 @@ expression_tree_walker(Node *node,
22662266
{
22672267
MergeAction *action = (MergeAction *) node;
22682268

2269-
if (walker(action->targetList, context))
2270-
return true;
22712269
if (walker(action->qual, context))
22722270
return true;
2271+
if (walker(action->targetList, context))
2272+
return true;
22732273
}
22742274
break;
22752275
case T_PartitionPruneStepOp:

‎src/backend/utils/misc/queryjumble.c‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -247,6 +247,7 @@ JumbleQueryInternal(JumbleState *jstate, Query *query)
247247
JumbleExpr(jstate, (Node *) query->cteList);
248248
JumbleRangeTable(jstate, query->rtable);
249249
JumbleExpr(jstate, (Node *) query->jointree);
250+
JumbleExpr(jstate, (Node *) query->mergeActionList);
250251
JumbleExpr(jstate, (Node *) query->targetList);
251252
JumbleExpr(jstate, (Node *) query->onConflict);
252253
JumbleExpr(jstate, (Node *) query->returningList);
@@ -737,6 +738,16 @@ JumbleExpr(JumbleState *jstate, Node *node)
737738
JumbleExpr(jstate, (Node *) conf->exclRelTlist);
738739
}
739740
break;
741+
case T_MergeAction:
742+
{
743+
MergeAction *mergeaction = (MergeAction *) node;
744+
745+
APP_JUMB(mergeaction->matched);
746+
APP_JUMB(mergeaction->commandType);
747+
JumbleExpr(jstate, mergeaction->qual);
748+
JumbleExpr(jstate, (Node *) mergeaction->targetList);
749+
}
750+
break;
740751
case T_List:
741752
foreach(temp, (List *) node)
742753
{

0 commit comments

Comments
 (0)