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

On Thu, 22 Oct 2026 at 15:31, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> But I think we are putting too much emphasis on this example.
> Reality is that if the same alias is specified in both the rule and
> the outer query, there is *nothing* we can print that won't be
> ambiguous/confusing. That's self-inflicted damage really, and
> I'm not very impressed by the argument that two different people
> might've written the rule and the query. So I'd rather focus on
> what happens when people avoid duplicate aliases, and make sure
> that we print names that are as useful as possible in that case.

OK, in that case, most of the examples on this thread qualify as
self-inflicted damage, since they rely on aliasing a relation as "old"
or "new" to trigger confusing behaviour.

Here's a more realistic example, following documented behaviour,
without any weirdly chosen aliases:

DROP TABLE IF EXISTS foo CASCADE;
CREATE TABLE foo (key int, val1 int, val2 int, val3 int);
CREATE VIEW foo_v AS SELECT * FROM foo;

CREATE RULE foo_r AS ON UPDATE TO foo_v DO INSTEAD
UPDATE foo
SET val1 = new.val1, val2 = new.val2, val3 = new.val3
WHERE key = old.key
RETURNING WITH (OLD AS o, NEW AS n)
key,
o.val1, -- Forces old.val1 to be returned regardless of old/new in
-- triggering query
n.val2, -- Forces new.val2 to be returned regardless of old/new in
-- triggering query
val3; -- Allows triggering query to choose desired old/new

EXPLAIN (VERBOSE, COSTS OFF)
UPDATE foo_v
SET val3 = val3 + 1
RETURNING key, old.val3, new.val3;

Currently, and with my patch, this returns

Update on public.foo
Output: foo.key, old.val3, new.val3
-> Merge Join
Output: foo_1.val1, foo_1.val2, (foo_1.val3 + 1), foo.ctid, foo_1.ctid
Merge Cond: (foo.key = foo_1.key)
-> Sort
Output: foo.ctid, foo.key
Sort Key: foo.key
-> Seq Scan on public.foo
Output: foo.ctid, foo.key
-> Sort
Output: foo_1.val1, foo_1.val2, foo_1.val3, foo_1.ctid, foo_1.key
Sort Key: foo_1.key
-> Seq Scan on public.foo foo_1
Output: foo_1.val1, foo_1.val2, foo_1.val3,
foo_1.ctid, foo_1.key

which I'd say makes perfect sense. With your patch, it returns

Update on public.foo
Output: foo.key, o.val3, n.val3
-> Merge Join
Output: foo_1.val1, foo_1.val2, (foo_1.val3 + 1), foo.ctid, foo_1.ctid
Merge Cond: (foo.key = foo_1.key)
-> Sort
Output: foo.ctid, foo.key
Sort Key: foo.key
-> Seq Scan on public.foo
Output: foo.ctid, foo.key
-> Sort
Output: foo_1.val1, foo_1.val2, foo_1.val3, foo_1.ctid, foo_1.key
Sort Key: foo_1.key
-> Seq Scan on public.foo foo_1
Output: foo_1.val1, foo_1.val2, foo_1.val3, foo_1.ctid, foo

which I'd say is a regression.

The problem is that changing the rewriter in the way you're proposing
causes all downstream code to treat all Vars in the returning list
with non-default varreturningtype as if that varreturningtype came
from the rule action, which isn't necessarily true.

Not changing the rewriter does at least give ruleutils a chance to
output something more useful in the weird edge cases, but as you say,
I don't think it's worth focussing too much on such cases. The
simplest fix is to just remove the Assert()s in get_variable(). The
second simplest fix is to replace them with refname = "old"/"new", if
it's NULL, which I think is slightly better in some of the weird edge
cases, though I'd be happy with either fix.

Regards,
Dean