BUG #18871: Cross-partition MERGE fails with unclear internal error

PG Bug reporting form <noreply@postgresql.org> 于2025年3月28日周五 22:49写道：

The following bug has been logged on the website:

Bug reference: 18871
Logged by: Alexander Lakhin
Email address: exclusion@gmail.com
PostgreSQL version: 17.4
Operating system: Ubuntu 24.04
Description:

The following script:
CREATE TABLE pt(a int, b int) PARTITION BY LIST(a);
CREATE TABLE p1 PARTITION OF pt FOR VALUES IN (a);

MERGE INTO pt USING (SELECT 2 AS a) AS q ON pt.a = q.a
WHEN NOT MATCHED THEN INSERT VALUES (1, 2)
WHEN MATCHED THEN DO NOTHING;
fails with:
ERROR: XX000: unknown action in MERGE WHEN clause
LOCATION: ExecInitPartitionInfo, execPartition.c:962

This error is triggered on REL_15_STABLE (starting from bf5c4b3d9) ..
master.
Before bf5c4b3d9, the MERGE produced ERROR: variable not found in subplan
target lists.

This anomaly is discovered with SQLsmith.

I tried a quick fix by adding "case CMD_NOTHING" after "case CMD_DELETE"
like below:

case CMD_DELETE:
case CMD_NOTHING:
break;

It can work, and all regression tests pass. But I don't look into more
details. This solution may not fix the root cause.

--
Thanks,
Tender Wang

On Fri, 28 Mar 2025 at 16:40, Tender Wang <tndrwang@gmail.com> wrote:

I tried a quick fix by adding "case CMD_NOTHING" after "case CMD_DELETE" like below:

case CMD_DELETE:
case CMD_NOTHING:
break;

It can work, and all regression tests pass. But I don't look into more details. This solution may not fix the root cause.

Yes, that looks like the right fix.

This appears to be a trivial oversight in ExecInitPartitionInfo(). I
did a quick scan of other places that look at action->commandType and
I didn't see any others that overlook CMD_NOTHING.

Regards,
Dean

Dean Rasheed <dean.a.rasheed@gmail.com> 于2025年3月29日周六 02:29写道：

On Fri, 28 Mar 2025 at 16:40, Tender Wang <tndrwang@gmail.com> wrote:

I tried a quick fix by adding "case CMD_NOTHING" after "case CMD_DELETE"

like below:

case CMD_DELETE:
case CMD_NOTHING:
break;

It can work, and all regression tests pass. But I don't look into more

details. This solution may not fix the root cause.

Yes, that looks like the right fix.

This appears to be a trivial oversight in ExecInitPartitionInfo(). I
did a quick scan of other places that look at action->commandType and
I didn't see any others that overlook CMD_NOTHING.

Yes, indeed. I double-check it.
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.

--
Thanks,
Tender Wang

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.et

On Sat, 29 Mar 2025 at 05:54, Gurjeet Singh <gurjeet@singh.im> wrote:

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.

I would've expecetd the comment to be in ExecInitMerge(), asking the
reader to look at other places the logic is duplicated.

There already is such a comment a little further up in
ExecInitMerge(), so I didn't bother repeating it.

I've committed the fix, except that I put the test case in merge.sql,
since part_abc_view doesn't exist in back branches of
partition_prune.sql, and it feels more like a MERGE bug anyway.

Thanks, all.

Regards,
Dean

Dean Rasheed <dean.a.rasheed@gmail.com> 于2025年3月29日周六 18:20写道：

On Sat, 29 Mar 2025 at 05:54, Gurjeet Singh <gurjeet@singh.im> wrote:

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.

I would've expecetd the comment to be in ExecInitMerge(), asking the
reader to look at other places the logic is duplicated.

There already is such a comment a little further up in
ExecInitMerge(), so I didn't bother repeating it.

I've committed the fix, except that I put the test case in merge.sql,
since part_abc_view doesn't exist in back branches of
partition_prune.sql, and it feels more like a MERGE bug anyway.

Agree.
Thanks for pushing.

--
Thanks,
Tender Wang