On Wednesday, June 29, 2022, Bryn Llewellyn <
bryn@yugabyte.com> 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.
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.
IOW, when you write : “ select f2(); “ in SQL f2() is always called, it is never durectly replaced with a cached value. The documentation does say this though I lack the relevant paragraph reference at the moment.
David J.