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

On Wed, Jun 29, 2022 at 5:03 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

>
> *Meanwhile. I'll appeal for some pointers to what I should read:*
> I *had* understood that the SQL that a user-created subprogram issues (at
> least for "language sql" and "language plpgsql") is implicitly prepared.
> But I've no idea what it uses as the "handle" for such a prepared
> statement. Might it be, for example, a node in the AST that represents the
> subprogram or anonymous block in my session? In the same way, I've no idea
> what the outcome is when two different subprograms issue the identical (or
> identical post-canonicalization) SQL statement text. I don't know how to
> search the PG doc to find the explanations that I need. For example
> "pl/pgsql execution model" gets just a single hit in in a piece about
> locking.
>

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

Wider Internet search gets too much noise, and too much unreliable suff
> from self-appointed experts, to be of much help. I was excited to find
> "Plpgsql_internals.pdf" by pavel(dot)stehule(at)gmail(dot)com(dot) But disappointed to
> find that it didn't answer my questions (and nor could it when it explains
> things w.r.t the C implementation).
>
> Does anybody have any recommendations for what I might study?
>
> Of course, I started with the account of "immutable" in the "create
> function" doc:
>
> «
> IMMUTABLE indicates that the function cannot modify the database and
> always returns the same result when given the same argument values; that
> is, it does not do database lookups or otherwise use information not
> directly present in its argument list. If this option is given, any call of
> the function with all-constant arguments can be immediately replaced with
> the function value.
> »
>
> "immediately replaced with the function value" implies a cache. But not
> when it's populated (and when it isn't) or what its lifetime might be. It
> certainly doesn't mention cache invalidation.
>
>
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.

>
>
>
>
>
>
>
>
> */* Presumably dropping f1() invalidates q1 but leaves its definition
> intact so that it can later be re-vaildated when f1() exists again.*/;drop
> function f1() cascade;execute q2; --------------------------------<< Still
> gets "dog"*
>

I think my cache example was misleading...
for f2():
LINE 2 in text: SELECT f1();
LINE 2 in the compiled code: SELECT 'dog'; -- no input arg so replace the
call with its constant return value

*--------------------------------------------------------------------------------*
> *-- TEST TWO. BREAKS MY MENTAL MODEL.*
> *-- ALL I DID WAS CHANGE f1() AND f2() TO HAVE A TEXT PARAMETER*
>

See below - a "this is all I did" is totally insufficient.

>
> *-- Now fails with "function f1(text) does not exist"execute q2('CAT');*
>
>
f2(text) cannot do anything because the return result, while immutable,
depends upon the value of "t".

Plausibly you might be able to produce something like:

select f2('DOG') => 'dog'
redefine f1(test); return 'cat'
select f2('DOG') => 'dog'
DISCARD ALL
select f2('DOG') => 'cat'

But in short extrapolating from a zero-argument scenario to a one-argument
scenario makes no sense. There are many more things to worry about, and
thus more reason to not optimize, when an input argument is involved.
Specifically, it is unlikely to be worth doing anything except within the
scope of a single query.

David J.