Tom Lane wrote:
> 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.
I was actually thinking of only caching when the structure of the
expression tree means that it is known to be constant across some period
-- e.g. (non-subquery) Params remain constant across a single query
execution. So there's no hashtable or datatype-specific comparisons
involved. The cache only lives as long as you can guarantee the
expression tree remains constant. I'm just trying to work out the best
lifetime for the cache (or, equivalently, the types of expression tree
that can be marks as cacheable).
> 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.
Fair enough. My concern is that if every query is parameterized by an
interface layer (as I'm planning to do with the JDBC driver), that one
expensive IMMUTABLE function is going to bite the application. So I'd
still like to see some sort of caching so that those few queries don't
run significantly slower, assuming that the cost of caching in the
common case is minor.
>>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.)
Does that mean that these functions satisfy "IMMUTABLE until the next
CommandCounterIncrement"? That sounds more cacheable than the
tablescan-based definition.
-O