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

On Wednesday, June 29, 2022, Bryn Llewellyn <bryn(at)yugabyte(dot)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.