Re: [DEPARSE] EXPLAIN prints another ModifyTable's RETURNING aliases in a SubPlan

On Wed, 21 Oct 2026 at 19:00, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> I wrote:
> > But wait a minute: there's still something pretty strange
> > going on here. In the first place, Ayush's test case doesn't
> > crash if the outer query doesn't try to use alias "new" for v:
>
> Ah, I see the cause of that part. The parser's
> transformReturningClause does this:
>
> if (qry->returningNewAlias == NULL &&
> refnameNamespaceItem(pstate, NULL, "new", -1, NULL) == NULL)
> {
> qry->returningNewAlias = "new";
> addNSItemForReturning(pstate, "new", VAR_RETURNING_NEW);
> }
>
> (and likewise for returningOldAlias). So using "new" as an
> alias in the outer query allows qry->returningNewAlias to be
> left as NULL. I guess the assumption is that if you do that
> then it's impossible to reference VAR_RETURNING_NEW explicitly?

Yes, that's intentional. If a query has some relation with alias
"new", and you don't specify RETURNING WITH (NEW AS ...), then there
is no way to get a non-default VarReturningType, because "new.colname"
is just a regular reference to that column of the relation with alias
"new".

> But then when rewriteRuleAction unconditionally copies the null
> value into the rule_action --- which does have VAR_RETURNING_NEW
> Vars --- we've got trouble.

Right.

(BTW, that can only happen if the rule action's RETURNING list
specifies an alias for NEW and contains Vars using it, since otherwise
NEW is the special pseudo-relation representing the values from the
triggering query, and "new.colname" has VAR_RETURNING_DEFAULT.)

> Contrarily, it's possible for the rule_action to have null
> returningOldAlias/returningNewAlias through the same mechanism.

Right. In fact, that's likely the most common case, and it's what you
get if you follow the recommendation in the docs of not using OLD/NEW
in the RETURNING list of a rule action, which then avoids all this
confusion.

> One regression test case that fails if I simply dike out
> rewriteRuleAction's copying is
>
> CREATE TEMP TABLE foo (f1 serial, f2 text, f3 int default 42);
> ALTER TABLE foo ADD COLUMN f4 int8 DEFAULT 99;
> CREATE RULE foo_del_rule AS ON DELETE TO foo DO INSTEAD
> UPDATE foo SET f2 = f2||' (deleted)', f3 = -1, f4 = -1 WHERE f1 = OLD.f1
> RETURNING *;
> EXPLAIN (verbose, costs off)
> DELETE FROM foo WHERE f1 = 4 RETURNING old.*,new.*, *;
>
> Here, the rule_action's aliases are both NULL, which I take to be
> because the rule mechanisms themselves set up dummy OLD and NEW RTEs.
> So if we don't supply something during rewriting then those new
> Asserts fire, as a consequence of having pushed VAR_RETURNING_OLD/NEW
> Vars down into the rule action from the outer query.

Yep.

> This makes me think it is probably possible to construct scenarios
> where a plan contains both VAR_RETURNING Vars that originated inside
> a rule and some that originated from the calling query, in which
> case using either level's aliases across-the-board would be wrong.
> But we don't have a lot of choice, because that mixing happens in
> the rewriter and its output format doesn't support recording multiple
> sets of aliases (or does it? maybe the two sets of Vars will have
> different varlevelsup?).

No, they'll be indistinguishable.

> Anyway, for a back-patchable fix I favor the fragment I showed,
> which keeps the lower-level aliases if any and only pushes down
> the upper aliases if there are no aliases in the rule.

That is consistent with the way a Vars' varReturningType is propagated
-- if the rule action's RETURNING list causes a Var to have a
non-default VarRetruningType, that cannot be overridden by the
triggering query. Otherwise, the VarReturningType from the triggering
query is used, as in the example above. This is why the docs recommend
using "RETURNING *" in a rule action, rather than specifying OLD/NEW
-- so the triggering query has control over whether to return old or
new, and all the confusion is avoided. With hindsight, perhaps it
would have been better to simply forbid RETURNING WITH (OLD/NEW AS
...) in a rule action.

Regards,
Dean