Re: Common Table Expressions applied; some issues remain

Tom Lane wrote:
> 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.

I don't think we should overload syntax choices with optimization hints.
We don't really know why or how people will be using this syntax, and
labeling it from the start as "will have unusual performance behavior"
isn't a good sell.

As a precedent, consider the JOIN syntax, which is obviously redundant
and in its first implementation contained an implicit optimization hint
with regard to join order that later had to be done away with because it
confused users (I think). The CTE case is quite similar, and maybe the
GUC answer of old could apply here as well. But I think by default we
should abide by SQL's declarative approach of "Tell me what you want and
I'll execute it any way I like."

Also, why is predictability about volatile function executation a
requirement? Is there some typical use case that involves sequences
functions here or something?

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

Yes, it's legacy. I wouldn't bother.