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

Commit72abf03

Browse files
committed
Fix pg_stat_statements for MERGE
We weren't jumbling the merge action list, so wildly different commandswould be considered to use the same query ID. Add that, mention it inthe 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 parentd1f95fa commit72abf03

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 testWHERE aIN (1,2,3,4,5);
102102

103+
-- MERGE
104+
MERGE INTO test USING test stON (st.a=test.aANDst.a>=4)
105+
WHEN MATCHED THENUPDATESET b=st.b||st.a::text;
106+
MERGE INTO test USING test stON (st.a=test.aANDst.a>=4)
107+
WHEN MATCHED THENUPDATESET b=test.b||st.a::text;
108+
MERGE INTO test USING test stON (st.a=test.aANDst.a>=4)
109+
WHEN MATCHEDAND length(st.b)>1 THENUPDATESET b=test.b||st.a::text;
110+
MERGE INTO test USING test stON (st.a=test.a)
111+
WHEN NOT MATCHED THEN INSERT (a, b)VALUES (0,NULL);
112+
MERGE INTO test USING test stON (st.a=test.a)
113+
WHEN NOT MATCHED THEN INSERTVALUES (0,NULL);-- same as above
114+
MERGE INTO test USING test stON (st.a=test.a)
115+
WHEN NOT MATCHED THEN INSERT (b, a)VALUES (NULL,0);
116+
MERGE INTO test USING test stON (st.a=test.a)
117+
WHEN NOT MATCHED THEN INSERT (a)VALUES (0);
118+
MERGE INTO test USING test stON (st.a=test.aANDst.a>=4)
119+
WHEN MATCHED THENDELETE;
120+
MERGE INTO test USING test stON (st.a=test.aANDst.a>=4)
121+
WHEN MATCHED THEN DO NOTHING;
122+
MERGE INTO test USING test stON (st.a=test.aANDst.a>=4)
123+
WHEN NOT MATCHED THEN DO NOTHING;
124+
103125
SELECT query, calls, rowsFROM pg_stat_statementsORDER 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
caseT_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+
caseT_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
caseT_List:
741752
foreach(temp, (List*)node)
742753
{

0 commit comments

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