diff options
Diffstat (limited to 'doc/src/sgml/html/xfunc-volatility.html')
| -rw-r--r-- | doc/src/sgml/html/xfunc-volatility.html | 107 |
1 files changed, 107 insertions, 0 deletions
diff --git a/doc/src/sgml/html/xfunc-volatility.html b/doc/src/sgml/html/xfunc-volatility.html new file mode 100644 index 0000000..b34ef2f --- /dev/null +++ b/doc/src/sgml/html/xfunc-volatility.html @@ -0,0 +1,107 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>38.7. Function Volatility Categories</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="xfunc-overload.html" title="38.6. Function Overloading" /><link rel="next" href="xfunc-pl.html" title="38.8. Procedural Language Functions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">38.7. Function Volatility Categories</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="xfunc-overload.html" title="38.6. Function Overloading">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="extend.html" title="Chapter 38. Extending SQL">Up</a></td><th width="60%" align="center">Chapter 38. Extending <acronym class="acronym">SQL</acronym></th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="xfunc-pl.html" title="38.8. Procedural Language Functions">Next</a></td></tr></table><hr /></div><div class="sect1" id="XFUNC-VOLATILITY"><div class="titlepage"><div><div><h2 class="title" style="clear: both">38.7. Function Volatility Categories</h2></div></div></div><a id="id-1.8.3.10.2" class="indexterm"></a><a id="id-1.8.3.10.3" class="indexterm"></a><a id="id-1.8.3.10.4" class="indexterm"></a><a id="id-1.8.3.10.5" class="indexterm"></a><p> + Every function has a <em class="firstterm">volatility</em> classification, with + the possibilities being <code class="literal">VOLATILE</code>, <code class="literal">STABLE</code>, or + <code class="literal">IMMUTABLE</code>. <code class="literal">VOLATILE</code> is the default if the + <a class="link" href="sql-createfunction.html" title="CREATE FUNCTION"><code class="command">CREATE FUNCTION</code></a> + command does not specify a category. The volatility category is a + promise to the optimizer about the behavior of the function: + + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + A <code class="literal">VOLATILE</code> 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. + </p></li><li class="listitem"><p> + A <code class="literal">STABLE</code> 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 <code class="literal">VOLATILE</code> function in an + index scan condition.) + </p></li><li class="listitem"><p> + An <code class="literal">IMMUTABLE</code> 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 + <code class="literal">SELECT ... WHERE x = 2 + 2</code> can be simplified on sight to + <code class="literal">SELECT ... WHERE x = 4</code>, because the function underlying + the integer addition operator is marked <code class="literal">IMMUTABLE</code>. + </p></li></ul></div><p> + </p><p> + For best optimization results, you should label your functions with the + strictest volatility category that is valid for them. + </p><p> + Any function with side-effects <span class="emphasis"><em>must</em></span> be labeled + <code class="literal">VOLATILE</code>, so that calls to it cannot be optimized away. + Even a function with no side-effects needs to be labeled + <code class="literal">VOLATILE</code> if its value can change within a single query; + some examples are <code class="literal">random()</code>, <code class="literal">currval()</code>, + <code class="literal">timeofday()</code>. + </p><p> + Another important example is that the <code class="function">current_timestamp</code> + family of functions qualify as <code class="literal">STABLE</code>, since their values do + not change within a transaction. + </p><p> + There is relatively little difference between <code class="literal">STABLE</code> and + <code class="literal">IMMUTABLE</code> 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 <code class="literal">IMMUTABLE</code> 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 + <span class="application">PL/pgSQL</span>). + </p><p> + 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 + <code class="literal">VOLATILE</code> function will see such changes, a <code class="literal">STABLE</code> + or <code class="literal">IMMUTABLE</code> function will not. This behavior is implemented + using the snapshotting behavior of MVCC (see <a class="xref" href="mvcc.html" title="Chapter 13. Concurrency Control">Chapter 13</a>): + <code class="literal">STABLE</code> and <code class="literal">IMMUTABLE</code> functions use a snapshot + established as of the start of the calling query, whereas + <code class="literal">VOLATILE</code> functions obtain a fresh snapshot at the start of + each query they execute. + </p><div class="note"><h3 class="title">Note</h3><p> + 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. + </p></div><p> + Because of this snapshotting behavior, + a function containing only <code class="command">SELECT</code> commands can safely be + marked <code class="literal">STABLE</code>, even if it selects from tables that might be + undergoing modifications by concurrent queries. + <span class="productname">PostgreSQL</span> will execute all commands of a + <code class="literal">STABLE</code> function using the snapshot established for the + calling query, and so it will see a fixed view of the database throughout + that query. + </p><p> + The same snapshotting behavior is used for <code class="command">SELECT</code> commands + within <code class="literal">IMMUTABLE</code> functions. It is generally unwise to select + from database tables within an <code class="literal">IMMUTABLE</code> function at all, + since the immutability will be broken if the table contents ever change. + However, <span class="productname">PostgreSQL</span> does not enforce that you + do not do that. + </p><p> + A common error is to label a function <code class="literal">IMMUTABLE</code> when its + results depend on a configuration parameter. For example, a function + that manipulates timestamps might well have results that depend on the + <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> setting. For safety, such functions should + be labeled <code class="literal">STABLE</code> instead. + </p><div class="note"><h3 class="title">Note</h3><p> + <span class="productname">PostgreSQL</span> requires that <code class="literal">STABLE</code> + and <code class="literal">IMMUTABLE</code> functions contain no SQL commands other + than <code class="command">SELECT</code> to prevent data modification. + (This is not a completely bulletproof test, since such functions could + still call <code class="literal">VOLATILE</code> functions that modify the database. + If you do that, you will find that the <code class="literal">STABLE</code> or + <code class="literal">IMMUTABLE</code> function does not notice the database changes + applied by the called function, since they are hidden from its snapshot.) + </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="xfunc-overload.html" title="38.6. Function Overloading">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="extend.html" title="Chapter 38. Extending SQL">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="xfunc-pl.html" title="38.8. Procedural Language Functions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">38.6. Function Overloading </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 38.8. Procedural Language Functions</td></tr></table></div></body></html>
\ No newline at end of file |