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

On Tue, Jun 28, 2022 at 7:03 PM Christophe Pettus <xof(at)thebuild(dot)com> wrote:

>
>
> > On Jun 28, 2022, at 18:41, Bryn Llewellyn <bryn(at)yugabyte(dot)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:

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