On Tue, Jun 28, 2022 at 7:03 PM Christophe Pettus <xof@xxxxxxxxxxxx> wrote:
> On Jun 28, 2022, at 18:41, Bryn Llewellyn <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:
xof=# select f1(), f2(), f3();
f1 | f2 | f3
-----+-----+-----
cat | cat | cat
(1 row)
The pl/pgsql plan cache now contains the following:
SELECT f1() => 'cat'
SELECT f2() => 'cat'
xof=# drop function f1();
DROP FUNCTION
Now the cache only contains:
SELECT f2() => 'cat'
The f1 plan has been invalidated due to the drop/replace action on the f1 function
xof=# create function f1() returns text as $$ begin return 'dog'; end $$ language plpgsql immutable;
CREATE FUNCTION
xof=# select f1(), f2(), f3();
f1 | f2 | f3
-----+-----+-----
dog | dog | cat
(1 row)
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.
David J.
