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

From: Henson Choi <assam258(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: [DEPARSE] EXPLAIN prints another ModifyTable's RETURNING aliases in a SubPlan
Date: 2026-10-20 07:49:15
Message-ID: CAAAe_zAUb_XydapJqUk7O8o2crJa7xbxX=KvSi3nY9h98OcN7g@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

EXPLAIN VERBOSE can print the OLD/NEW aliases of another
ModifyTable in the RETURNING list of a data-modifying statement.
When a SubPlan in the outer statement's RETURNING list refers to
its OLD or NEW, and a data-modifying CTE with aliases of its own is
printed before it, the reference is printed with the CTE's alias.
Only the EXPLAIN text is wrong: the query computes the right values,
and views, rules and pg_dump are not affected. The two attached
patches add tests and fix it.

The problem
-----------

create table e1 (k int, c int);
create table e2 (k int, c int);
explain (verbose, costs off)
with x as (update e2 set c = c + 1
returning with (old as o, new as n) n.k, o.c as oc)
update e1 set c = c + 1
returning with (old as p) p.c,
(select count(*) from x where x.oc = p.c) as m;

before: Filter: (x.oc = o.c)
after: Filter: (x.oc = p.c)

The SubPlan compares the OLD value the CTE returned with the OLD
value of the outer UPDATE, but the output reads as if it compared
the CTE's OLD value with itself. Running the statement gives the
right result with or without the patch. The default names are
affected too: with returning old.k in the CTE and with (old as p)
outside, p.c is printed as old.c.

The cause
---------

The aliases are not stored in the Vars, which only say whether they
are OLD or NEW (varreturningtype). ruleutils.c keeps the names of
the ModifyTable it is looking at in the deparse namespace, and
get_variable() prints them. set_deparse_context_plan(), which
EXPLAIN calls for each node it prints, set them when that node was a
ModifyTable. But to print a Param of a SubPlan, get_parameter()
moves to the ancestor ModifyTable with push_ancestor_plan(), and the
set_deparse_plan() that it calls left the aliases alone. They were
still those of the last ModifyTable EXPLAIN had printed, here the
CTE's.

The fix
-------

0002 sets the aliases in set_deparse_plan(). Every move to another
plan node goes through it, so the aliases are always those of the
ModifyTable in focus. pop_ancestor_plan() and pop_child_plan()
restore the whole namespace, aliases included. Other plan nodes
keep the aliases they inherit, as before.

A RETURNING list can refer only to the OLD and NEW of its own
statement, and a data-modifying WITH must be at the top level, so a
Var that refers to OLD or NEW is always printed in the context of
the ModifyTable that owns it.

Testing
-------

0001 adds two EXPLAIN tests with the output before the fix, and 0002
updates it; the diff is three lines. One has SubPlans that refer to
the OLD and NEW of the outer statement, the other is the example
above.

Back branches
-------------

RETURNING OLD/NEW was added in 18, so I think this should be
back-patched to 18. I applied both patches to REL_18_STABLE and
REL_19_STABLE. They apply cleanly to REL_19 and pass the regression
tests. REL_18 only needs the test conflict and the expected output
resolved (SubPlans are named "SubPlan 2" there). The code change
applies as is, and the regression tests pass.

This came out of an AI-assisted audit of ruleutils.c. I will post
the other problems it found as separate, independent reports as I
work through them.

Best regards,
Henson

Attachment Content-Type Size
v1-0001-Add-tests-for-OLD-NEW-aliases-EXPLAIN-prints-in-R.patch application/octet-stream 5.0 KB
v1-0002-Fix-OLD-NEW-aliases-EXPLAIN-prints-in-RETURNING-S.patch application/octet-stream 4.2 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2026-10-20 08:16:39 Re: [PATCH] Report no unpinned buffers as insufficient resources
Previous Message Chao Li 2026-10-20 07:40:27 Fix integer truncation in frontend pnstrdup()