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

david.g.johnston@gmail.com wrote:

xof@thebuild.com wrote:

bryn@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.