Horton

Thread

Commits

  1. Fix MERGE into a plain inheritance parent table.

  2. Fix problems when a plain-inheritance parent table is excluded.

  1. MERGE issues around inheritance

    Andres Freund <andres@anarazel.de> — 2025-05-21T15:34:43Z

    Hi,

In [1] I added some verification to projection building, to check if the
tupleslot passed to ExecBuildProjectionInfo() is compatible with the target
list.  One query in merge.sql [2] got flagged.

Trying to debug that issue, I found another problem. This leaves us with:

1) w/ inheritance INSERTed rows are not returned by RETURNING. This seems to
   just generally not work with MERGE

2) w/ inheritance the projection for insert that ExecInitMerge() builds
   sometimes uses mismatching columns between the slot passed to
   ExecBuildProjectionInfo() and tgtslot.  The only reason this doesn't seem
   to immediately crash is 1).

   E.g. the attached repro-merge-inheritance.sql triggers [3] if the attached
   debugging patch is applied.


I wouldn't be surprised if there were other issues around inheritance with
mismatched column (order), but I didn't look further.


Greetings,

Andres Freund

[1] https://postgr.es/m/smj2mopf4t654xinuyyq4azik2d5zp2g3lsv2o7vficegqq5c5%40v35iq4llqjpw

[2] MERGE into measurement m
 USING new_measurement nm ON
      (m.city_id = nm.city_id and m.logdate=nm.logdate)
WHEN MATCHED AND nm.peaktemp IS NULL THEN DELETE
WHEN MATCHED THEN UPDATE
     SET peaktemp = greatest(m.peaktemp, nm.peaktemp),
        unitsales = m.unitsales + coalesce(nm.unitsales, 0)
WHEN NOT MATCHED THEN INSERT
     (city_id, logdate, peaktemp, unitsales)
   VALUES (city_id, logdate, peaktemp, unitsales);

triggers

2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] WARNING:  type mismatch: resno 1: slot (type: 0, name: ........pg.dropped.1........) vs expr (type: 23, name: city_id)
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] WARNING:  TargetEntry:
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] DETAIL:     {TARGETENTRY
	   :expr
	      {VAR
	      :varno -1
	      :varattno 3
	      :vartype 23
	      :vartypmod -1
	      :varcollid 0
	      :varnullingrels (b)
	      :varlevelsup 0
	      :varreturningtype 0
	      :varnosyn 2
	      :varattnosyn 1
	      :location 379
	      }
	   :resno 1
	   :resname city_id
	   :ressortgroupref 0
	   :resorigtbl 0
	   :resorigcol 0
	   :resjunk false
	   }

2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] WARNING:  type mismatch: resno 2: slot (type: 23, name: peaktemp) vs expr (type: 1082, name: logdate)
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] WARNING:  TargetEntry:
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] DETAIL:     {TARGETENTRY
	   :expr
	      {VAR
	      :varno -1
	      :varattno 4
	      :vartype 1082
	      :vartypmod -1
	      :varcollid 0
	      :varnullingrels (b)
	      :varlevelsup 0
	      :varreturningtype 0
	      :varnosyn 2
	      :varattnosyn 2
	      :location 388
	      }
	   :resno 2
	   :resname logdate
	   :ressortgroupref 0
	   :resorigtbl 0
	   :resorigcol 0
	   :resjunk false
	   }

2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] WARNING:  type mismatch: resno 3: slot (type: 1082, name: logdate) vs expr (type: 23, name: peaktemp)
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] WARNING:  TargetEntry:
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] DETAIL:     {TARGETENTRY
	   :expr
	      {VAR
	      :varno -1
	      :varattno 1
	      :vartype 23
	      :vartypmod -1
	      :varcollid 0
	      :varnullingrels (b)
	      :varlevelsup 0
	      :varreturningtype 0
	      :varnosyn 2
	      :varattnosyn 3
	      :location 397
	      }
	   :resno 3
	   :resname peaktemp
	   :ressortgroupref 0
	   :resorigtbl 0
	   :resorigcol 0
	   :resjunk false
	   }

2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] WARNING:  type mismatch: resno 5: slot (type: 23, name: unitsales) vs expr (type: 25, name: last_action)
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] WARNING:  TargetEntry:
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] DETAIL:     {TARGETENTRY
	   :expr
	      {CONST
	      :consttype 25
	      :consttypmod -1
	      :constcollid 100
	      :constlen -1
	      :constbyval false
	      :constisnull false
	      :location 418
	      :constvalue 16 [ 64 0 0 0 109 101 114 103 101 45 105 110 115 101 114 116
	       ]
	      }
	   :resno 5
	   :resname last_action
	   :ressortgroupref 0
	   :resorigtbl 0
	   :resorigcol 0
	   :resjunk false
	   }

[3] 2025-05-21 11:24:55.111 EDT [1838296][client backend][0/16:0][psql] WARNING:  type mismatch: resno 1: slot (type: 1700, name: dropme) vs expr (type: 25, name: key)
2025-05-21 11:24:55.112 EDT [1838296][client backend][0/16:0][psql] WARNING:  TargetEntry:
2025-05-21 11:24:55.112 EDT [1838296][client backend][0/16:0][psql] DETAIL:     {TARGETENTRY
	   :expr
	      {VAR
	      :varno -1
	      :varattno 1
	      :vartype 25
	      :vartypmod -1
	      :varcollid 100
	      :varnullingrels (b)
	      :varlevelsup 0
	      :varreturningtype 0
	      :varnosyn 2
	      :varattnosyn 1
	      :location 179
	      }
	   :resno 1
	   :resname key
	   :ressortgroupref 0
	   :resorigtbl 0
	   :resorigcol 0
	   :resjunk false
	   }

  2. Re: MERGE issues around inheritance

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-05-24T09:10:44Z

    On 2025-May-21, Andres Freund wrote:

> Hi,
> 
> In [1] I added some verification to projection building, to check if the
> tupleslot passed to ExecBuildProjectionInfo() is compatible with the target
> list.  One query in merge.sql [2] got flagged.
> 
> Trying to debug that issue, I found another problem. This leaves us with:
> 
> 1) w/ inheritance INSERTed rows are not returned by RETURNING. This seems to
>    just generally not work with MERGE

Hmm, curious.  One thing to observe is that the original source tuple is
in the child table, but the tuple inserted by MERGE ends up in the
parent table.  I'm guessing that the code gets confused as to the
relation that the tuple in the returned slot comes from, and that
somehow makes it somehow not "see" the tuple to process for RETURNING?
I dunno.  CC'ing Dean, who is more familiar with this code than I am.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"I love the Postgres community. It's all about doing things _properly_. :-)"
(David Garamond)

  3. Re: MERGE issues around inheritance

    Tender Wang <tndrwang@gmail.com> — 2025-05-25T11:17:38Z

    Álvaro Herrera <alvherre@alvh.no-ip.org> 于2025年5月24日周六 17:11写道：

> On 2025-May-21, Andres Freund wrote:
>
> > Hi,
> >
> > In [1] I added some verification to projection building, to check if the
> > tupleslot passed to ExecBuildProjectionInfo() is compatible with the
> target
> > list.  One query in merge.sql [2] got flagged.
> >
> > Trying to debug that issue, I found another problem. This leaves us with:
> >
> > 1) w/ inheritance INSERTed rows are not returned by RETURNING. This
> seems to
> >    just generally not work with MERGE
>
> Hmm, curious.  One thing to observe is that the original source tuple is
> in the child table, but the tuple inserted by MERGE ends up in the
> parent table.  I'm guessing that the code gets confused as to the
> relation that the tuple in the returned slot comes from, and that
> somehow makes it somehow not "see" the tuple to process for RETURNING?
> I dunno.  CC'ing Dean, who is more familiar with this code than I am.
>

 In ExecMergeNotMatched(), we passed the mtstate->rootResultRelInfo to
ExecInsert().
In this case,  the ri_projectReturning of mtstate->rootResultRelInfo  is
NULL,  in ExecInsert(),
the "if (resultRelInfo->ri_projectReturning)" branch will not run, so
inheritance INSERTed rows are not returned by RETURNING.

