My 2 cents.
Level 1. with (isCachableStatic)
Level 2. with (isCachableDynamic)
Level 3. default
In my mind (isCachable) sounds like level 1
On Tuesday 02 April 2002 03:40 pm, Tom Lane wrote:
> Since I'm about to have to edit pg_proc.h to add a namespace column,
> I thought this would be a good time to revise the current proiscachable
> column into the three-way cachability distinction we've discussed
> before. But I need some names for the values, and I'm not satisfied
> with the ideas I've had so far.
>
> To refresh people's memory: what we want is to be able to distinguish
> between functions that are:
>
> 1. Strictly cachable (a/k/a constant-foldable): given fixed input
> values, the same result value will always be produced, for ever and
> ever, amen. Examples: addition operator, sin(x). Given a call
> of such a function with all-constant input values, the system is
> entitled to fold the function call to a constant on sight.
>
> 2. Cachable within a single command: given fixed input values, the
> result will not change if the function were to be repeatedly evaluated
> within a single SQL command; but the result could change over time.
> Examples: now(); datetime-related operations that depend on the current
> timezone (or other SET-able variables); any function that looks in
> database tables to determine its result.
>
> 3. Totally non-cachable: result may change from one call to the next,
> even within a single SQL command. Examples: nextval(), random(),
> timeofday(). (Yes, timeofday() and now() are in different categories.
> See
> http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions-datet
>ime.html#FUNCTIONS-DATETIME-CURRENT)
>
> Currently the system can only distinguish cases 1 and 3, so functions
> that are really case 2 have to be labeled as case 3; this prevents a lot
> of useful optimizations. In particular, it is safe to use expressions
> involving only case-1 and case-2 functions as indexscan conditions,
> whereas case-3 functions cannot be optimized into an indexscan. So this
> is an important fix to make.
>
> BTW, because of MVCC semantics, case 2 covers more ground than you might
> think. We are interested in functions whose values cannot change during
> a single "scan", ie, while the intra-transaction command counter does
> not increment. So functions that do SELECTs are actually guaranteed to
> be case 2, even if stuff outside the function is changing the table
> being looked at.
>
> My problem is picking names for the three categories of functions.
> Currently we use "with (isCachable)" to identify category 1, but it
> seems like this name might actually be more sensible for category 2.
> I'm having a hard time picking simple names that convey these meanings
> accurately, or even with a reasonable amount of suggestiveness.
>
> Comments, ideas?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
