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

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.

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

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