Re: Display of multi-target-table Modify plan nodes in EXPLAIN

On Sun, Mar 22, 2015 at 6:32 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I've gotten the foreign table inheritance patch to a state where I'm
> almost ready to commit it, but there's one thing that's bothering me,
> which is what it does for EXPLAIN. As it stands you might get something
> like
>
> regression=# explain (verbose) update pt1 set c1=c1+1;
> QUERY PLAN
>
> ----------------------------------------------------------------------------
> Update on public.pt1 (cost=0.00..321.05 rows=3541 width=46)
> Foreign Update on public.ft1
> Remote SQL: UPDATE public.ref1 SET c1 = $2 WHERE ctid = $1
> Foreign Update on public.ft2
> Remote SQL: UPDATE public.ref2 SET c1 = $2 WHERE ctid = $1
> -> Seq Scan on public.pt1 (cost=0.00..0.00 rows=1 width=46)
> Output: (pt1.c1 + 1), pt1.c2, pt1.c3, pt1.ctid
> -> Foreign Scan on public.ft1 (cost=100.00..148.03 rows=1170 width=46)
> Output: (ft1.c1 + 1), ft1.c2, ft1.c3, ft1.ctid
> Remote SQL: SELECT c1, c2, c3, ctid FROM public.ref1 FOR UPDATE
> -> Foreign Scan on public.ft2 (cost=100.00..148.03 rows=1170 width=46)
> Output: (ft2.c1 + 1), ft2.c2, ft2.c3, ft2.ctid
> Remote SQL: SELECT c1, c2, c3, ctid FROM public.ref2 FOR UPDATE
> -> Seq Scan on public.child3 (cost=0.00..25.00 rows=1200 width=46)
> Output: (child3.c1 + 1), child3.c2, child3.c3, child3.ctid
> (15 rows)
>
> which seems fairly messy to me because you have to guess at which of
> the child plan subtrees goes with which "Remote SQL" item.
>
> In a green field we might choose to solve this by refactoring the output
> so that it's logically
>
> Multi-Table Update
> [
> Update Target: pt1
> Plan: (seq scan on pt1 here)
> ]
> [
> Update Target: ft1
> Remote SQL: UPDATE ref1 ...
> Plan: (foreign scan on ft1 here)
> ]
> [
> Update Target: ft2
> Remote SQL: UPDATE ref2 ...
> Plan: (foreign scan on ft2 here)
> ]
> [
> Update Target: child3
> Plan: (seq scan on child3 here)
> ]
>
> but I think that ship has sailed. Changing the logical structure of
> EXPLAIN output like this would break clients that know what's where in
> JSON/YAML/XML formats, which is exactly what we said we wouldn't do with
> those output formats.
>
> What I'm imagining instead is that when there's more than one
> target relation, we produce output like
>
> Multi-Table Update
> Relation Name: pt1 -- this is the *nominal* target
> Target Relations:
> [
> Relation Name: pt1 -- first actual target
> Schema: public
> Alias: pt1
> ]
> [
> Relation Name: ft1
> Schema: public
> Alias: ft1
> Remote SQL: UPDATE ref1 ...
> ]
> [
> Relation Name: ft2
> Schema: public
> Alias: ft2
> Remote SQL: UPDATE ref2 ...
> ]
> [
> Relation Name: child3
> Schema: public
> Alias: child3
> ]
> Plans:
> Plan: (seq scan on pt1 here)
> Plan: (foreign scan on ft1 here)
> Plan: (foreign scan on ft2 here)
> Plan: (seq scan on child3 here)
>
> That is, there'd be a new subnode of ModifyTable (which existing clients
> would ignore), and that would fully identify *each* target table not only
> foreign ones. The text-mode output might look like
>
> Update on public.pt1 (cost=0.00..321.05 rows=3541 width=46)
> Update on public.pt1
> Foreign Update on public.ft1
> Remote SQL: UPDATE public.ref1 SET c1 = $2 WHERE ctid = $1
> Foreign Update on public.ft2
> Remote SQL: UPDATE public.ref2 SET c1 = $2 WHERE ctid = $1
> Update on public.child3
> -> Seq Scan on public.pt1 (cost=0.00..0.00 rows=1 width=46)
> Output: (pt1.c1 + 1), pt1.c2, pt1.c3, pt1.ctid
> ... etc ...
>
> where there would always now be as many target tables listed as
> there are child plan trees.
>
>
This looks better.
In the format above, you have specified both the Remote SQL for scan as
well as update but in the example you have only mentioned only Remote SQL
for update; it may be part of "... etc ...". It's better to provide both.

> Thoughts, better ideas?
>
> regards, tom lane
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company