I’ve copied my self-contained testcase at the end.
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()".
Then I do this:
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
