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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
