agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Alvaro Herrera <[email protected]>
Subject: [PATCH v2] Fix pg_stat_statements for MERGE
Date: Mon, 26 Sep 2022 13:27:24 +0200
Author: Tatsu <[email protected]>
Reviewed-by: Julien Rouhaud <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
.../expected/pg_stat_statements.out | 41 ++++++++++++++++++-
.../sql/pg_stat_statements.sql | 22 ++++++++++
doc/src/sgml/pgstatstatements.sgml | 4 +-
src/backend/nodes/nodeFuncs.c | 4 +-
src/backend/utils/misc/queryjumble.c | 11 +++++
5 files changed, 77 insertions(+), 5 deletions(-)
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index ff0166fb9d..9ac5c87c3a 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -222,12 +222,51 @@ SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5);
3 | c
(8 rows)
+-- MERGE
+MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
+ WHEN MATCHED THEN UPDATE SET b = st.b || st.a::text;
+MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
+ WHEN MATCHED THEN UPDATE SET b = test.b || st.a::text;
+MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
+ WHEN MATCHED AND length(st.b) > 1 THEN UPDATE SET b = test.b || st.a::text;
+MERGE INTO test USING test st ON (st.a = test.a)
+ WHEN NOT MATCHED THEN INSERT (a, b) VALUES (0, NULL);
+MERGE INTO test USING test st ON (st.a = test.a)
+ WHEN NOT MATCHED THEN INSERT VALUES (0, NULL); -- same as above
+MERGE INTO test USING test st ON (st.a = test.a)
+ WHEN NOT MATCHED THEN INSERT (b, a) VALUES (NULL, 0);
+MERGE INTO test USING test st ON (st.a = test.a)
+ WHEN NOT MATCHED THEN INSERT (a) VALUES (0);
+MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
+ WHEN MATCHED THEN DELETE;
+MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
+ WHEN MATCHED THEN DO NOTHING;
+MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
+ WHEN NOT MATCHED THEN DO NOTHING;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls | rows
------------------------------------------------------------------------------+-------+------
DELETE FROM test WHERE a > $1 | 1 | 1
INSERT INTO test (a, b) VALUES ($1, $2), ($3, $4), ($5, $6) | 1 | 3
INSERT INTO test VALUES(generate_series($1, $2), $3) | 1 | 10
+ MERGE INTO test USING test st ON (st.a = test.a AND st.a >= $1) +| 1 | 6
+ WHEN MATCHED AND length(st.b) > $2 THEN UPDATE SET b = test.b || st.a::text | |
+ MERGE INTO test USING test st ON (st.a = test.a AND st.a >= $1) +| 1 | 6
+ WHEN MATCHED THEN DELETE | |
+ MERGE INTO test USING test st ON (st.a = test.a AND st.a >= $1) +| 1 | 0
+ WHEN MATCHED THEN DO NOTHING | |
+ MERGE INTO test USING test st ON (st.a = test.a AND st.a >= $1) +| 1 | 6
+ WHEN MATCHED THEN UPDATE SET b = st.b || st.a::text | |
+ MERGE INTO test USING test st ON (st.a = test.a AND st.a >= $1) +| 1 | 6
+ WHEN MATCHED THEN UPDATE SET b = test.b || st.a::text | |
+ MERGE INTO test USING test st ON (st.a = test.a AND st.a >= $1) +| 1 | 0
+ WHEN NOT MATCHED THEN DO NOTHING | |
+ MERGE INTO test USING test st ON (st.a = test.a) +| 1 | 0
+ WHEN NOT MATCHED THEN INSERT (a) VALUES ($1) | |
+ MERGE INTO test USING test st ON (st.a = test.a) +| 2 | 0
+ WHEN NOT MATCHED THEN INSERT (a, b) VALUES ($1, $2) | |
+ MERGE INTO test USING test st ON (st.a = test.a) +| 1 | 0
+ WHEN NOT MATCHED THEN INSERT (b, a) VALUES ($1, $2) | |
SELECT * FROM test ORDER BY a | 1 | 12
SELECT * FROM test WHERE a > $1 ORDER BY a | 2 | 4
SELECT * FROM test WHERE a IN ($1, $2, $3, $4, $5) | 1 | 8
@@ -235,7 +274,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0
UPDATE test SET b = $1 WHERE a = $2 | 6 | 6
UPDATE test SET b = $1 WHERE a > $2 | 1 | 3
-(10 rows)
+(19 rows)
--
-- INSERT, UPDATE, DELETE on test table to validate WAL generation metrics
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index a01f183727..8f5c866225 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -100,6 +100,28 @@ SELECT * FROM test ORDER BY a;
-- SELECT with IN clause
SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5);
+-- MERGE
+MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
+ WHEN MATCHED THEN UPDATE SET b = st.b || st.a::text;
+MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
+ WHEN MATCHED THEN UPDATE SET b = test.b || st.a::text;
+MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
+ WHEN MATCHED AND length(st.b) > 1 THEN UPDATE SET b = test.b || st.a::text;
+MERGE INTO test USING test st ON (st.a = test.a)
+ WHEN NOT MATCHED THEN INSERT (a, b) VALUES (0, NULL);
+MERGE INTO test USING test st ON (st.a = test.a)
+ WHEN NOT MATCHED THEN INSERT VALUES (0, NULL); -- same as above
+MERGE INTO test USING test st ON (st.a = test.a)
+ WHEN NOT MATCHED THEN INSERT (b, a) VALUES (NULL, 0);
+MERGE INTO test USING test st ON (st.a = test.a)
+ WHEN NOT MATCHED THEN INSERT (a) VALUES (0);
+MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
+ WHEN MATCHED THEN DELETE;
+MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
+ WHEN MATCHED THEN DO NOTHING;
+MERGE INTO test USING test st ON (st.a = test.a AND st.a >= 4)
+ WHEN NOT MATCHED THEN DO NOTHING;
+
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
--
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index ecf6cd6bf3..ea90365c7f 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -487,7 +487,7 @@
<para>
Plannable queries (that is, <command>SELECT</command>, <command>INSERT</command>,
- <command>UPDATE</command>, and <command>DELETE</command>) are combined into a single
+ <command>UPDATE</command>, <command>DELETE</command>, and <command>MERGE</command>) are combined into a single
<structname>pg_stat_statements</structname> entry whenever they have identical query
structures according to an internal hash calculation. Typically, two
queries will be considered the same for this purpose if they are
@@ -783,7 +783,7 @@
<varname>pg_stat_statements.track_utility</varname> controls whether
utility commands are tracked by the module. Utility commands are
all those other than <command>SELECT</command>, <command>INSERT</command>,
- <command>UPDATE</command> and <command>DELETE</command>.
+ <command>UPDATE</command>, <command>DELETE</command>, and <command>MERGE</command>.
The default value is <literal>on</literal>.
Only superusers can change this setting.
</para>
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 724d076674..0a7b22f97e 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -2259,10 +2259,10 @@ expression_tree_walker_impl(Node *node,
{
MergeAction *action = (MergeAction *) node;
- if (WALK(action->targetList))
- return true;
if (WALK(action->qual))
return true;
+ if (WALK(action->targetList))
+ return true;
}
break;
case T_PartitionPruneStepOp:
diff --git a/src/backend/utils/misc/queryjumble.c b/src/backend/utils/misc/queryjumble.c
index 6e75acda27..e795115c2e 100644
--- a/src/backend/utils/misc/queryjumble.c
+++ b/src/backend/utils/misc/queryjumble.c
@@ -258,6 +258,7 @@ JumbleQueryInternal(JumbleState *jstate, Query *query)
JumbleExpr(jstate, (Node *) query->windowClause);
JumbleExpr(jstate, (Node *) query->distinctClause);
JumbleExpr(jstate, (Node *) query->sortClause);
+ JumbleExpr(jstate, (Node *) query->mergeActionList);
JumbleExpr(jstate, query->limitOffset);
JumbleExpr(jstate, query->limitCount);
APP_JUMB(query->limitOption);
@@ -738,6 +739,16 @@ JumbleExpr(JumbleState *jstate, Node *node)
JumbleExpr(jstate, (Node *) conf->exclRelTlist);
}
break;
+ case T_MergeAction:
+ {
+ MergeAction *mergeaction = (MergeAction *) node;
+
+ APP_JUMB(mergeaction->matched);
+ APP_JUMB(mergeaction->commandType);
+ JumbleExpr(jstate, mergeaction->qual);
+ JumbleExpr(jstate, (Node *) mergeaction->targetList);
+ }
+ break;
case T_List:
foreach(temp, (List *) node)
{
--
2.30.2
--5bd547n3jbf3jvum--
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v2] Fix pg_stat_statements for MERGE
In-Reply-To: <no-message-id-122147@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox