Oliver Jowett <oliver@opencloud.com> writes:
> But there are still other expression trees
> that would benefit, e.g. those involving an IMMUTABLE function with
> parameterized arguments.
Oh, you are thinking of some very-long-lived cache. This has been
proposed and rejected before; it's just not apparent that the costs of
maintaining and searching such a cache are justified by the possible
benefits. Most of the functions that actually appear in SQL commands
are cheap enough to evaluate that it'd not be worthwhile to do this at
all for them, ever --- the costs of executing datatype-specific
comparison functions to verify a hashtable hit would equal or exceed
the cost of evaluating the function.
There certainly are expensive user functions out there, and if we knew
which ones those were, it might be worth caching their values. But we
don't presently have any way to identify such functions. More, I'm not
convinced that very many of the ones that are that expensive can
reasonably be marked IMMUTABLE; an expensive function is likely one that
does database accesses.
> It seems quite hard to build a STABLE function that doesn't also satisfy
> the stronger requirements. I can't think of how you'd do it as a SQL
> function at all, off the top of my head. What sort of function were you
> thinking of that is STABLE-safe but doesn't satisfy the stronger
> requirements?
Anything at all that inspects database contents is probably STABLE and
not anything stronger, since it could potentially be affected by
intra-transaction updates. (The definition of STABLE is partly
motivated by MVCC semantics, particularly the fact that updates executed
by a command only become visible at CommandCounterIncrement boundaries.)
regards, tom lane