Re: CTE inlining

On 30 Apr. 2017 13:28, "Andres Freund" <andres(at)anarazel(dot)de> wrote:

On 2017-04-30 00:28:46 -0400, Tom Lane wrote:
> There's already a pretty large hill to climb here in the way of
> breaking peoples' expectations about CTEs being optimization
> fences. Breaking the documented semantics about CTEs being
> single-evaluation seems to me to be an absolute non-starter.

If all referenced functions are non-volatile, I don't quite see the
problem? Personally I believe we'll have to offer a proper
anti-inlining workaround anyway, and in that case there's really nothing
that should stop us from inlining CTE without volatile functions twice?

Exactly.

The initial implementation had limitations. So they got documented as
features, not bugs or possible future enhancements. Yay? So we're stuck
with it forever?

I agree we shouldn't break working, correct queries such that they return
different results. But if someone is lying about volatility they don't get
the expectation of correctness. And we have a policy against hints, so
surely we should be keen to remove this hack that serves as a hint - right?

We have OFFSET 0 for anyone really depending on it, and at least when you
read that you know to go "wtf" and look at the manual, wheras the CTE fence
behaviour is invisible and silent.

Yes, experienced and established postgres users expect the optimisation
fence behaviour. They abuse it as a query hint or work around it with
subqueries in FROM. They also know OFFSET 0 ... and ideally should even
read the relnotes. Users from other DMBSes looking to migrate, and new
users, are regularly surprised by our CTEs. I see it a lot on Stack
Overflow and other places outside our comfortable walls.

Personally I find it very annoying when I'd like to use CTEs to structure
queries more readably, but land up having to use subqueries in FROM instead.

Like the work Andes has been doing on our bizarre handing of SRFs in the
SELECT target list I really think it's just something that needs to be done.