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

st 29. 6. 2022 v 6:28 odesílatel Bryn Llewellyn <bryn(at)yugabyte(dot)com> napsal:

> *david(dot)g(dot)johnston(at)gmail(dot)com <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:*
>
>
> *xof(at)thebuild(dot)com <of(at)thebuild(dot)com> wrote:*
>
> bryn(at)yugabyte(dot)com wrote:
>
> Should I simply understand that when I have such a dynamic dependency
> chain of "immutable" functions, and should I drop and re-create the
> function at the start of the chain, then all bets are off until I drop and
> re-create every function along the rest of the chain?
>
>
> Yes.
>
> You don't have to drop and recreate the functions, though. DISCARD PLANS
> handles it as well:
>
>
> Specifically:
>
>
>
>
>
> *select f1(), f2(), f3(); f1 | f2 | f3 -----+-----+----- cat | cat |
> cat*
>
> The pl/pgsql plan cache now contains the following:
>
> SELECT f1() => 'cat'
> SELECT f2() => 'cat'
>
>
> *drop function f1();*
> Now the cache only contains:
>
> SELECT f2() => 'cat'
>
> The f1 plan has been invalidated due to the drop/replace action on the f1
> function
>
>
> *create function f1() returns text as $$ begin return 'dog'; end $$
> language plpgsql immutable;*
>
>
>
> *select f1(), f2(), f3(); f1 | f2 | f3 -----+-----+----- dog | dog |
> cat*
>
>
> And so f3()'s invocation of "SELECT f2()" yields 'cat' from the cache
> since that one hasn't been invalidated. While f2() replans its f1()
> invocation and thus returns 'dog'
>
> The fundamental limitation here is that there really is no attempt being
> made to deal with inter-functional dependencies. Their bodies are
> blackboxes (...wonders how this resolves in the new SQL Standard
> Function Bodies implementation...) and no explicit dependency information
> is recorded either. So we don't know that the saved plan for f2() depends
> on a specific version of f1() and thus if f1() is changed plans involving
> f2() should be invalidated along with plans involving f1(). Nor is there
> sufficient recognized benefit to doing so.
>
>
> DISCARD PLANS is unsafe in a multi-user concurrent scenario. The doc says
> explicitly that its scope is just the single session. And it's easy to show
> the danger by using my testcase manually, step by appropriate step, with
> two concurrent sessions.
>
> However, you said (indirectly) that the session-duration caching is a red
> herring—and that the real danger comes with an expression-based index that
> involves a PL/pgSQL function. I agree.
>
> PG's lack of dependency tracking shows up with just a "worker" function
> f1() and a "jacket" function f2() when you base the index on f2(). You can
> happily drop and recreate f1() with a new implementation while the index
> lives on. (For the reasons that we've mentioned, the "2BP01: cannot drop
> function... because other objects depend on it" error doesn't occur.)
>
> *I've concluded that the only practical practice for "immutable" is to
> reserve its use for functions that don't mention even a single user-created
> artifact*.
>
> Moreover, this "hermetic" property of a to-be-immutable function can be
> established only by human analysis of the function's source code.
>

Our immutable functions are more tolerant than they should be - for real
immutable functions we should disallow SQL inside functions (and everything
that is not immutable (plpgsql_check raises warning in this case)), but it
is allowed. On the second hand, it allows some very dirty tricks with the
planner.

Regards

Pavel