On Wed, Mar 23, 2016 at 6:34 PM, Tom Lane <t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers