Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix MERGE with DO NOTHING actions into a partitioned table.

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

    PG Bug reporting form <noreply@postgresql.org> — 2025-03-28T05:00:01Z

    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.

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

    Tender Wang <tndrwang@gmail.com> — 2025-03-28T16:39:58Z

    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

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

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-03-28T18:29:45Z

    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

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

    Tender Wang <tndrwang@gmail.com> — 2025-03-29T02:39:12Z

    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

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

    Gurjeet Singh <gurjeet@singh.im> — 2025-03-29T05:54:50Z

    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

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

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-03-29T10:20:24Z

    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

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

    Tender Wang <tndrwang@gmail.com> — 2025-03-29T10:40:19Z

    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