38.7. Function Volatility Categories

38.7. Function Volatility Categories
Prev	Up	Chapter 38. Extending SQL	Home	Next

38.7. Function Volatility Categories #

+ Every function has a volatility classification, with + the possibilities being VOLATILE, STABLE, or + IMMUTABLE. VOLATILE is the default if the + CREATE FUNCTION + command does not specify a category. The volatility category is a + promise to the optimizer about the behavior of the function: + +

+ A VOLATILE function 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 STABLE function 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 VOLATILE function in an + index scan condition.) +
+ An IMMUTABLE function 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 + 2 can be simplified on sight to + SELECT ... WHERE x = 4, because the function underlying + the integer addition operator is marked IMMUTABLE. +

+ 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 random(), currval(), + timeofday(). +

+ Another important example is that the current_timestamp + family of functions qualify as STABLE, since their values do + not change within a transaction. +

+ There is relatively little difference between STABLE and + 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 IMMUTABLE 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 as + PL/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. A + VOLATILE function will see such changes, a STABLE + or IMMUTABLE function will not. This behavior is implemented + using the snapshotting behavior of MVCC (see Chapter 13): + STABLE and IMMUTABLE functions use a snapshot + established as of the start of the calling query, whereas + VOLATILE 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 only SELECT commands can safely be + marked STABLE, even if it selects from tables that might be + undergoing modifications by concurrent queries. + PostgreSQL will execute all commands of a + STABLE 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 for SELECT commands + within 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 + be labeled STABLE instead. +

Note

+ PostgreSQL requires that STABLE + and IMMUTABLE functions contain no SQL commands other + than SELECT to prevent data modification. + (This is not a completely bulletproof test, since such functions could + still call VOLATILE functions that modify the database. + If you do that, you will find that the STABLE or + IMMUTABLE function does not notice the database changes + applied by the called function, since they are hidden from its snapshot.) +

Prev	Up	Next
38.6. Function Overloading	Home	38.8. Procedural Language Functions