The mtstate->rootResultRelInfo  assigned in ExecInitModifyTable()  is only
here:
if (node->rootRelation > 0)
{
     Assert(bms_is_member(node->rootRelation, estate->es_unpruned_relids));
     mtstate->rootResultRelInfo = makeNode(ResultRelInfo);
     ExecInitResultRelation(estate, mtstate->rootResultRelInfo,
             node->rootRelation);
}
The ri_projectReturning is not assigned.

I try to pass resultRelInfo to ExecInsert, the inherited INSERTed rows are
returned by RETURNING.
But some test cases in regression failed.

-- 
Thanks,
Tender Wang

  4. Re: MERGE issues around inheritance

    Tender Wang <tndrwang@gmail.com> — 2025-05-25T12:05:59Z

    Tender Wang <tndrwang@gmail.com> 于2025年5月25日周日 19:17写道：

>
>
> Álvaro Herrera <alvherre@alvh.no-ip.org> 于2025年5月24日周六 17:11写道：
>
>> On 2025-May-21, Andres Freund wrote:
>>
>> > Hi,
>> >
>> > In [1] I added some verification to projection building, to check if the
>> > tupleslot passed to ExecBuildProjectionInfo() is compatible with the
>> target
>> > list.  One query in merge.sql [2] got flagged.
>> >
>> > Trying to debug that issue, I found another problem. This leaves us
>> with:
>> >
>> > 1) w/ inheritance INSERTed rows are not returned by RETURNING. This
>> seems to
>> >    just generally not work with MERGE
>>
>> Hmm, curious.  One thing to observe is that the original source tuple is
>> in the child table, but the tuple inserted by MERGE ends up in the
>> parent table.  I'm guessing that the code gets confused as to the
>> relation that the tuple in the returned slot comes from, and that
>> somehow makes it somehow not "see" the tuple to process for RETURNING?
>> I dunno.  CC'ing Dean, who is more familiar with this code than I am.
>>
>
>  In ExecMergeNotMatched(), we passed the mtstate->rootResultRelInfo to
> ExecInsert().
> In this case,  the ri_projectReturning of mtstate->rootResultRelInfo  is
> NULL,  in ExecInsert(),
> the "if (resultRelInfo->ri_projectReturning)" branch will not run, so
> inheritance INSERTed rows are not returned by RETURNING.
>
> The mtstate->rootResultRelInfo  assigned in ExecInitModifyTable()  is only
> here:
> if (node->rootRelation > 0)
> {
>      Assert(bms_is_member(node->rootRelation, estate->es_unpruned_relids));
>      mtstate->rootResultRelInfo = makeNode(ResultRelInfo);
>      ExecInitResultRelation(estate, mtstate->rootResultRelInfo,
>              node->rootRelation);
> }
> The ri_projectReturning is not assigned.
>
> I try to pass resultRelInfo to ExecInsert, the inherited INSERTed rows are
> returned by RETURNING.
> But some test cases in regression failed.
>

For a partitioned table, we must pass rootResultRelInfo to ExecInsert(). I
added the check before calling ExecInsert()
If it is a partitioned table, we continue to pass rootResultRelInfo.
Otherwise, we pass resultRelInfo.
Please see the attached diff file. The patch passed all regression test
cases.

-- 
Thanks,
Tender Wang

  5. Re: MERGE issues around inheritance

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-05-25T12:41:40Z

    On Sun, 25 May 2025 at 13:06, Tender Wang <tndrwang@gmail.com> wrote:
>
> For a partitioned table, we must pass rootResultRelInfo to ExecInsert(). I added the check before calling ExecInsert()
> If it is a partitioned table, we continue to pass rootResultRelInfo. Otherwise, we pass resultRelInfo.
> Please see the attached diff file. The patch passed all regression test cases.
>

No, I don't think that's the right fix. I'm looking at it now, and I
think I have a fix, but it's more complicated than that. I'll post an
update later.

Regards,
Dean

  6. Re: MERGE issues around inheritance

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-05-25T20:10:44Z

    On Sun, 25 May 2025 at 13:41, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>
