|PostgreSQL Reference Manual - Volume 2 - Programming Guide|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 408 pages
RRP £19.95 ($34.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
5.6 Function Volatility Categories
Every function has a volatility classification, with
the possibilities being
VOLATILE is the default if the
command does not specify a category. The volatility category is a
promise to the optimizer about the behavior of the function:
VOLATILEfunction can do anything, including modifying the database. It can return different results on successive calls with the same arguments. The optimizer makes no assumptions about the behavior of such functions. A query using a volatile function will re-evaluate the function at every row where its value is needed.
STABLEfunction cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a single statement. This category allows the optimizer to optimize multiple calls of the function to a single call. In particular, it is safe to use an expression containing such a function in an index scan condition. (Since an index scan will evaluate the comparison value only once, not once at each row, it is not valid to use a
VOLATILEfunction in an index scan condition.)
IMMUTABLEfunction cannot modify the database and is guaranteed to return the same results given the same arguments forever. This category allows the optimizer to pre-evaluate the function when a query calls it with constant arguments. For example, a query like
SELECT ... WHERE x = 2 + 2can be simplified on sight to
SELECT ... WHERE x = 4, because the function underlying the integer addition operator is marked
For best optimization results, you should label your functions with the strictest volatility category that is valid for them.
Any function with side-effects must be labeled
VOLATILE, so that calls to it cannot be optimized away.
Even a function with no side-effects needs to be labeled
VOLATILE if its value can change within a single query;
some examples are
There is relatively little difference between
IMMUTABLE categories when considering simple interactive
queries that are planned and immediately executed: it doesn't matter
a lot whether a function is executed once during planning or once during
query execution startup. But there is a big difference if the plan is
saved and reused later. Labeling a function
it really isn't may allow it to be prematurely folded to a constant during
planning, resulting in a stale value being re-used during subsequent uses
of the plan. This is a hazard when using prepared statements or when
using function languages that cache plans (such as
Because of the snapshotting behavior of MVCC (see Volume 1: Concurrency Control)
a function containing only
SELECT commands can safely be
STABLE, even if it selects from tables that might be
undergoing modifications by concurrent queries.
PostgreSQL will execute a
function using the snapshot established for the calling query, and so it
will see a fixed view of the database throughout that query.
current_timestamp family of functions qualify
as stable, since their values do not change within a transaction.
The same snapshotting behavior is used for
IMMUTABLE functions. It is generally unwise to select
from database tables within an
IMMUTABLE function at all,
since the immutability will be broken if the table contents ever change.
However, PostgreSQL does not enforce that you
do not do that.
A common error is to label a function
IMMUTABLE when its
results depend on a configuration parameter. For example, a function
that manipulates timestamps might well have results that depend on the
timezone setting. For safety, such functions should
Note: Before PostgreSQL release 8.0, the requirement that
IMMUTABLEfunctions cannot modify the database was not enforced by the system. Release 8.0 enforces it by requiring SQL functions and procedural language functions of these categories to contain no SQL commands other than
SELECT. (This is not a completely bulletproof test, since such functions could still call
VOLATILEfunctions that modify the database. If you do that, you will find that the
IMMUTABLEfunction does not notice the database changes applied by the called function.)
|ISBN 0954612035||PostgreSQL Reference Manual - Volume 2 - Programming Guide||See the print edition|