Обсуждение: BUG #14149: when use LATERAL functions with IMMUTABLE called multiple times

VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDE0OQpMb2dnZWQgYnk6ICAg
ICAgICAgIERhdmlkIFR1cm9uCkVtYWlsIGFkZHJlc3M6ICAgICAgdHVyb24u
ZGF2aWRAc2V6bmFtLmN6ClBvc3RncmVTUUwgdmVyc2lvbjogOS41LjMKT3Bl
cmF0aW5nIHN5c3RlbTogICBDRU5UT1MgNgpEZXNjcmlwdGlvbjogICAgICAg
IAoKSGVsbG8sDQoNCndlIGZvdW5kIHN0cmFuZ2UgYmVoYXZpb3IgTEFURVJB
TCB3aGVuIHdlIHVwZ3JhZGUgZnJvbSA5LjMuMTIgdG8gOS41LjMsCnNpbXBs
ZSBleGFtcGxlOg0KDQpDUkVBVEUgT1IgUkVQTEFDRSBGVU5DVElPTiBmX2lt
dXRhYmxlKE9VVCBhIGludCwgb3V0IGIgaW50KSBBUyAkJCANCkJFR0lOIA0K
ICAgICAgICBhIDo9IDE7IA0KICAgICAgICBiIDo9IDI7IA0KICAgICAgICBS
QUlTRSBOT1RJQ0UgJ2NhbGwgZnVuY3Rpb24gZl9pbXV0YWJsZSc7IA0KRU5E
OyANCiQkIExBTkdVQUdFIHBscGdzcWwgSU1NVVRBQkxFOyANCg0KLS1leGVj
dXRpb24gb24gOS4zLjEyLCBmb3Igb25lIHJvdyBvbmUgY2FsbA0KDQpTRUxF
Q1QgKHgueSkuYSwgKHgueSkuYiBGUk9NIGdlbmVyYXRlX3NlcmllcygxLDEp
LCBMQVRFUkFMIChTRUxFQ1QKZl9pbXV0YWJsZSgpKSBBUyB4KHkpOw0KTk9U
SUNFOiAgY2FsbCBmdW5jdGlvbiBmX2ltdXRhYmxlDQogYSB8IGIgDQotLS0r
LS0tDQogMSB8IDINCigxIMWZw6Fka2EpDQoNCi0tZXhlY3V0aW9uIG9uIDku
NS4zIGNhbGxlZCAyeA0KDQpTRUxFQ1QgKHgueSkuYSwgKHgueSkuYiBGUk9N
IGdlbmVyYXRlX3NlcmllcygxLDEpLCBMQVRFUkFMIChTRUxFQ1QKZl9pbXV0
YWJsZSgpKSBBUyB4KHkpOyAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgIAogICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgDQpOT1RJ
Q0U6ICBjYWxsIGZ1bmN0aW9uIGZfaW11dGFibGUNCk5PVElDRTogIGNhbGwg
ZnVuY3Rpb24gZl9pbXV0YWJsZQ0KIGEgfCBiIA0KLS0tKy0tLQ0KIDEgfCAy
DQooMSDFmcOhZGthKQ0KDQpJIGtub3csIGl0cyBsaXR0bGUgdWdseSB3cml0
dGVuLCBidXQgd2UgZG9uJ3QgZXhjZXB0IHRoaXMgYmVoYXZpb3IgYW5kIGFm
dGVyCnVwZ3JhZGUgd2Ugc2F3IGh1Z2Ugc2xvd2Rvd24gYW5kIHdlIGhhZCB0
byBkb3duZ3JhZGUgYmFjayB0byA5LjMuMTIuIEZvcgpWT0xBVElMRSBmdWN0
aW9ucyB3b3JrcyBmaW5lLg0KDQpUaGFua3MNCgoK

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