Display of multi-target-table Modify plan nodes in EXPLAIN - Mailing list pgsql-hackers

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=3541width=46)  Foreign Update on public.ft1    Remote SQL: UPDATE public.ref1 SET c1 = $2 WHERE ctid = $1  Foreign
Updateon public.ft2    Remote SQL: UPDATE public.ref2 SET c1 = $2 WHERE ctid = $1  ->  Seq Scan on public.pt1
(cost=0.00..0.00rows=1 width=46)        Output: (pt1.c1 + 1), pt1.c2, pt1.c3, pt1.ctid  ->  Foreign Scan on public.ft1
(cost=100.00..148.03rows=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:
UPDATEref2 ...      Plan: (foreign scan on ft2 here)    ]    [      Update Target: child3      Plan: (seq scan on
child3here)    ]
 

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
RemoteSQL: UPDATE public.ref1 SET c1 = $2 WHERE ctid = $1  Foreign Update on public.ft2    Remote SQL: UPDATE
public.ref2SET 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.

Thoughts, better ideas?
        regards, tom lane