> [email protected] wrote:
>
> [email protected] 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"
