I create three functions, marking each of them "immutable". "f1()" simply returns the manifest constant 'dog'. So it seems perfectly honest to mark it as I did. "f2()" simply returns "f1()"—so, here too, it seems that I'm being honest. But I do see that I'm using human reasoning, and that Postgres cannot check that I'm right. In the same way, and with the same reasoning for my marking, "f3()" returns "f2()".
select rpad('at start', 30) as history, f1(), f2(), f3();
Then I drop, and then re-create "f(1)", now returning 'cat', and do this:
select rpad('after drop, re-create f1()', 30) as history, f1(), f2(), f3();
Finally, I create-and-replace "f3()", using the identical source text, and do this:
select rpad('after create-and-replace f3()', 30) as history, f1(), f2(), f3();
Here's what I see when I run my .sql script:
history | f1 | f2 | f3
--------------------------------+-----+-----+-----
at start | dog | dog | dog
after drop, re-create f1() | cat | cat | dog
after create-and-replace f3() | cat | cat | cat
I understand that the possible session-duration caching that I allow with "immutable" doesn't guarantee that I'll get any caching. But I had expected a cascade purge on anything that was cashed on delete of "f1()".
Something seems odd to me: if I do my "select f1(), f2(), f3()" after dropping "f1()" (i.e. before re-creating it) then I get an ordinary error saying that "f1()" doesn't exist. So it seems that Postgres does understand the dynamic dependency chain—even when the result from "f3()" is cached. If I then recreate "f1()" to return 'cat', I get no error—but, same as in my straight-through test, "f3()" continues to return its cached (and now "wrong") result.
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?
--------------------------------------------------------------------------------
-- testcase.sql
\t off
drop function if exists f3() cascade;
drop function if exists f2() cascade;
drop function if exists f1() cascade;
create function f1()
returns text
immutable
language plpgsql
as $body$
begin
return 'dog';
end;
$body$;
create function f2()
returns text
immutable
language plpgsql
as $body$
begin
return f1();
end;
$body$;
create function f3()
returns text
immutable
language plpgsql
as $body$
begin
return f2();
end;
$body$;
select rpad('at start', 30) as history, f1(), f2(), f3();
\t on
drop function f1() cascade;
create function f1()
returns text
immutable
language plpgsql
as $body$
begin
return 'cat';
end;
$body$;
select rpad('after drop, re-create f1()', 30) as history, f1(), f2(), f3();
create or replace function f3()
returns text
immutable
language plpgsql
as $body$
declare
t1 constant text := f2();
begin
return t1;
end;
$body$;
select rpad('after create-and-replace f3()', 30) as history, f1(), f2(), f3();
\t off