> On Sun, 25 May 2025 at 13:06, Tender Wang <tndrwang@gmail.com> wrote:
> >
> > For a partitioned table, we must pass rootResultRelInfo to ExecInsert(). I added the check before calling ExecInsert()
> > If it is a partitioned table, we continue to pass rootResultRelInfo. Otherwise, we pass resultRelInfo.
> > Please see the attached diff file. The patch passed all regression test cases.
> >
>
> No, I don't think that's the right fix. I'm looking at it now, and I
> think I have a fix, but it's more complicated than that. I'll post an
> update later.
>

The reason that MERGE must pass rootResultRelInfo to ExecInsert() for
a plain-inheritance table dates back to 387f9ed0a08. As that commit
demonstrates, it is possible for the parent to be excluded from the
plan, and so all of the entries in the resultRelInfo array may be for
different relations than rootResultRelInfo.

So there are indeed two related bugs here:

1. The projection built by ExecInitMerge() in the INSERT case may use
a tuple slot that's not compatible with the target table.

2. ExecInitModifyTable() does not initialize the WCO lists or
RETURNING list for rootResultRelInfo, so those never get executed.

As it happens, it is possible to construct cases where (1) causes a
crash, even without WCO's or a RETURNING list (see the first test case
in the attached patch), so this bug goes all the way back to v15,
where MERGE was introduced.

So I think we need to do something like the attached.

There is perhaps scope to reduce the code duplication between this and
ExecInitPartitionInfo(), but that'd likely make the patch bigger, so I
think it's best to leave that for now.

Regards,
Dean

  7. Re: MERGE issues around inheritance

    Tender Wang <tndrwang@gmail.com> — 2025-05-26T02:27:06Z

    Dean Rasheed <dean.a.rasheed@gmail.com> 于2025年5月26日周一 04:10写道：

> On Sun, 25 May 2025 at 13:41, Dean Rasheed <dean.a.rasheed@gmail.com>
> wrote:
> >
> > On Sun, 25 May 2025 at 13:06, Tender Wang <tndrwang@gmail.com> wrote:
> > >
> > > For a partitioned table, we must pass rootResultRelInfo to
> ExecInsert(). I added the check before calling ExecInsert()
> > > If it is a partitioned table, we continue to pass rootResultRelInfo.
> Otherwise, we pass resultRelInfo.
> > > Please see the attached diff file. The patch passed all regression
> test cases.
> > >
> >
> > No, I don't think that's the right fix. I'm looking at it now, and I
> > think I have a fix, but it's more complicated than that. I'll post an
> > update later.
> >
>
> The reason that MERGE must pass rootResultRelInfo to ExecInsert() for
> a plain-inheritance table dates back to 387f9ed0a08. As that commit
> demonstrates, it is possible for the parent to be excluded from the
> plan, and so all of the entries in the resultRelInfo array may be for
> different relations than rootResultRelInfo.
>

Thanks for the information.  Passing resultRelInfo to ExecInsert() is wrong.


> So there are indeed two related bugs here:
>
> 1. The projection built by ExecInitMerge() in the INSERT case may use
> a tuple slot that's not compatible with the target table.
>
> 2. ExecInitModifyTable() does not initialize the WCO lists or
> RETURNING list for rootResultRelInfo, so those never get executed.
>
> As it happens, it is possible to construct cases where (1) causes a
> crash, even without WCO's or a RETURNING list (see the first test case
> in the attached patch), so this bug goes all the way back to v15,
> where MERGE was introduced.
>
> So I think we need to do something like the attached.
>

I tested the attached patch, and there's no problem anymore.  LGTM.


> There is perhaps scope to reduce the code duplication between this and
> ExecInitPartitionInfo(), but that'd likely make the patch bigger, so I
> think it's best to leave that for now.
>

Agreed.

-- 
Thanks,
Tender Wang

  8. Re: MERGE issues around inheritance

    Tender Wang <tndrwang@gmail.com> — 2025-05-26T06:46:05Z

    Dean Rasheed <dean.a.rasheed@gmail.com> 于2025年5月26日周一 04:10写道：

> On Sun, 25 May 2025 at 13:41, Dean Rasheed <dean.a.rasheed@gmail.com>
> wrote:
> >
> > On Sun, 25 May 2025 at 13:06, Tender Wang <tndrwang@gmail.com> wrote:
> > >
> > > For a partitioned table, we must pass rootResultRelInfo to
> ExecInsert(). I added the check before calling ExecInsert()
> > > If it is a partitioned table, we continue to pass rootResultRelInfo.
> Otherwise, we pass resultRelInfo.
> > > Please see the attached diff file. The patch passed all regression
> test cases.
> > >
> >
> > No, I don't think that's the right fix. I'm looking at it now, and I
> > think I have a fix, but it's more complicated than that. I'll post an
> > update later.
> >
>
> The reason that MERGE must pass rootResultRelInfo to ExecInsert() for
> a plain-inheritance table dates back to 387f9ed0a08. As that commit
> demonstrates, it is possible for the parent to be excluded from the
> plan, and so all of the entries in the resultRelInfo array may be for
> different relations than rootResultRelInfo.
>

Hi Dean,

 "it is possible for the parent to be excluded from the
plan and so all of the entries in the resultRelInfo array may be for
different relations than rootResultRelInfo."

I didn't fully understand the above sentence.  Can you give me more
information or an example?
If the parent is excluded from the plan, the first entry in the
resultRelInfo array will not be the parent but some surviving child.


-- 
Thanks,
Tender Wang

  9. Re: MERGE issues around inheritance

    jian he <jian.universality@gmail.com> — 2025-05-26T09:29:50Z

    On Mon, May 26, 2025 at 4:11 AM Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>
> On Sun, 25 May 2025 at 13:41, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> >
>
> 2. ExecInitModifyTable() does not initialize the WCO lists or
> RETURNING list for rootResultRelInfo, so those never get executed.
>
> As it happens, it is possible to construct cases where (1) causes a
> crash, even without WCO's or a RETURNING list (see the first test case
> in the attached patch), so this bug goes all the way back to v15,
> where MERGE was introduced.
>
> So I think we need to do something like the attached.
>
> There is perhaps scope to reduce the code duplication between this and
> ExecInitPartitionInfo(), but that'd likely make the patch bigger, so I
> think it's best to leave that for now.

+ Relation rootRelation = rootRelInfo->ri_RelationDesc;
+ Relation firstResultRel = mtstate->resultRelInfo[0].ri_RelationDesc;
+ int firstVarno = mtstate->resultRelInfo[0].ri_RangeTableIndex;

firstResultRel may equal (==) to rootRelation,
in that case, we don't need to call map_variable_attnos?

we can
        returningList = linitial(node->returningLists);
        if (rel != firstResultRel)
        {
            /* Convert any Vars in it to contain the root's attno's */
            part_attmap =
                build_attrmap_by_name(RelationGetDescr(rel),
                                      RelationGetDescr(firstResultRel),
                                      false);
            returningList = (List *)
                map_variable_attnos((Node *) returningList,
                                    firstVarno, 0,
                                    part_attmap,
                                    RelationGetForm(rel)->reltype,
                                    &found_whole_row);
        }
(i am not sure that will make code less readable).


we can unconditionally call ExecBuildProjectionInfo for rootRelInfo
within ExecInitModifyTable instead of in ExecInitMerge.
right after

        /*
         * Build a projection for each result rel.
         */
        resultRelInfo = mtstate->resultRelInfo;
        foreach(l, returningLists)
        {
            List       *rlist = (List *) lfirst(l);
            resultRelInfo->ri_returningList = rlist;
            resultRelInfo->ri_projectReturning =
                ExecBuildProjectionInfo(rlist, econtext, slot, &mtstate->ps,
                                        resultRelInfo->ri_RelationDesc->rd_att);
            resultRelInfo++;
        }

This would make related initiation logic stay together, also harmless (i think).
what do you think?

  10. Re: MERGE issues around inheritance

    Tender Wang <tndrwang@gmail.com> — 2025-05-26T09:39:08Z

    jian he <jian.universality@gmail.com> 于2025年5月26日周一 17:30写道：

