BUG #14149: when use LATERAL functions with IMMUTABLE called multiple times

turon.david@seznam.cz writes:

we found strange behavior LATERAL when we upgrade from 9.3.12 to 9.5.3,
simple example:

CREATE OR REPLACE FUNCTION f_imutable(OUT a int, out b int) AS $$
BEGIN
a := 1;
b := 2;
RAISE NOTICE 'call function f_imutable';
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--execution on 9.3.12, for one row one call

SELECT (x.y).a, (x.y).b FROM generate_series(1,1), LATERAL (SELECT
f_imutable()) AS x(y);

--execution on 9.5.3 called 2x

I think you are confusing an implementation artifact of older versions
with a guaranteed behavior. Declaring a function IMMUTABLE (or STABLE)
says that it's okay if the generated plan calls the function more or fewer
times than naive analysis might suggest. 9.3 happened not to do so, for
this specific query, but 9.5 does.

Really the best fix for this is to mark a function VOLATILE if you can't
afford for the planner to rearrange the calls. In this particular case,
you might also consider rearranging the query so that the function is
called as a FROM item rather than a select-list item:

# SELECT x.a, x.b FROM generate_series(1,1), LATERAL f_imutable() as x;
NOTICE: call function f_imutable
a | b
---+---
1 | 2
(1 row)

but I wouldn't really want to promise that that won't ever change behavior
either. The argument for it is as much that it's a less messy notation
as anything else.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs