Re: Killing off removed rels properly

I wrote:
>> It looks like we're somehow triggering the elide-a-left-join code
>> when we shouldn't?

So the reason why we see this with a partitioned target table and not
with a regular target table reduces to this bit in preprocess_targetlist:

    /*
     * For non-inherited UPDATE/DELETE/MERGE, register any junk column(s)
     * needed to allow the executor to identify the rows to be updated or
     * deleted.  In the inheritance case, we do nothing now, leaving this to
     * be dealt with when expand_inherited_rtentry() makes the leaf target
     * relations.  (But there might not be any leaf target relations, in which
     * case we must do this in distribute_row_identity_vars().)
     */
    if ((command_type == CMD_UPDATE || command_type == CMD_DELETE ||
         command_type == CMD_MERGE) &&
        !target_rte->inh)
    {
        /* row-identity logic expects to add stuff to processed_tlist */
        root->processed_tlist = tlist;
        add_row_identity_columns(root, result_relation,
                                 target_rte, target_relation);
        tlist = root->processed_tlist;
    }

This happens before join removal, so that we see use of the row identity
columns of a regular table as a reason not to do join removal.  But
expand_inherited_rtentry will happen after that, too late to stop join
removal.

I think the best fix is just to teach join removal that it mustn't
remove the result relation, as attached (needs a regression test).

I'm a little inclined to back-patch this, even though I think it's
probably unreachable in v15.  It's a cheap enough safety measure,
and at the very least it will save a few cycles deciding that we
can't remove the target table of a MERGE.

            regards, tom lane

diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 0dfefd71f2..f79bc4430c 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -183,6 +183,14 @@ join_is_removable(PlannerInfo *root, SpecialJoinInfo *sjinfo)
     if (!bms_get_singleton_member(sjinfo->min_righthand, &innerrelid))
         return false;

+    /*
+     * Never try to eliminate a left join to the query result rel.  Although
+     * the case is syntactically impossible in standard SQL, MERGE will build
+     * a join tree that looks exactly like that.
+     */
+    if (innerrelid == root->parse->resultRelation)
+        return false;
+
     innerrel = find_base_rel(root, innerrelid);

     /*