From: Gurjeet Singh <gurjeet@singh.im>
To: Tender Wang <tndrwang@gmail.com>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: exclusion@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18871: Cross-partition MERGE fails with unclear internal error
Date: Fri, 28 Mar 2025 22:54:50 -0700
Message-ID: <D8SIN5I77I7S.4YF5B429CIGO@singh.im> (raw)
In-Reply-To: <CAHewXNkN7fN7sh1VapmOb+C7BW+tm9U=0T4TSs5=bEF+-A0u8w@mail.gmail.com>
References: <18871-b44e3c96de3bd2e8@postgresql.org>
<CAHewXNmteprb6V1REydM7ehMB0nM9RX4FAEkAFseC2hOy0rY=w@mail.gmail.com>
<CAEZATCVg_9pUorFJxA2+RzVo1TEcgyPGSrk4gBojR2-NUH9HxQ@mail.gmail.com>
<CAHewXNkN7fN7sh1VapmOb+C7BW+tm9U=0T4TSs5=bEF+-A0u8w@mail.gmail.com>
On Fri Mar 28, 2025 at 7:39 PM PDT, Tender Wang wrote:
> In ExecInitPartitionInfo(), it has comments like:
>
> * This duplicates much of the logic in ExecInitMerge(), so something
> * changes there, look here too.
>
> ExecInitMerge() considers CMD_NOTHING operation.
> I give the attached fix.
I would've expecetd the comment to be in ExecInitMerge(), asking the
reader to look at other places the logic is duplicated.
Attached is a patch with an attempt at that; it includes the diff from
your patch, too, so I named it v2.
Best regards,
Gurjeet
http://Gurje.etAttachments:0002-Fix-cross-partition-merge-fail.patch (text/plain, 2.8K) downloaddiff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.cindex 84ccd7d457d..8d1e3c0624f 100644--- a/src/backend/executor/execPartition.c+++ b/src/backend/executor/execPartition.c@@ -956,6 +956,7 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate,
NULL);
break;
case CMD_DELETE:
+ case CMD_NOTHING:
break;
default:
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.cindex 87c820276a8..381847cfdf3 100644--- a/src/backend/executor/nodeModifyTable.c+++ b/src/backend/executor/nodeModifyTable.c@@ -3712,6 +3712,10 @@ ExecInitMerge(ModifyTableState *mtstate, EState *estate)
lappend(resultRelInfo->ri_MergeActions[action->matchKind],
action_state);
+ /*+ * ExecInitPartitionInfo() duplicates much of the logic here, so if+ * you change something here, look there, too.+ */
switch (action->commandType)
{
case CMD_INSERT:
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.outindex 0bf35260b46..2f3e5db8252 100644--- a/src/test/regress/expected/partition_prune.out+++ b/src/test/regress/expected/partition_prune.out@@ -4694,6 +4694,34 @@ table part_abc_view;
2 | c | t
(2 rows)
+rollback;+-- Cross-partition merge+begin;+explain (costs off)+merge into part_abc_view pt+using (select 3 as a) as q on pt.a = q.a+WHEN NOT MATCHED THEN INSERT VALUES (1, 2)+WHEN MATCHED THEN DO NOTHING;+ QUERY PLAN +--------------------------------------+ Merge on part_abc+ -> Nested Loop Left Join+ -> Result+ -> Result+ One-Time Filter: false+(5 rows)++merge into part_abc_view pt+using (select 3 as a) as q on pt.a = q.a+WHEN NOT MATCHED THEN INSERT VALUES (1, 2)+WHEN MATCHED THEN DO NOTHING;+table part_abc_view;+ a | b | c +---+---+---+ 1 | 2 | + 2 | c | t+(2 rows)+
rollback;
-- A case with multiple ModifyTable nodes.
begin;
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sqlindex f6db9479f54..339c8d9a4e8 100644--- a/src/test/regress/sql/partition_prune.sql+++ b/src/test/regress/sql/partition_prune.sql@@ -1413,6 +1413,20 @@ when not matched then insert values (1, 'd', false) returning pt.a;
table part_abc_view;
rollback;
+-- Cross-partition merge+begin;+explain (costs off)+merge into part_abc_view pt+using (select 3 as a) as q on pt.a = q.a+WHEN NOT MATCHED THEN INSERT VALUES (1, 2)+WHEN MATCHED THEN DO NOTHING;+merge into part_abc_view pt+using (select 3 as a) as q on pt.a = q.a+WHEN NOT MATCHED THEN INSERT VALUES (1, 2)+WHEN MATCHED THEN DO NOTHING;+table part_abc_view;+rollback;+
-- A case with multiple ModifyTable nodes.
begin;
create table part_abc_log (action text, a int, b text, c bool);
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: pgsql-bugs@postgresql.org
Cc: gurjeet@singh.im, tndrwang@gmail.com, dean.a.rasheed@gmail.com, exclusion@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18871: Cross-partition MERGE fails with unclear internal error
In-Reply-To: <D8SIN5I77I7S.4YF5B429CIGO@singh.im>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox