Re: Volatile functions under Memoize node

On Fri, 20 Sept 2024 at 20:46, Andrei Lepikhov <lepihov@gmail.com> wrote:
> I skimmed the code entries with such checks and found out that the
> initial reason was to avoid index scans, with a reason that such a
> routine should be applied to each tuple of the table.
> The second reason - to postpone expression evaluation (9118d03) - is
> also reasonable for me. It was introduced to be consistent with the
> clause's syntactical level in the SQL.
> It seems to follow the same idea as disabling subquery pull-ups: to
> avoid multiple evaluations and change the syntactical level.
> At the same time, Material doesn't care about volatility. So, what was
> the idea behind the commit 990c365 you added?

I don't recall. Likely to try and keep Memoize more in keeping with
what would happen prior to Memoize existing.

It seems customary around here to disable various optimisations in the
planner when there are volatile functions to try to avoid changing the
number of evaluations of the volatile function. However, I've yet to
see any sort of standard we're meant to be abiding by for it.

You only need to look at things like;

postgres=# select random(), random();
       random       |       random
--------------------+--------------------
 0.6097568694225706 | 0.5371689823343302
(1 row)


postgres=# select random(), random() order by random();
       random        |       random
---------------------+---------------------
 0.16673781514021058 | 0.16673781514021058
(1 row)

and you might be left scratching your head at why all the random()
calls randomly returned the same value. Of course, the above behaviour
is on purpose, but there are certainly reports of people questioning
it [1].

David

[1] https://www.postgresql.org/message-id/CALA8mJrDQhL-kntd%3DypBgwvogL8%3Dkspn5za1Mxv%2BmS%3DdinL5Sg%40mail.gmail.com