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

> david(dot)g(dot)johnston(at)gmail(dot)com wrote:
>
> bryn(at)yugabyte(dot)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-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"