summaryrefslogtreecommitdiff
path: root/doc/src/sgml
diff options
context:
space:
mode:
Diffstat (limited to 'doc/src/sgml')
-rw-r--r--doc/src/sgml/plpgsql.sgml9
-rw-r--r--doc/src/sgml/ref/declare.sgml51
-rw-r--r--doc/src/sgml/ref/delete.sgml15
-rw-r--r--doc/src/sgml/ref/update.sgml11
4 files changed, 60 insertions, 26 deletions
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 2a0894bbf27..b8dfe01f5cd 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.135 2008/10/28 22:02:05 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.136 2008/11/16 17:34:28 tgl Exp $ -->
<chapter id="plpgsql">
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -2674,9 +2674,10 @@ DELETE FROM <replaceable>table</replaceable> WHERE CURRENT OF <replaceable>curso
<para>
When a cursor is positioned on a table row, that row can be updated
- or deleted using the cursor to identify the row. Note that this
- only works for simple (non-join, non-grouping) cursor queries.
- For additional information see the
+ or deleted using the cursor to identify the row. There are
+ restrictions on what the cursor's query can be (in particular,
+ no grouping) and it's best to use <literal>FOR UPDATE</> in the
+ cursor. For additional information see the
<xref linkend="sql-declare" endterm="sql-declare-title">
reference page.
</para>
diff --git a/doc/src/sgml/ref/declare.sgml b/doc/src/sgml/ref/declare.sgml
index ea9b080816f..373ef39be79 100644
--- a/doc/src/sgml/ref/declare.sgml
+++ b/doc/src/sgml/ref/declare.sgml
@@ -1,5 +1,5 @@
<!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/declare.sgml,v 1.44 2008/11/14 10:22:46 petere Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/declare.sgml,v 1.45 2008/11/16 17:34:28 tgl Exp $
PostgreSQL documentation
-->
@@ -214,6 +214,12 @@ DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ INSENSITI
</para>
<para>
+ Backward fetches are also disallowed when the query
+ includes <literal>FOR UPDATE</> or <literal>FOR SHARE</>; therefore
+ <literal>SCROLL</literal> may not be specified in this case.
+ </para>
+
+ <para>
If the cursor's query includes <literal>FOR UPDATE</> or <literal>FOR
SHARE</>, then returned rows are locked at the time they are first
fetched, in the same way as for a regular
@@ -221,19 +227,40 @@ DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ INSENSITI
these options.
In addition, the returned rows will be the most up-to-date versions;
therefore these options provide the equivalent of what the SQL standard
- calls a <quote>sensitive cursor</>. It is often wise to use <literal>FOR
- UPDATE</> if the cursor is intended to be used with <command>UPDATE
- ... WHERE CURRENT OF</> or <command>DELETE ... WHERE CURRENT OF</>,
- since this will prevent other sessions from changing the rows between
- the time they are fetched and the time they are updated. Without
- <literal>FOR UPDATE</>, a subsequent <literal>WHERE CURRENT OF</> command
- will have no effect if the row was changed meanwhile.
+ calls a <quote>sensitive cursor</>. (Specifying <literal>INSENSITIVE</>
+ together with <literal>FOR UPDATE</> or <literal>FOR SHARE</> is an error.)
</para>
- <para>
- <literal>SCROLL</literal> may not be specified when the query
- includes <literal>FOR UPDATE</> or <literal>FOR SHARE</>.
- </para>
+ <caution>
+ <para>
+ It is generally recommended to use <literal>FOR UPDATE</> if the cursor
+ is intended to be used with <command>UPDATE ... WHERE CURRENT OF</> or
+ <command>DELETE ... WHERE CURRENT OF</>. Using <literal>FOR UPDATE</>
+ prevents other sessions from changing the rows between the time they are
+ fetched and the time they are updated. Without <literal>FOR UPDATE</>,
+ a subsequent <literal>WHERE CURRENT OF</> command will have no effect if
+ the row was changed since the cursor was created.
+ </para>
+
+ <para>
+ Another reason to use <literal>FOR UPDATE</> is that without it, a
+ subsequent <literal>WHERE CURRENT OF</> might fail if the cursor query
+ does not meet the SQL standard's rules for being <quote>simply
+ updatable</> (in particular, the cursor must reference just one table
+ and not use grouping or <literal>ORDER BY</>). 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.
+ </para>
+
+ <para>
+ The main reason not to use <literal>FOR UPDATE</> with <literal>WHERE
+ CURRENT OF</> is if you need the cursor to be scrollable, or to be
+ insensitive to the subsequent updates (that is, continue to show the old
+ data). If this is a requirement, pay close heed to the caveats shown
+ above.
+ </para>
+ </caution>
<para>
The SQL standard only makes provisions for cursors in embedded
diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index 6c9fdac5033..62e4555614f 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -1,5 +1,5 @@
<!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/delete.sgml,v 1.34 2008/11/14 10:22:46 petere Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/delete.sgml,v 1.35 2008/11/16 17:34:28 tgl Exp $
PostgreSQL documentation
-->
@@ -148,10 +148,13 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ]
<para>
The name of the cursor to use in a <literal>WHERE CURRENT OF</>
condition. The row to be deleted is the one most recently fetched
- from this cursor. The cursor must be a simple (non-join, non-aggregate)
+ from this cursor. The cursor must be a non-grouping
query on the <command>DELETE</>'s target table.
Note that <literal>WHERE CURRENT OF</> cannot be
- specified together with a Boolean condition.
+ specified together with a Boolean condition. See
+ <xref linkend="sql-declare" endterm="sql-declare-title">
+ for more information about using cursors with
+ <literal>WHERE CURRENT OF</>.
</para>
</listitem>
</varlistentry>
@@ -244,14 +247,14 @@ DELETE FROM films WHERE kind &lt;&gt; 'Musical';
Clear the table <literal>films</literal>:
<programlisting>
DELETE FROM films;
-</programlisting>
+</programlisting>
</para>
<para>
Delete completed tasks, returning full details of the deleted rows:
<programlisting>
DELETE FROM tasks WHERE status = 'DONE' RETURNING *;
-</programlisting>
+</programlisting>
</para>
<para>
@@ -259,7 +262,7 @@ DELETE FROM tasks WHERE status = 'DONE' RETURNING *;
<literal>c_tasks</> is currently positioned:
<programlisting>
DELETE FROM tasks WHERE CURRENT OF c_tasks;
-</programlisting>
+</programlisting>
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index c1996d2d931..2464bf16f93 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -1,5 +1,5 @@
<!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.47 2008/11/14 10:22:47 petere Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.48 2008/11/16 17:34:28 tgl Exp $
PostgreSQL documentation
-->
@@ -167,10 +167,13 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep
<para>
The name of the cursor to use in a <literal>WHERE CURRENT OF</>
condition. The row to be updated is the one most recently fetched
- from this cursor. The cursor must be a simple (non-join, non-aggregate)
+ from this cursor. The cursor must be a non-grouping
query on the <command>UPDATE</>'s target table.
Note that <literal>WHERE CURRENT OF</> cannot be
- specified together with a Boolean condition.
+ specified together with a Boolean condition. See
+ <xref linkend="sql-declare" endterm="sql-declare-title">
+ for more information about using cursors with
+ <literal>WHERE CURRENT OF</>.
</para>
</listitem>
</varlistentry>
@@ -331,7 +334,7 @@ COMMIT;
<literal>c_films</> is currently positioned:
<programlisting>
UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;
-</programlisting>
+</programlisting>
</para>
</refsect1>
generated by cgit v1.2.3 (git 2.46.0) at 2025-10-27 15:28:17 +0000