<div> </div><div> </div><div>30.04.2017, 08:58, "Craig Ringer" <craig(dot)ringer(at)2ndquadrant(dot)com>:</div><blockquote type="cite"><div><div> <div> <div>On 30 Apr. 2017 13:28, "Andres Freund" <<a href="mailto:andres(at)anarazel(dot)de">andres(at)anarazel(dot)de</a>> wrote:<blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex;"><div>On <span>2017-04-30 00</span>:28:46 -0400, Tom Lane wrote:<br />> There's already a pretty large hill to climb here in the way of<br />> breaking peoples' expectations about CTEs being optimization<br />> fences. Breaking the documented semantics about CTEs being<br />> single-evaluation seems to me to be an absolute non-starter.<br /> </div>If all referenced functions are non-volatile, I don't quite see the<br />problem? Personally I believe we'll have to offer a proper<br />anti-inlining workaround anyway, and in that case there's really nothing<br />that should stop us from inlining CTE without volatile functions twice?</blockquote></div></div></div><div> </div><div>Exactly.</div><div> </div><div>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?</div><div> </div><div>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? </div><div> </div><div>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.</div><div> </div><div><div style="font-family:sans-serif;">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. </div><div style="font-family:sans-serif;"> </div><div style="font-family:sans-serif;">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.</div><div style="font-family:sans-serif;"> </div><div style="font-family:sans-serif;">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.</div></div><div> </div></div></blockquote><div> </div><div>Also, I would like to remind that the disabling optimization fence is suggested to be OPTIONAL.</div><div>So we don't break peoples' expectations, nor documented semantics.</div>