> david.g.johns...@gmail.com wrote: > >> x...@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.
