36.7. Function Volatility Categories#
Every function has avolatility classification, with the possibilities beingVOLATILE,STABLE, orIMMUTABLE.VOLATILE is the default if theCREATE FUNCTION command does not specify a category. The volatility category is a promise to the optimizer about the behavior of the function:
A
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.A
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 aVOLATILEfunction in an index scan condition.)An
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 likeSELECT ... WHERE x = 2 + 2can be simplified on sight toSELECT ... WHERE x = 4, because the function underlying the integer addition operator is markedIMMUTABLE.
For best optimization results, you should label your functions with the strictest volatility category that is valid for them.
Any function with side-effectsmust be labeledVOLATILE, so that calls to it cannot be optimized away. Even a function with no side-effects needs to be labeledVOLATILE if its value can change within a single query; some examples arerandom(),currval(),timeofday().
Another important example is that thecurrent_timestamp family of functions qualify asSTABLE, since their values do not change within a transaction.
There is relatively little difference betweenSTABLE andIMMUTABLE 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 functionIMMUTABLE when it really isn't might 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 asPL/pgSQL).
For functions written in SQL or in any of the standard procedural languages, there is a second important property determined by the volatility category, namely the visibility of any data changes that have been made by the SQL command that is calling the function. AVOLATILE function will see such changes, aSTABLE orIMMUTABLE function will not. This behavior is implemented using the snapshotting behavior of MVCC (seeChapter 13):STABLE andIMMUTABLE functions use a snapshot established as of the start of the calling query, whereasVOLATILE functions obtain a fresh snapshot at the start of each query they execute.
Note
Functions written in C can manage snapshots however they want, but it's usually a good idea to make C functions work this way too.
Because of this snapshotting behavior, a function containing onlySELECT commands can safely be markedSTABLE, even if it selects from tables that might be undergoing modifications by concurrent queries.Postgres Pro will execute all commands of aSTABLE function using the snapshot established for the calling query, and so it will see a fixed view of the database throughout that query.
The same snapshotting behavior is used forSELECT commands withinIMMUTABLE functions. It is generally unwise to select from database tables within anIMMUTABLE function at all, since the immutability will be broken if the table contents ever change. However,Postgres Pro does not enforce that you do not do that.
A common error is to label a functionIMMUTABLE when its results depend on a configuration parameter. For example, a function that manipulates timestamps might well have results that depend on theTimeZone setting. For safety, such functions should be labeledSTABLE instead.
Note
Postgres Pro requires thatSTABLE andIMMUTABLE functions contain no SQL commands other thanSELECT to prevent data modification. (This is not a completely bulletproof test, since such functions could still callVOLATILE functions that modify the database. If you do that, you will find that theSTABLE orIMMUTABLE function does not notice the database changes applied by the called function, since they are hidden from its snapshot.)