Re: Suggestions please: names for function cachability

On Tue, 2 Apr 2002, Peter Eisentraut wrote:

> Tom Lane writes:
>
> > 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.
>
> Well, for one thing, we might want to change the name to the correct
> spelling "cacheable".
>
> > 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.
>
> deterministic
>
> (That's how SQL99 calls it.)
>
> > 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.
>
> "cacheable" seems OK for this.

SQL99 suggests that there are only two types of user defined
routines: deterministic and 'possibly non-deterministic'. However, in
section 11.49 it defines

<deterministic characteristic> ::= DETERMINISTIC | NOT DETERMINISTIC

So the real problem is how to qualify this.

TRANSACTIONAL DETERMINISTIC

or

NOT DETERMINISTIC CACHEABLE

are the only ways that come to mind. I'll admit that I don't like either.

>
> > 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-datetime.html#FUNCTIONS-DATETIME-CURRENT)
>
> not deterministic, not cacheable
>
>

Gavin