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

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

david.g.johnston@xxxxxxxxx wrote:

bryn@xxxxxxxxxxxx wrote:

When I drop the first link, f1() in the chain of "immutable" functions, I see that I cannot invoke f(2) because it now complains that f1() doesn't exist. This surprises me because, after all, the result of f2() is now cached (at least as I suppose) and its body isn't executed to produce the result. This outcome almost suggests that there is, after all, a dependency tracking scheme at work.

Yet I can still invoke the third link, f(3), and it still does produce the value that it had cached!

The “cache” is just a prepared statement plan. You didn’t create any of those yourself at the top SQL context so you don’t see caching effects in the stuff you execute in SQL directly.

Thanks, Got it.

PL/pgSQL, however, creates prepared statement plans for any internal SQL it executes (i.e., it compiles the function). That is the caching artefact you are seeing and why I mentioned pl/pgsql in my reply where I described why you saw the results you did.

Thanks again. Got this, too, now.

IOW, when you write : "select f2();" in SQL, f2() is always called, it is never directly replaced with a cached value. The documentation does say this though I lack the relevant paragraph reference at the moment.

Ah... but where is it!

Thanks, David. It's clear that my mental model has been missing at least one critical piece. (My instincts have been conditioned by too many years with Oracle Database to realize when I'm making some wrong assumption in a PG context.)

Anyway... armed with this new knowledge, I can write a much simpler demo of this risk brought when an "ummutable" function calls another with just two function.

I copied this below. But then I made a slight change. And this brought an error that I can't explain. Any ideas?

Meanwhile. I'll appeal for some pointers to what I should read:

I *had* understood that the SQL that a user-created subprogram issues (at least for "language sql" and "language plpgsql") is implicitly prepared. But I've no idea what it uses as the "handle" for such a prepared statement. Might it be, for example, a node in the AST that represents the subprogram or anonymous block in my session? In the same way, I've no idea what the outcome is when two different subprograms issue the identical (or identical post-canonicalization) SQL statement text. I don't know how to search the PG doc to find the explanations that I need. For example "pl/pgsql execution model" gets just a single hit in in a piece about locking. Wider Internet search gets too much noise, and too much unreliable suff from self-appointed experts, to be of much help. I was excited to find "Plpgsql_internals.pdf" by pavel.stehule@xxxxxxxxx. But disappointed to find that it didn't answer my questions (and nor could it when it explains things w.r.t the C implementation).

Does anybody have any recommendations for what I might study?

Of course, I started with the account of "immutable" in the "create function" doc:

«
IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value.

»

"immediately replaced with the function value" implies a cache. But not when it's populated (and when it isn't) or what its lifetime might be. It certainly doesn't mention cache invalidation.

Of course, I read the account in the "prepare" doc too. That says nothing to the effect that the result of prepare, when the SQL statement references an "immutable" function, is to build a cache whose key is the vector of input actuals and whose payload is the corresponding return value. Nor does it mention the cache's capacity and what happens when (if) the cache becomes full.

About your comment thus:

"select f2();" in (explicit) SQL, f2() is always called, it is never directly replaced with a cached value 

I suppose that this can be deduced from the fact that the cache mechanism is the prepared statement (not that this latter point is spelled out).

--------------------------------------------------------------------------------

-- TEST ONE. FITS MY MENTAL MODEL. NICELY SHOWS THE "WRONG RESULTS" RISK.

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$;

prepare q1 as select f1(), f2();
execute q1; --------------------------------<< Gets "dog | dog"

prepare q2 as select f2();
execute q2; --------------------------------<< Gets "dog"

/*
  Presumably dropping f1() invalidates q1 but
  leaves its definition intact so that it can later be re-vaildated
  when f1() exists again.
*/;
drop function f1() cascade;
execute q2; --------------------------------<< Still gets "dog"

create function f1()
  returns text
  immutable
  language plpgsql
as $body$
begin
  return 'cat';
end;
$body$;

execute q1; --------------------------------<< Now gets "cat | cat"
execute q2; --------------------------------<< Still gets "dog"

--------------------------------------------------------------------------------

-- TEST TWO. BREAKS MY MENTAL MODEL.

-- ALL I DID WAS CHANGE f1() AND f2() TO HAVE A TEXT PARAMETER

-- AND TO USE "lower()" IN THE BODY OF f1(),

create function f1(t in text)
  returns text
  immutable
  language plpgsql
as $body$
begin
  return lower(t);
end;
$body$;

create function f2(t in text)
  returns text
  immutable
  language plpgsql
as $body$
begin
  return f1(t);
end;
$body$;

prepare q1(text) as select f1($1), f2($1);
execute q1('DOG'); -------------------------<< Gets "dog | dog"

prepare q2(text) as select f2($1);
execute q2('CAT'); -------------------------<< Gets "cat"

drop function f1(text) cascade;

-- Now fails with "function f1(text) does not exist"
execute q2('CAT');