Re: Common Table Expressions applied; some issues remain

> I've applied the latest version of the CTE patch.  Congratulations on
> making that happen!

Great! and thanks, Tom. Without your great help, we cannot make it
reality. I also would like to thank to everyone who helped this
project!

> There are still some loose ends that need to be considered, though.

I think in addition to them, we need to update ecpg.

> 1. As committed, the patch takes an extremely hard line about WITH
> queries being evaluated independently of the main query and only once
> per main query execution.  This could be seen as a good thing --- it
> provides much more determinism for execution of volatile functions
> within complex queries than was really available in the past.  It could
> also be seen as a bad thing --- in particular, we won't push any
> limiting qualifications from the main query into the WITH queries.
> So for instance
> 
>     WITH q AS ( SELECT * FROM foo )
>     SELECT * FROM q WHERE key = 42;
> 
> is going to be executed quite inefficiently; it won't use an index on
> foo.key.  I think we don't have much choice about this in the case of
> recursive WITH queries: it would be pretty difficult to determine
> whether pushing a restriction into a recursive WITH would change the
> results incorrectly.  However, for plain non-recursive WITHs it's all
> a matter of definition.  I gather from
> http://www.oracle-developer.net/display.php?id=212
> that Oracle chooses to treat WITH-queries as if they were plain
> sub-selects if they're non-recursive and only referenced once.
> That is, Oracle would rewrite the above into
> 
>     SELECT * FROM ( SELECT * FROM foo ) AS q WHERE key = 42;
> 
> and then flatten the sub-select and optimize normally.  It would
> not be hard to make Postgres do the same, but then we would lose
> some guarantees about predictable execution of volatile functions.
> 
> I'm inclined to think that there is no reason to provide two
> different syntaxes to do the same thing, and so having the WITH
> syntax behave like this is okay.  But it could well result in
> performance surprises for people who are used to Oracle.
> 
> Any thoughts on what to do?  One possibility is to flatten only
> if the subquery doesn't contain any volatile functions.
> 
> 
> 2. The patch didn't touch the implicit-RTE code, which means that
> 
>     WITH q AS ( SELECT ... )
>     SELECT q.*
> 
> will fail even if you've got add_missing_from enabled.  I'm inclined
> to think that this violates the principle of least surprise.  On
> the other hand, add_missing_from is certainly a legacy thing and maybe
> we shouldn't bother expending any extra code to make it work with
> new features.  Thoughts?
> 
> 
> 3. ruleutils.c's get_name_for_var_field() hasn't implemented the
> RTE_CTE case, which means that it doesn't work to reverse-list
> examples like this:
> 
> explain verbose with qq as (select x from (values(1,2),(3,4)) as x(c1,c2))
> select * from (select (x).c2 from qq offset 0) ss;
> 
> The reason I let this go is that while poking into it I found out that 
> get_name_for_var_field is pretty broken already; this fails in HEAD:
> 
> explain verbose select (x).c2 from
>  (select x from (values(1,2),(3,4)) as x(c1,c2) offset 0) ss ;
> 
> and this fails even in the back branches:
> 
> explain select * from 
>  (select x from (values(1,2),(3,4)) as x(c1,c2) offset 0) ss
>  where (x).c2 > 0;
> 
> It seems we need some redesign in and around EXPLAIN to make that work
> nicely, so I figured it would be reasonable to tackle that stuff as a
> separate patch.
--
Tatsuo Ishii
SRA OSS, Inc. Japan