Home • Explore • Blog • CI
☰



postgresql
4th chunk of `doc/src/sgml/ref/declare.sgml`
5861d139ba75cbd36df16fcf0480ef50e75e45ea351587760000000100000a7b
 UPDATE</literal> or <literal>FOR SHARE</literal>; therefore
    <literal>SCROLL</literal> may not be specified in this case.
   </para>

   <caution>
    <para>
     Scrollable cursors may give unexpected
     results if they invoke any volatile functions (see <xref
     linkend="xfunc-volatility"/>).  When a previously fetched row is
     re-fetched, the functions might be re-executed, perhaps leading to
     results different from the first time.  It's best to
     specify <literal>NO SCROLL</literal> for a query involving volatile
     functions.  If that is not practical, one workaround
     is to declare the cursor <literal>SCROLL WITH HOLD</literal> and commit the
     transaction before reading any rows from it.  This will force the
     entire output of the cursor to be materialized in temporary storage,
     so that volatile functions are executed exactly once for each row.
    </para>
   </caution>

   <para>
    If the cursor's query includes <literal>FOR UPDATE</literal> or <literal>FOR
    SHARE</literal>, then returned rows are locked at the time they are first
    fetched, in the same way as for a regular
    <link linkend="sql-select"><command>SELECT</command></link> command with
    these options.
    In addition, the returned rows will be the most up-to-date versions.
   </para>

   <caution>
    <para>
     It is generally recommended to use <literal>FOR UPDATE</literal> if the cursor
     is intended to be used with <command>UPDATE ... WHERE CURRENT OF</command> or
     <command>DELETE ... WHERE CURRENT OF</command>.  Using <literal>FOR UPDATE</literal>
     prevents other sessions from changing the rows between the time they are
     fetched and the time they are updated.  Without <literal>FOR UPDATE</literal>,
     a subsequent <literal>WHERE CURRENT OF</literal> command will have no effect if
     the row was changed since the cursor was created.
    </para>

    <para>
     Another reason to use <literal>FOR UPDATE</literal> is that without it, a
     subsequent <literal>WHERE CURRENT OF</literal> might fail if the cursor query
     does not meet the SQL standard's rules for being <quote>simply
     updatable</quote> (in particular, the cursor must reference just one table
     and not use grouping or <literal>ORDER BY</literal>).  Cursors
     that are not simply updatable might work, or might not, depending on plan
     choice details; so in the worst case, an application might work in testing
     and then fail in production.  If <literal>FOR UPDATE</literal> is
     specified, the cursor is guaranteed to be updatable.
    </para>

    <para>
     The main reason not to use <literal>FOR UPDATE</literal> with
Title: DECLARE Command Cautions: Volatile Functions, Locking, and FOR UPDATE/SHARE
Summary
This section further explains cautions related to the DECLARE command, expanding on the previous warning regarding volatile functions and scrollable cursors. It details that rows returned by a cursor with FOR UPDATE or FOR SHARE are locked when first fetched, ensuring they are the most up-to-date versions. It emphasizes the importance of using FOR UPDATE when the cursor is intended for UPDATE or DELETE WHERE CURRENT OF commands, preventing other sessions from changing rows between fetch and update. Finally, it highlights that without FOR UPDATE, WHERE CURRENT OF might fail if the cursor query isn't 'simply updatable', advising FOR UPDATE to guarantee updatability.