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