Re: Unclear documentation (IMMUTABLE functions)

Neil Conway <neilc(at)samurai(dot)com> writes:
> While we're on the subject, this adjacent paragraph of the docs seems
> unclear:

> STABLE indicates that within a single table scan the function
> will consistently return the same result for the same argument
> values, but that its result could change across SQL statements.
> This is the appropriate selection for functions whose results
> depend on database lookups, parameter variables (such as the
> current time zone), etc. Also note that the current_timestamp
> family of functions qualify as stable, since their values do not
> change within a transaction.

> So, can a STABLE function change across SQL statements (as the beginning
> of the paragraph implies), or across transactions (as the end of the
> paragraph implies)?

The former. current_timestamp is at the less-volatile end of the range
of behaviors that are legal for STABLE functions. Nonetheless, it is
one, and a rather important member of the category at that. If we had a
standard function that was at the more-volatile end of the range, I'd be
happy to mention it as another example; but I don't think we have any.

What the system actually uses these categories for are (AFAIR) three
decisions. In decreasing order of the amount of stability needed:

1. Is it okay to make a functional index on this function? (It had
better hold still forever.)

2. Can I fold a function call with constant arguments to a constant at
planning time? (It had better hold still for as long as the plan
will survive, which is presently not further than the end of the
current session. But if we ever start caching plans across backends,
this will start to look a lot like #1, so there's not a separate
provolatile category for this.)

3. Is it okay to use this function call in an indexscan constraint?
(It had better hold still while the index scan is active.)

Case 3 is the one that motivates having a STABLE category. The real
definition of STABLE is "can't change value within a single indexscan".
To make use of an index, you have to assume that the value you're
comparing to the index column will hold still across the whole scan.
As an example, "where foo = random()" isn't indexable; we have to fall
back to the naive SQL semantics that says to evaluate the WHERE
condition at every row. But "where msgdate >= current_date()" is safe
to index, and so would be a function call that involved database lookup,
because the active snapshot can't change within an indexscan.

I think we may also insist that functions appearing in merge/hash join
conditions be STABLE, for more or less the same reasons as indexscans.

regards, tom lane