Sophie: postgresql8.3-docs-8.3.6-2mdv2008.1 x86

postgresql8.3-docs-8.3.6-2mdv2008.1.x86_64.rpm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Function Volatility Categories</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.3.6 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Extending SQL"
HREF="extend.html"><LINK
REL="PREVIOUS"
TITLE="Function Overloading"
HREF="xfunc-overload.html"><LINK
REL="NEXT"
TITLE="Procedural Language Functions"
HREF="xfunc-pl.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2009-02-03T04:34:16"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.3.6 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="xfunc-overload.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="extend.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 34. Extending <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="extend.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="xfunc-pl.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="XFUNC-VOLATILITY"
>34.6. Function Volatility Categories</A
></H1
><A
NAME="AEN40421"
></A
><A
NAME="AEN40424"
></A
><A
NAME="AEN40426"
></A
><A
NAME="AEN40428"
></A
><P
>    Every function has a <I
CLASS="FIRSTTERM"
>volatility</I
> classification, with
    the possibilities being <TT
CLASS="LITERAL"
>VOLATILE</TT
>, <TT
CLASS="LITERAL"
>STABLE</TT
>, or
    <TT
CLASS="LITERAL"
>IMMUTABLE</TT
>.  <TT
CLASS="LITERAL"
>VOLATILE</TT
> is the default if the
    <A
HREF="sql-createfunction.html"
><I
>CREATE FUNCTION</I
></A
>
    command does not specify a category.  The volatility category is a
    promise to the optimizer about the behavior of the function:

   <P
></P
></P><UL
><LI
><P
>      A <TT
CLASS="LITERAL"
>VOLATILE</TT
> 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
><P
>      A <TT
CLASS="LITERAL"
>STABLE</TT
> 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 <TT
CLASS="LITERAL"
>VOLATILE</TT
> function in an
      index scan condition.)
     </P
></LI
><LI
><P
>      An <TT
CLASS="LITERAL"
>IMMUTABLE</TT
> 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
      <TT
CLASS="LITERAL"
>SELECT ... WHERE x = 2 + 2</TT
> can be simplified on sight to
      <TT
CLASS="LITERAL"
>SELECT ... WHERE x = 4</TT
>, because the function underlying
      the integer addition operator is marked <TT
CLASS="LITERAL"
>IMMUTABLE</TT
>.
     </P
></LI
></UL
><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"
><I
CLASS="EMPHASIS"
>must</I
></SPAN
> be labeled
    <TT
CLASS="LITERAL"
>VOLATILE</TT
>, so that calls to it cannot be optimized away.
    Even a function with no side-effects needs to be labeled
    <TT
CLASS="LITERAL"
>VOLATILE</TT
> if its value can change within a single query;
    some examples are <TT
CLASS="LITERAL"
>random()</TT
>, <TT
CLASS="LITERAL"
>currval()</TT
>,
    <TT
CLASS="LITERAL"
>timeofday()</TT
>.
   </P
><P
>    There is relatively little difference between <TT
CLASS="LITERAL"
>STABLE</TT
> and
    <TT
CLASS="LITERAL"
>IMMUTABLE</TT
> 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 <TT
CLASS="LITERAL"
>IMMUTABLE</TT
> 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
>    Because of the snapshotting behavior of MVCC (see <A
HREF="mvcc.html"
>Chapter 13</A
>)
    a function containing only <TT
CLASS="COMMAND"
>SELECT</TT
> commands can safely be
    marked <TT
CLASS="LITERAL"
>STABLE</TT
>, even if it selects from tables that might be
    undergoing modifications by concurrent queries.
    <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> will execute a <TT
CLASS="LITERAL"
>STABLE</TT
>
    function using the snapshot established for the calling query, and so it
    will see a fixed view of the database throughout that query.
    Also note
    that the <CODE
CLASS="FUNCTION"
>current_timestamp</CODE
> family of functions qualify
    as stable, since their values do not change within a transaction.
   </P
><P
>    The same snapshotting behavior is used for <TT
CLASS="COMMAND"
>SELECT</TT
> commands
    within <TT
CLASS="LITERAL"
>IMMUTABLE</TT
> functions.  It is generally unwise to select
    from database tables within an <TT
CLASS="LITERAL"
>IMMUTABLE</TT
> 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 <TT
CLASS="LITERAL"
>IMMUTABLE</TT
> when its
    results depend on a configuration parameter.  For example, a function
    that manipulates timestamps might well have results that depend on the
    <A
HREF="runtime-config-client.html#GUC-TIMEZONE"
>timezone</A
> setting.  For safety, such functions should
    be labeled <TT
CLASS="LITERAL"
>STABLE</TT
> instead.
   </P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>     Before <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> release 8.0, the requirement
     that <TT
CLASS="LITERAL"
>STABLE</TT
> and <TT
CLASS="LITERAL"
>IMMUTABLE</TT
> functions 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 <TT
CLASS="COMMAND"
>SELECT</TT
>.
     (This is not a completely bulletproof test, since such functions could
     still call <TT
CLASS="LITERAL"
>VOLATILE</TT
> functions that modify the database.
     If you do that, you will find that the <TT
CLASS="LITERAL"
>STABLE</TT
> or
     <TT
CLASS="LITERAL"
>IMMUTABLE</TT
> function does not notice the database changes
     applied by the called function.)
    </P
></BLOCKQUOTE
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="xfunc-overload.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="xfunc-pl.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Function Overloading</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="extend.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Procedural Language Functions</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>