david.g.johnston@gmail.com wrote:
bryn@yugabyte.com wrote:
Meanwhile. I'll appeal for some pointers to what I should read...
I tend not to search...or at least that isn't my first (or at least only) recourse. The pg/pgsql chapter has a subchapter named "Plan Caching":
https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHINGYou really need to read the "see related" reference there to get the level of detail that you want:
https://www.postgresql.org/docs/current/xfunc-volatility.html
"This category allows the optimizer to pre-evaluate the function when a query calls it with constant arguments."
The implication is that this operation is not session-scoped but query-scoped. Other parts of the page reinforce this. Not saying it is perfect wording but I came by my understanding pretty much exclusively from this documentation.
Thank you very much for the doc pointers, David. I believe that I have all I need, now. I understood already that "giving permission to cache" doesn't mean that PG will actually cache anything. I wanted only to find a compelling example of how lying when you mark a function "immutable" can bring wring results. I think that this is sufficient:
set x.a = '13';
create function dishonestly_marked_immutable(i in int)
returns int
immutable
language plpgsql
as $body$
begin
return i*(current_setting('x.a')::int);
end;
$body$;
prepare q as
select
dishonestly_marked_immutable(2) as "With actual '2'",
dishonestly_marked_immutable(3) as "With actual '3'";
execute q;
set x.a = '19';
execute q; ------------------<< Produces the stale "26 | 39".
discard plans;
execute q; ------------------<< Now produces the correct "38 | 57"
