Обсуждение: 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=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

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> writes:
> On Sun, Mar 22, 2015 at 6:32 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> What I'm imagining instead is that when there's more than one
>> target relation, we produce output like ...

> 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.

Hm?  We don't have scan nodes that read more than one table, so I'm
not following your point.
        regards, tom lane

<div dir="ltr"><br /><div class="gmail_extra"><br /><div class="gmail_quote">On Mon, Mar 23, 2015 at 10:51 AM, Tom Lane
<spandir="ltr"><<a href="mailto:tgl@sss.pgh.pa.us" target="_blank">tgl@sss.pgh.pa.us</a>></span> wrote:<br
/><blockquoteclass="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid
rgb(204,204,204);padding-left:1ex"><spanclass="">Ashutosh Bapat <<a
href="mailto:ashutosh.bapat@enterprisedb.com">ashutosh.bapat@enterprisedb.com</a>>writes:<br /> > On Sun, Mar 22,
2015at 6:32 AM, Tom Lane <<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>> wrote:<br /></span><span
class="">>>What I'm imagining instead is that when there's more than one<br /></span>>> target relation, we
produceoutput like ...<br /><span class=""><br /> > This looks better.<br /> > In the format above, you have
specifiedboth the Remote SQL for scan as<br /> > well as update but in the example you have only mentioned only
RemoteSQL<br /> > for update; it may be part of "... etc ...". It's better to provide both.<br /><br /></span>Hm? 
Wedon't have scan nodes that read more than one table, so I'm<br /> not following your point.<br /><br />              
         regards, tom lane<br /></blockquote></div><br /></div><div class="gmail_extra">In the format you specified<br
/>     Multi-Table Update<br />                 Relation Name: pt1  -- this is the *nominal* target<br />              
 Target Relations:<br />                   [<br />                     Relation Name: pt1  -- first actual target<br />
                   Schema: public<br />                     Alias: pt1<br />                   ]<br />                
 [<br />                     Relation Name: ft1<br />                     Schema: public<br />                    
Alias:ft1<br />                     Remote SQL: UPDATE ref1 ...<br />                   ]<br /><br />                
Plans:<br/>                   Plan: (seq scan on pt1 here)<br />                   Plan: (foreign scan on ft1 here)<br
/></div><divclass="gmail_extra">For relation ft1, there is an Update node as well as a scan node. Update node has
UpdateRemote SQL and Scan will have corresponding SELECT Remote SQL.<br /><br /></div><div class="gmail_extra">But in
thetext output you gave<br />Update on public.pt1  (cost=0.00..321.05 rows=3541 width=46)<br />    Update on
public.pt1<br/>    Foreign Update on public.ft1<br />      Remote SQL: UPDATE public.ref1 SET c1 = $2 WHERE ctid =
$1<br/>    Foreign Update on public.ft2<br />      Remote SQL: UPDATE public.ref2 SET c1 = $2 WHERE ctid = $1<br />  
 Updateon public.child3<br />    ->  Seq Scan on public.pt1  (cost=0.00..0.00 rows=1 width=46)<br />        
 Output:(pt1.c1 + 1), pt1.c2, pt1.c3, pt1.ctid<br />    ... etc ...<br /><br /></div><div class="gmail_extra">For ft1
thereis only Update Remote SQL. whereas for child3 you have specified the Seq Scan as well. I was wondering if the
foreignscan on ft1 is part of "...etc..."?. Further we have to make it clear which scan goes with which Update, either
bylisting the scans in the same order as updates or by associating each scan with corresponding update.<br clear="all"
/></div><divclass="gmail_extra"><br />-- <br /><div class="gmail_signature"><div dir="ltr">Best Wishes,<br />Ashutosh
Bapat<br/>EnterpriseDB Corporation<br />The Postgres Database Company<br /></div></div></div></div> 

On 03/22/2015 03:02 AM, Tom Lane wrote:
> 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)
>         ]

The "Remote SQL" nodes should go under the Foreign Scan nodes.

> 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.

If we have promised that, I think we should break the promise. No 
application should depend on the details of EXPLAIN output, even if it's 
in JSON/YAML/XML format. EXPLAIN is used by humans, and by tools like 
pgAdmin that display the output for humans, so let's do what makes most 
sense for humans. Admin tools will have to deal with new node types, and 
also new plan structures in every new release anyway. And if an admin 
tool doesn't recognize the new format, it surely falls back to 
displaying them in some a reasonable generic form.

- Heikki

On Sun, Mar 22, 2015 at 11:38 PM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
> If we have promised that, I think we should break the promise. No
> application should depend on the details of EXPLAIN output, even if it's in
> JSON/YAML/XML format. EXPLAIN is used by humans, and by tools like pgAdmin
> that display the output for humans, so let's do what makes most sense for
> humans. Admin tools will have to deal with new node types, and also new plan
> structures in every new release anyway. And if an admin tool doesn't
> recognize the new format, it surely falls back to displaying them in some a
> reasonable generic form.


+1

-- 
Peter Geoghegan

Heikki Linnakangas <hlinnaka@iki.fi> writes:
> On 03/22/2015 03:02 AM, Tom Lane wrote:
>> In a green field we might choose to solve this by refactoring the output
>> so that it's logically ...
>> 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.

> If we have promised that, I think we should break the promise. No 
> application should depend on the details of EXPLAIN output, even if it's 
> in JSON/YAML/XML format.

I think this is entirely wrong.  The entire point of having those
machine-readable output formats was to let people write tools that would
process plans in some intelligent manner.  Relocating where child plans of
a Modify appear in the data structure would certainly break any tool that
had any understanding of plan trees.  Now, maybe there are no such tools,
but in that case the whole exercise in adding those formats was a waste of
time and we should rip them out.

In any case, what I was suggesting here is only very marginally cleaner
than what got implemented, so it really doesn't seem to me to be worth
breaking backwards compatibility here, even if I bought the premise that
backwards compatibility of this output is of low priority.
        regards, tom lane

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> writes:
> On Mon, Mar 23, 2015 at 10:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Hm?  We don't have scan nodes that read more than one table, so I'm
>> not following your point.

> But in the text output you gave
> 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 ...

> For ft1 there is only Update Remote SQL. whereas for child3 you have
> specified the Seq Scan as well.

I think you're confused by my perhaps-overly-abbreviated example.
Here's the whole output for the example in HEAD:

# explain update pt1 set c1=c1+1;                            QUERY PLAN                              
---------------------------------------------------------------------Update on pt1  (cost=0.00..321.05 rows=3541
width=46) Update on pt1  Foreign Update on ft1  Foreign Update on ft2  Update on child3  ->  Seq Scan on pt1
(cost=0.00..0.00rows=1 width=46)  ->  Foreign Scan on ft1  (cost=100.00..148.03 rows=1170 width=46)  ->  Foreign Scan
onft2  (cost=100.00..148.03 rows=1170 width=46)  ->  Seq Scan on child3  (cost=0.00..25.00 rows=1200 width=46)
 
(9 rows)

# explain verbose update pt1 set c1=c1+1;                                QUERY PLAN                                 
----------------------------------------------------------------------------Update on public.pt1  (cost=0.00..321.05
rows=3541width=46)  Update on public.pt1  Foreign Update on public.ft1    Remote SQL: UPDATE public.ref1 SET c1 = $2
WHEREctid = $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  ->  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
 
(17 rows)

        regards, tom lane

On Mon, Mar 23, 2015 at 10:26 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Heikki Linnakangas <hlinnaka@iki.fi> writes:
>> On 03/22/2015 03:02 AM, Tom Lane wrote:
>>> In a green field we might choose to solve this by refactoring the output
>>> so that it's logically ...
>>> 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.
>
>> If we have promised that, I think we should break the promise. No
>> application should depend on the details of EXPLAIN output, even if it's
>> in JSON/YAML/XML format.
>
> I think this is entirely wrong.  The entire point of having those
> machine-readable output formats was to let people write tools that would
> process plans in some intelligent manner.  Relocating where child plans of
> a Modify appear in the data structure would certainly break any tool that
> had any understanding of plan trees.  Now, maybe there are no such tools,
> but in that case the whole exercise in adding those formats was a waste of
> time and we should rip them out.
>
> In any case, what I was suggesting here is only very marginally cleaner
> than what got implemented, so it really doesn't seem to me to be worth
> breaking backwards compatibility here, even if I bought the premise that
> backwards compatibility of this output is of low priority.

I agree that we shouldn't break backward compatibility here for no
particularly good reason, but I also think it would be fine to break
it if the new output were a significant improvement.  People who write
tools that parse this output should, and I think do, understand that
sometimes we'll make changes upstream and they'll need to adjust for
it.  We shouldn't do that on a whim, but we shouldn't let it stand in
the way of progress, either.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company