summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/xfunc-volatility.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:19:15 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:19:15 +0000
commit6eb9c5a5657d1fe77b55cc261450f3538d35a94d (patch)
tree657d8194422a5daccecfd42d654b8a245ef7b4c8 /doc/src/sgml/html/xfunc-volatility.html
parentInitial commit. (diff)
downloadpostgresql-13-upstream.tar.xz
postgresql-13-upstream.zip
Adding upstream version 13.4.upstream/13.4upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/xfunc-volatility.html')
-rw-r--r--doc/src/sgml/html/xfunc-volatility.html107
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..336098e
--- /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>37.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 V1.79.1" /><link rel="prev" href="xfunc-overload.html" title="37.6. Function Overloading" /><link rel="next" href="xfunc-pl.html" title="37.8. Procedural Language Functions" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">37.7. Function Volatility Categories</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="xfunc-overload.html" title="37.6. Function Overloading">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="extend.html" title="Chapter 37. Extending SQL">Up</a></td><th width="60%" align="center">Chapter 37. Extending <acronym xmlns="http://www.w3.org/1999/xhtml" class="acronym">SQL</acronym></th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="xfunc-pl.html" title="37.8. Procedural Language Functions">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="XFUNC-VOLATILITY"><div class="titlepage"><div><div><h2 class="title" style="clear: both">37.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="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></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 xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="xfunc-overload.html" title="37.6. Function Overloading">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="extend.html" title="Chapter 37. Extending SQL">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="xfunc-pl.html" title="37.8. Procedural Language Functions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">37.6. Function Overloading </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 37.8. Procedural Language Functions</td></tr></table></div></body></html> \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-12 07:57:33 +0000