> On Mon, May 26, 2025 at 4:11 AM Dean Rasheed <dean.a.rasheed@gmail.com>
> wrote:
> >
> > On Sun, 25 May 2025 at 13:41, Dean Rasheed <dean.a.rasheed@gmail.com>
> wrote:
> > >
> >
> > 2. ExecInitModifyTable() does not initialize the WCO lists or
> > RETURNING list for rootResultRelInfo, so those never get executed.
> >
> > As it happens, it is possible to construct cases where (1) causes a
> > crash, even without WCO's or a RETURNING list (see the first test case
> > in the attached patch), so this bug goes all the way back to v15,
> > where MERGE was introduced.
> >
> > So I think we need to do something like the attached.
> >
> > There is perhaps scope to reduce the code duplication between this and
> > ExecInitPartitionInfo(), but that'd likely make the patch bigger, so I
> > think it's best to leave that for now.
>
> + Relation rootRelation = rootRelInfo->ri_RelationDesc;
> + Relation firstResultRel = mtstate->resultRelInfo[0].ri_RelationDesc;
> + int firstVarno = mtstate->resultRelInfo[0].ri_RangeTableIndex;
>
> firstResultRel may equal (==) to rootRelation,
> in that case, we don't need to call map_variable_attnos?
>
> +       if (rootRelInfo != mtstate->resultRelInfo &&
+               rootRelInfo->ri_RelationDesc->rd_rel->relkind !=
RELKIND_PARTITIONED_TABLE &&
+               (mtstate->mt_merge_subcommands & MERGE_INSERT) != 0)

Above if already does the check.


-- 
Thanks,
Tender Wang

  11. Re: MERGE issues around inheritance

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-05-26T10:35:11Z

    On Mon, 26 May 2025 at 10:30, jian he <jian.universality@gmail.com> wrote:
>
> + Relation rootRelation = rootRelInfo->ri_RelationDesc;
> + Relation firstResultRel = mtstate->resultRelInfo[0].ri_RelationDesc;
> + int firstVarno = mtstate->resultRelInfo[0].ri_RangeTableIndex;
>
> firstResultRel may equal (==) to rootRelation,
> in that case, we don't need to call map_variable_attnos?

Good point. I think that's by far the most common case, so that seems
like a worthwhile optimisation. v2 attached.


> we can unconditionally call ExecBuildProjectionInfo for rootRelInfo
> within ExecInitModifyTable instead of in ExecInitMerge.
> right after
>
>         /*
>          * Build a projection for each result rel.
>          */
>         resultRelInfo = mtstate->resultRelInfo;
>         foreach(l, returningLists)
>         {
>             List       *rlist = (List *) lfirst(l);
>             resultRelInfo->ri_returningList = rlist;
>             resultRelInfo->ri_projectReturning =
>                 ExecBuildProjectionInfo(rlist, econtext, slot, &mtstate->ps,
>                                         resultRelInfo->ri_RelationDesc->rd_att);
>             resultRelInfo++;
>         }
>
> This would make related initiation logic stay together, also harmless (i think).
> what do you think?

Well it would have to be done after calling ExecInitMerge() to set up
rootRelInfo->ri_returningList, but I don't think it really makes sense
to do it there. The patch intentionally only does it for a MERGE into
an inherited table when there are INSERT actions, and this also allows
the new code to be more consistent with ExecInitPartitionInfo().

Regards,
Dean

  12. Re: MERGE issues around inheritance

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-05-26T10:40:54Z

    On Mon, 26 May 2025 at 07:46, Tender Wang <tndrwang@gmail.com> wrote:
>
> Hi Dean,
>
>  "it is possible for the parent to be excluded from the
> plan and so all of the entries in the resultRelInfo array may be for
> different relations than rootResultRelInfo."
>
> I didn't fully understand the above sentence.  Can you give me more information or an example?
> If the parent is excluded from the plan, the first entry in the resultRelInfo array will not be the parent but some surviving child.

There's an example in the updated regression tests. A non-inherited
CHECK constraint on the parent causes the planner to exclude the
parent from the relations being scanned and from the resultRelInfo
array, so the first resultRelInfo entry is for a child relation.

Regards,
Dean

  13. Re: MERGE issues around inheritance

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-05-26T11:03:46Z

    On Mon, 26 May 2025 at 10:39, Tender Wang <tndrwang@gmail.com> wrote:
