st 29. 6. 2022 v 6:28 odesílatel Bryn Llewellyn <bryn@xxxxxxxxxxxx> napsal:
david.g.johnston@xxxxxxxxx wrote:xof@xxxxxxxxxxxx wrote:bryn@xxxxxxxxxxxx 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 functioncreate function f1() returns text as $$ begin return 'dog'; end $$ language plpgsql immutable;
select f1(), f2(), f3();
f1 | f2 | f3
-----+-----+-----
dog | dog | catAnd 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
