Thread: BUG #14149: when use LATERAL functions with IMMUTABLE called multiple times
BUG #14149: when use LATERAL functions with IMMUTABLE called multiple times
From
turon.david@seznam.cz
Date:
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