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

On Thu, 22 Oct 2026 at 12:57, Henson Choi <assam258(at)gmail(dot)com> wrote:
>
> CREATE TABLE t (a int, b int);
> CREATE VIEW v AS SELECT * FROM t;
> CREATE RULE r AS ON UPDATE TO v DO INSTEAD
> UPDATE t SET a = new.a, b = new.b WHERE a = old.a
> RETURNING WITH (OLD AS y) y.a, t.b;
> EXPLAIN (VERBOSE, COSTS OFF)
> UPDATE v AS old SET a = old.a + 100
> RETURNING WITH (NEW AS y) y.a AS new_a, a, b;
>
> without the Assert()s: y.a, a, t.b
> Tom's fragment: y.a, y.a, t.b
> your change: y.a, old.a, t.b

I really don't like "y.a, y.a, t.b" here because it looks like it's
outputting the same column twice, and from the point of view of the
person running the query, "y" is NEW, so it appears to output NEW.a
twice.

Additionally, the person running the query may well not be the same
person who wrote the rule, and so they may well not realise that they
inadvertently chose the same alias for NEW as the rule author chose
for OLD.

Printing "y.a, old.a, t.b" isn't perfect either, because "old" is the
alias the query author gave to the view, but the rest of the EXPLAIN
output, makes it clear that the view got replaced with the table "t",
and the alias "old" doesn't appear anywhere else in the EXPLAIN
output, so it's not such a leap to recognise that "old" means OLD, not
the view in the EXPLAIN output.

Regards,
Dean