On Sat, 17 Nov 2018 at 10:12, Stephen Frost <sfr...@snowman.net> wrote:
> Greetings, > > * Andrew Gierth (and...@tao11.riddles.org.uk) wrote: > > >>>>> "Tom" == Tom Lane <t...@sss.pgh.pa.us> writes: > > > > >> [ inlining-ctes-v5.patch ] > > > > Tom> I took a little bit of a look through this. Some thoughts: > > > > Tom> * I think it'd be a good idea if we made OFFSET/LIMIT in a CTE be > > Tom> an alternate way of keeping it from being inlined. As the patch > > Tom> stands, if that's the behavior you want, you have no way to > > Tom> express it in a query that will also work in older servers. (I > > Tom> will manfully resist suggesting that then we don't need the > > Tom> nonstandard syntax at all ... oops, too late.) > > > > I think this is the wrong approach, because you may want the > > optimization-barrier effects of OFFSET/LIMIT _without_ the actual > > materialization - there is no need to force a query like > > > > with d as (select stuff from bigtable offset 1) select * from d; > > > > to push all the data through an (on-disk) tuplestore. > > Agreed, there's going to be cases where you want the CTE to be inlined > even with OFFSET/LIMIT. Let's please not cater to the crowd who > happened to know that they could hack around with OFFSET/LIMIT to make > something not be inlined when it comes to the question of if the CTE > should be inlined or not. That's the same issue we were argueing around > when discussing if we should allow parallel array_agg, imv. > > Particularly since, with CTEs anyway, we never inlined them, so the > whole OFFSET/LIMIT thing doesn't really make any sense- today, if you > wrote a CTE, you wouldn't bother with OFFSET/LIMIT because you knew it > wasn't going to be inlined, that entire line of thinking is for > subqueries, not CTEs. If you're going to force people to change their > CTEs to require that they not be inlined, let's not pick a method which > makes it ambiguous and makes us have to ask "do they really want this > limit/offset, or did they just want to make the CTE not be inlined...?" > > To satisfy Tom's understandable desire to let people write queries that behave the same on old and new versions, can we get away with back-patching the MATERIALIZED parser enhancement as a no-op in point releases? -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services