Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux