Re: Implement targetlist SRFs using ROWS FROM() (was Changed SRF in targetlist handling)

On 23/08/16 09:40, Andres Freund wrote:
> Hi,
>
> as noted in [1] I started hacking on removing the current implementation
> of SRFs in the targetlist (tSRFs henceforth). IM discussion brought the
> need for a description of the problem, need and approach to light.
>
> There are several reasons for wanting to get rid of tSRFs. The primary
> ones in my opinion are that the current behaviour of several SRFs in one
> targetlist is confusing, and that the implementation burden currently is
> all over the executor.  Especially the latter is what is motivating me
> working on this, because it blocks my work on making the executor faster
> for queries involving significant amounts of tuples.  Batching is hard
> if random places in the querytree can icnrease the number of tuples.
>
> The basic idea, hinted at in several threads, is, at plan time, to convert a query like
> SELECT generate_series(1, 10);
> into
> SELECT generate_series FROM ROWS FROM(generate_series(1, 10));
>
> thereby avoiding the complications in the executor (c.f. execQual.c
> handling of isDone/ExprMultipleResult and supporting code in many
> executor nodes / node->*.ps.ps_TupFromTlist).
>
> There are several design questions along the way:
>
> 1) How to deal with the least-common-multiple behaviour of tSRFs. E.g.
> =# SELECT generate_series(1, 3), generate_series(1,2);
> returning
> ┌─────────────────┬─────────────────┐
> │ generate_series │ generate_series │
> ├─────────────────┼─────────────────┤
> │               1 │               1 │
> │               2 │               2 │
> │               3 │               1 │
> │               1 │               2 │
> │               2 │               1 │
> │               3 │               2 │
> └─────────────────┴─────────────────┘
> (6 rows)
> but
> =# SELECT generate_series(1, 3), generate_series(5,7);
> returning
> ┌─────────────────┬─────────────────┐
> │ generate_series │ generate_series │
> ├─────────────────┼─────────────────┤
> │               1 │               5 │
> │               2 │               6 │
> │               3 │               7 │
> └─────────────────┴─────────────────┘
>
> discussion in this thread came, according to my reading, to the
> conclusion that that behaviour is just confusing and that the ROWS FROM
> behaviour of
> =# SELECT * FROM ROWS FROM(generate_series(1, 3), generate_series(1,2));
> ┌─────────────────┬─────────────────┐
> │ generate_series │ generate_series │
> ├─────────────────┼─────────────────┤
> │               1 │               1 │
> │               2 │               2 │
> │               3 │          (null) │
> └─────────────────┴─────────────────┘
> (3 rows)
>
> makes more sense.
I had always implicitly assumed that having 2 generated sequences would 
act as equivalent to:

SELECT
     sa,
     sb
FROM
     ROWS FROM(generate_series(1, 3)) AS sa,
     ROWS FROM(generate_series(5, 7)) AS sb
ORDER BY
     sa,
     sb;

  sa | sb
----+----
   1 |  5
   1 |  6
   1 |  7
   2 |  5
   2 |  6
   2 |  7
   3 |  5
   3 |  6
   3 |  7


Obviously I was wrong - but to me, my implicit assumption makes more sense!
[...]


Cheers,
Gavin