>
> jian he <jian.universality@gmail.com> 于2025年5月26日周一 17:30写道：
>>
>> + Relation rootRelation = rootRelInfo->ri_RelationDesc;
>> + Relation firstResultRel = mtstate->resultRelInfo[0].ri_RelationDesc;
>> + int firstVarno = mtstate->resultRelInfo[0].ri_RangeTableIndex;
>>
>> firstResultRel may equal (==) to rootRelation,
>> in that case, we don't need to call map_variable_attnos?
>>
> +       if (rootRelInfo != mtstate->resultRelInfo &&
> +               rootRelInfo->ri_RelationDesc->rd_rel->relkind != RELKIND_PARTITIONED_TABLE &&
> +               (mtstate->mt_merge_subcommands & MERGE_INSERT) != 0)
>
> Above if already does the check.

That's a different check. "rootRelInfo != mtstate->resultRelInfo"
checks that we're dealing with an inheritance/partitioned case (see
the code in ExecInitModifyTable() that sets up rootRelInfo). However,
in the inherited case, when rootRelInfo and mtstate->resultRelInfo
point to different ResultRelInfo structures, it is possible (actually
quite likely) that they will internally point to the same Relation. In
that case, we do still need to set up the WCO list, RETURNING list and
projection for rootRelInfo, but we don't need to map attribute
numbers. Building the attribute map looks like it's O(n^2) in the
number of attributes, so it's worth avoiding if we can.

Regards,
Dean

  14. Re: MERGE issues around inheritance

    Tender Wang <tndrwang@gmail.com> — 2025-05-26T11:50:18Z

    Dean Rasheed <dean.a.rasheed@gmail.com> 于2025年5月26日周一 18:41写道：

> On Mon, 26 May 2025 at 07:46, Tender Wang <tndrwang@gmail.com> wrote:
> >
> > Hi Dean,
> >
> >  "it is possible for the parent to be excluded from the
> > plan and so all of the entries in the resultRelInfo array may be for
> > different relations than rootResultRelInfo."
> >
> > I didn't fully understand the above sentence.  Can you give me more
> information or an example?
> > If the parent is excluded from the plan, the first entry in the
> resultRelInfo array will not be the parent but some surviving child.
>
> There's an example in the updated regression tests. A non-inherited
> CHECK constraint on the parent causes the planner to exclude the
> parent from the relations being scanned and from the resultRelInfo
> array, so the first resultRelInfo entry is for a child relation.
>

Yes, it is.  I debugged the updated regression tests. It would crash if
resultRelInfo were used instead of rootResultInfo.
Is that the reason that we must use rootResultInfo?  Are there other things
that I miss?


>That's a different check. "rootRelInfo != mtstate->resultRelInfo"
>checks that we're dealing with an inheritance/partitioned case (see
>the code in ExecInitModifyTable() that sets up rootRelInfo). However,
>in the inherited case, when rootRelInfo and mtstate->resultRelInfo
>point to different ResultRelInfo structures, it is possible (actually
>quite likely) that they will internally point to the same Relation. In
>that case, we do still need to set up the WCO list, RETURNING list and
>projection for rootRelInfo, but we don't need to map attribute
>numbers. Building the attribute map looks like it's O(n^2) in the
>number of attributes, so it's worth avoiding if we can.
Yeah, Jian's idea is right.

-- 
Thanks,
Tender Wang

  15. Re: MERGE issues around inheritance

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-05-28T10:26:19Z

    On Mon, 26 May 2025 at 12:50, Tender Wang <tndrwang@gmail.com> wrote:
>
>> >  "it is possible for the parent to be excluded from the
>> > plan and so all of the entries in the resultRelInfo array may be for
>> > different relations than rootResultRelInfo."
>> >
>> > I didn't fully understand the above sentence.  Can you give me more information or an example?
>> > If the parent is excluded from the plan, the first entry in the resultRelInfo array will not be the parent but some surviving child.
>>
>> There's an example in the updated regression tests. A non-inherited
>> CHECK constraint on the parent causes the planner to exclude the
>> parent from the relations being scanned and from the resultRelInfo
>> array, so the first resultRelInfo entry is for a child relation.
>
> Yes, it is.  I debugged the updated regression tests. It would crash if resultRelInfo were used instead of rootResultInfo.
> Is that the reason that we must use rootResultInfo?
>

