Re: Confusing EXPLAIN output in case of inherited tables - Mailing list pgsql-hackers

Thanks Tom for giving a stronger case. I found the problem whille looking at inherited tables, and didn't think beyond
inheritedtables. Since inherited tables are expanded when subquery planner is invoked, I thought the problem will occur
onlyin Explain output as we won't generate queries, that can be used elsewhere after/during planning.<br /><br />So, as
Iunderstand we have two problems here<br />1. Prefixing schemaname to the fake alises if there is another RTE with same
name.There may not be a relation with that name (fake alias name given) in the schema chosen as prefix.<br /> 2. Fake
aliasesthemselves can be conflicting.<br /><br />If I understand correctly, if we solve the second problem, first
problemwill not occur. Is that correct?<br /><br /><div class="gmail_quote">On Sat, Jan 28, 2012 at 8:08 AM, Tom Lane
<spandir="ltr"><<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="margin:0pt 0pt 0pt 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div
class="im">RobertHaas <<a href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>> writes:<br /> > It's
afeature, not a bug, that we schema-qualify names when VERBOSE<br /> > is specified.  That was done on purpose for
thebenefit of external<br /> > tools that might need this information to disambiguate which object is<br /> >
beingreferenced.<br /><br /> > Table *aliases*, of course, should not be schema-qualified, but I<br /> > don't
thinkthat's what we're doing.  You could make it more clear by<br /> > including an alias in the query, like
this:<br/><br /> > explain verbose select * into table ramp from road hwy where name ~ '.*Ramp';<br /><br /></div>I
thinkyou are both focusing on the wrong thing.  There is a lot of<br /> squishiness in what EXPLAIN prints out, since
SQLnotation is not always<br /> well suited to what an execution plan actually does.  But this code has<br /> a hard
andfast requirement that it dump view definitions correctly,<br /> else pg_dump doesn't work.  And after looking at
thisI think Ashutosh<br /> has in fact found a bug.  Consider this example:<br /><br /> regression=# create schema
s1;<br/> CREATE SCHEMA<br /> regression=# create schema s2;<br /> CREATE SCHEMA<br /> regression=# create table s1.t1
(f1int);<br /> CREATE TABLE<br /> regression=# create table s2.t1 (f1 int);<br /> CREATE TABLE<br /> regression=#
createview v1 as<br /> regression-#   select * from s1.t1 where exists (<br /> regression(#     select 1 from s2.t1
wheres2.t1.f1 = s1.t1.f1<br /> regression(#   );<br /> CREATE VIEW<br /> regression=# \d+ v1<br />                  
View"public.v1"<br />  Column |  Type   | Modifiers | Storage | Description<br />
--------+---------+-----------+---------+-------------<br/>  f1     | integer |           | plain   |<br /> View
definition:<br/>  SELECT t1.f1<br />   FROM s1.t1<br />  WHERE (EXISTS ( SELECT 1<br />           FROM s2.t1<br />    
    WHERE t1.f1 = s1.t1.f1));<br /><br /> regression=# alter table s2.t1 rename to tx;<br /> ALTER TABLE<br />
regression=#\d+ v1<br />                   View "public.v1"<br />  Column |  Type   | Modifiers | Storage |
Description<br/> --------+---------+-----------+---------+-------------<br />  f1     | integer |           | plain  
|<br/> View definition:<br />  SELECT t1.f1<br />   FROM s1.t1<br />  WHERE (EXISTS ( SELECT 1<br />           FROM
s2.txt1<br />          WHERE t1.f1 = s1.t1.f1));<br /><br /> Both of the above displays of the view are formally
correct,in that the<br /> variables will be taken to refer to the correct upper or lower RTE.<br /> But let's change
thatback and rename the other table:<br /><br /> regression=# alter table s2.tx rename to t1;<br /> ALTER TABLE<br />
regression=#alter table s1.t1 rename to tx;<br /> ALTER TABLE<br /> regression=# \d+ v1<br />                   View
"public.v1"<br/>  Column |  Type   | Modifiers | Storage | Description<br />
--------+---------+-----------+---------+-------------<br/>  f1     | integer |           | plain   |<br /> View
definition:<br/>  SELECT t1.f1<br />   FROM s1.tx t1<br />  WHERE (EXISTS ( SELECT 1<br />           FROM s2.t1<br />  
      WHERE t1.f1 = s1.t1.f1));<br /><br /> This is just plain wrong, as you'll see if you try to execute that<br />
query:<br/><br /> regression=# SELECT t1.f1<br /> regression-#    FROM s1.tx t1<br /> regression-#   WHERE (EXISTS (
SELECT1<br /> regression(#            FROM s2.t1<br /> regression(#           WHERE t1.f1 = s1.t1.f1));<br /> ERROR:
 invalidreference to FROM-clause entry for table "t1"<br /> LINE 5:           WHERE t1.f1 = s1.t1.f1));<br />          
                    ^<br /> HINT:  There is an entry for table "t1", but it cannot be referenced<br /> from this part
ofthe query.<br /><br /> (The HINT is a bit confused here, but the query is certainly invalid.)<br /><br /> So what we
havehere is a potential failure to dump and reload view<br /> definitions, which is a lot more critical in my book than
whether<br/> EXPLAIN's output is confusing.<br /><br /> If we stick with the existing rule for attaching a fake alias
torenamed<br /> RTEs, I think that Ashutosh's patch or something like it is probably<br /> appropriate, because the
variable-printingcode ought to be in step with<br /> the RTE-printing code.  Unfortunately, I think the hack to attach
afake<br /> alias to renamed RTEs creates some issues of its own.  Consider<br /><br />        select * from s1.t1<br
/>         where exists (select 1 from s2.t2 t1 where t1.f1 = s1.t1.f1);<br /><br /> If s1.t1 is now renamed to s1.tx,
itis still possible to express<br /> the same semantics:<br /><br />        select * from s1.tx<br />          where
exists(select 1 from s2.t2 t1 where t1.f1 = s1.tx.f1);<br /><br /> But when we attach a fake alias, it's broken:<br
/><br/>        select * from s1.tx t1<br />          where exists (select 1 from s2.t2 t1 where t1.f1 = ?.f1);<br /><br
/>There is no way to reference the outer RTE anymore from the subquery,<br /> because the conflicting lower alias masks
it.<br/><br /> We may be between a rock and a hard place though, because it's not that<br /> hard to demonstrate cases
wherenot adding a fake alias breaks it too:<br /><br />        select * from s1.t1 tx<br />          where exists
(select1 from s2.t1 where s2.t1.f1 = tx.f1);<br /><br /> If s2.t1 is renamed to s2.tx, there's no longer any way to
referencethe<br /> upper alias tx, unless you alias the lower RTE to some different name.<br /> I think that when we
putin the fake-alias behavior, we made a value<br /> judgment that this type of situation was more common than the
other,<br/> but I'm not really sure why.<br /><br /> Maybe what we need to do instead is create totally-made-up,
unique<br/> aliases when something like this happens.<br /><br />                        regards, tom lane<br
/></blockquote></div><br/><br clear="all" /><br />-- <br />Best Wishes,<br />Ashutosh Bapat<br />EntepriseDB
Corporation<br/>The Enterprise Postgres Company<br /><br />