Re: Strange behavior of some volatile function like random(), nextval()

[ Please do not quote the entire thread in each followup. That's
disrespectful of your readers' time, and will soon cause people to
stop reading the thread, meaning you don't get answers. ]

Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru> writes:
> In this subquery(below) we have reference to outer variables but it is
> not working as it should(or i dont understand something):

> postgres=# postgres=# select id, ( select string_agg('a','') from
> generate_series(1,trunc(10*random()+1)::int) where id=id) from
> generate_series(1,10) as id;

The inner generate_series() call does not contain any outer references, so
it doesn't get recomputed. There's a comment in ExecReScanFunctionScan
about that:

* Here we have a choice whether to drop the tuplestores (and recompute
* the function outputs) or just rescan them. We must recompute if an
* expression contains changed parameters, else we rescan.
*
* XXX maybe we should recompute if the function is volatile? But in
* general the executor doesn't conditionalize its actions on that.

So you get some random number of generate_series output rows on the first
call, but then each subsequent run of the subquery just rescans those same
rows. I do not think this is wrong or bad, really; if it was done
differently, examples such as this same generate_series call on the inside
of a nestloop join would behave very strangely.

regards, tom lane