Yes. To work correctly for an inherited table, ExecInsert() must be
passed a pointer to a ResultRelInfo structure that points to the
parent table.

As I mentioned before, this goes back to commit 387f9ed0a08, so it's
worth reading that in more detail.

Prior to commit 387f9ed0a08, rootResultInfo was equal to resultRelInfo
for an inherited table, which was wrong because that could point to a
child table, if the parent table was excluded from the plan.

Commit 387f9ed0a08 fixed that by changing the planner so that it set
ModifyTable.rootRelation to the index of the parent table, if it was
an inherited table. As a result, starting from that commit, for an
inherited table, rootResultInfo is a different ResultRelInfo structure
which always points to the parent table, making it the correct thing
to pass to ExecInsert() under all circumstances.

The thing that was overlooked was that the separate ResultRelInfo
structure in rootResultInfo, and the insert projection, weren't being
correctly initialised for MERGE, which is what this patch aims to fix.
Unless there are any further comments, I plan to commit it in a day or
so.

Regards,
Dean

  16. Re: MERGE issues around inheritance

    Tender Wang <tndrwang@gmail.com> — 2025-05-28T10:37:07Z

    Dean Rasheed <dean.a.rasheed@gmail.com> 于2025年5月28日周三 18:26写道：

> On Mon, 26 May 2025 at 12:50, Tender Wang <tndrwang@gmail.com> wrote:
> >
> >> >  "it is possible for the parent to be excluded from the
> >> > plan and so all of the entries in the resultRelInfo array may be for
> >> > different relations than rootResultRelInfo."
> >> >
> >> > I didn't fully understand the above sentence.  Can you give me more
> information or an example?
> >> > If the parent is excluded from the plan, the first entry in the
> resultRelInfo array will not be the parent but some surviving child.
> >>
> >> There's an example in the updated regression tests. A non-inherited
> >> CHECK constraint on the parent causes the planner to exclude the
> >> parent from the relations being scanned and from the resultRelInfo
> >> array, so the first resultRelInfo entry is for a child relation.
> >
> > Yes, it is.  I debugged the updated regression tests. It would crash if
> resultRelInfo were used instead of rootResultInfo.
> > Is that the reason that we must use rootResultInfo?
> >
>
> Yes. To work correctly for an inherited table, ExecInsert() must be
> passed a pointer to a ResultRelInfo structure that points to the
> parent table.
>
> As I mentioned before, this goes back to commit 387f9ed0a08, so it's
> worth reading that in more detail.
>
> Prior to commit 387f9ed0a08, rootResultInfo was equal to resultRelInfo
> for an inherited table, which was wrong because that could point to a
> child table, if the parent table was excluded from the plan.
>
> Commit 387f9ed0a08 fixed that by changing the planner so that it set
> ModifyTable.rootRelation to the index of the parent table, if it was
> an inherited table. As a result, starting from that commit, for an
> inherited table, rootResultInfo is a different ResultRelInfo structure
> which always points to the parent table, making it the correct thing
> to pass to ExecInsert() under all circumstances.
>

Yeah.  Your explanation above made me understand completely.
Thanks for your explanation.

>
> The thing that was overlooked was that the separate ResultRelInfo
> structure in rootResultInfo, and the insert projection, weren't being
> correctly initialised for MERGE, which is what this patch aims to fix.
>

Yes,  it is.

> Unless there are any further comments, I plan to commit it in a day or
> so.
>
I don't have any other comments. It looks good for me.


-- 
Thanks,
Tender Wang

  17. Re: MERGE issues around inheritance

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-05-31T11:54:43Z

    On Wed, 28 May 2025 at 11:37, Tender Wang <tndrwang@gmail.com> wrote:
>
> Dean Rasheed <dean.a.rasheed@gmail.com> 于2025年5月28日周三 18:26写道：
>>
>> Unless there are any further comments, I plan to commit it in a day or so.
>
> I don't have any other comments. It looks good for me.
>

Thanks for looking. I have committed this now.

Regards,
Dean