Re: with (iscachable)

Tom Lane wrote:
> Reasonable definitions would be:
>
> 1. noncachable: must be called every time; not guaranteed to return same
> result for same parameters even within a query. random(), timeofday(),
> nextval() are examples.
>
> 2. fully cachable: function guarantees same result for same parameters
> no matter when invoked. This setting allows a call with constant
> parameters to be constant-folded on sight.
Something like strlower() or metaphone() are perfect examples.
>
> 3. query cachable: function guarantees same result for same parameters
> within a single query, or more precisely within a single
> CommandCounterIncrement interval. This corresponds to the actual
> behavior of functions that execute SELECTs, and it's sufficiently strong
> to allow the function result to be used in an indexscan, which is what
> we really care about.

This is IMHO the important one. I would not presume to argue the scope, but as
someone who has spent the last year building entire systems on Postgres (and
loving every minute of it) I would really like a fairly reliable definition
from which someone can understand and predict the behavior.

Currently, it seems fairly reliable that "iscachable" functions are called once
per unique parameter within the scope of a select, and I like this behavior.
When/if it breaks I will have some work to do. (As observed with fairly simple
queries.)

>
> I'm by no means wedded to those names ... maybe someone can think of
> better terminology.

I kinda like the terminology I presented, "cacheable, non-cacheable,
persistent" but hey, they are only words. I don't think people will be
confused, "persistent" and "non-cacheable" are pretty obvious.
>
> > With the above definitions, it would make sense to have "iscacheable" as the
> > default for a function.
>
> I'd still vote for noncachable as the default; unsurprising behavior is
> to be preferred over maximum performance IMHO.

I've thought about this one, a lot, and I can totally see your point of view,
but it isn't clear to me that it would be surprising that a function would only
be called once per unique set of parameters per select. What do other databases
do?