st 29. 6. 2022 v 6:28 odesílatel Bryn Llewellyn <b...@yugabyte.com> napsal:
> *david.g.johns...@gmail.com <david.g.johns...@gmail.com> wrote:* > > > *x...@thebuild.com <o...@thebuild.com> wrote:* > > b...@yugabyte.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
