Re: User's responsibility when using a chain of "immutable" functions?

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

david.g.johnston@xxxxxxxxx wrote:

bryn@xxxxxxxxxxxx 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-CACHING

You 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"