Re: Early WIP/PoC for inlining CTEs

On Sat, 17 Nov 2018 at 10:12, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * Andrew Gierth (andrew(at)tao11(dot)riddles(dot)org(dot)uk) wrote:
> > >>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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