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

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.

Thoughts, better ideas?

                        regards, tom lane


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers