Re: PostgreSQL 9.6 behavior change with set returning (funct).*

On Wed, Mar 23, 2016 at 6:34 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I wrote:
>> ... I'd love to
>> toss the entire SRF-in-tlist feature overboard one of these years,
>> both because of semantic issues like these and because a fair amount
>> of code and overhead could be ripped out if it were disallowed.
>> But I don't know how we get to that --- as Merlin says, there's a
>> pretty large amount of application code depending on the feature.
>
> BTW, after further thought I seem to recall some discussion about whether
> we could mechanically transform SRF-in-tlist into a LATERAL query.
> That is, we could make the rewriter or planner convert
>
> SELECT srf1(...), srf2(...)
> FROM ...
> WHERE ...
>
> into
>
> SELECT u.s1, u.s2
> FROM ..., LATERAL ROWS FROM(srf1(...), srf2(...)) AS u(s1,s2)
> WHERE ...

I think *that* would be grand. If I'm not wrong, that's the behavior
that anybody would naively expect.

> (The SRF invocations might be buried inside expressions, but we'd find
> them and convert them anyway. Also, we'd merge textually-identical SRF
> invocations into a single ROWS FROM entry to avoid multiple evaluation,
> at least for SRFs not marked volatile.) Having done that, the executor's
> support for SRFs in general expressions could be removed, a significant
> savings.

That is a highly appealing fringe benefit.

> One problem with this is that it only duplicates the current behavior
> in cases where the SRFs all have the same period. But you could argue
> that the current behavior in cases where they don't is widely considered
> a bug anyway.

I would so argue. Also, wouldn't this fix the problem that
(srf(blah)).* causes multiple evaluation? That would be *awesome*.

> A possibly larger problem is that it causes the SRFs to be evaluated
> before sorting/ordering/limiting.

I'm not sure I understand quite what the problem is here. If there's
a LIMIT, then the proposed transformation would presumably run the SRF
only enough times to fill the limit. I'm not sure you have any
guarantees about ordering anyway - doesn't that depend on whether the
planner can find a way to produce presorted output via an index scan,
merge join, etc.?

> In view of the discussions that led
> up to 9118d03a8, that could be a fatal objection :-(. Maybe we could
> get around it with a different transformation, into a two-level query?
> The above sketch doesn't really consider what to do with GROUP BY,
> ORDER BY, etc, but maybe we could push those down into a sub-select
> that becomes the first FROM item.

That seems like it might work.

> Anyway, that's food for thought not something that could realistically
> happen right now.

Ah, bummer.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company