From 18004101acb98d8fefe7dda1c9f010cceff83b6d Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 16 Nov 2008 17:34:28 +0000 Subject: Modify UPDATE/DELETE WHERE CURRENT OF to use the FOR UPDATE infrastructure to locate the target row, if the cursor was declared with FOR UPDATE or FOR SHARE. This approach is more flexible and reliable than digging through the plan tree; for instance it can cope with join cursors. But we still provide the old code for use with non-FOR-UPDATE cursors. Per gripe from Robert Haas. --- doc/src/sgml/plpgsql.sgml | 9 ++++---- doc/src/sgml/ref/declare.sgml | 51 +++++++++++++++++++++++++++++++++---------- doc/src/sgml/ref/delete.sgml | 15 ++++++++----- doc/src/sgml/ref/update.sgml | 11 ++++++---- 4 files changed, 60 insertions(+), 26 deletions(-) (limited to 'doc/src') 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 @@ - + <application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language @@ -2674,9 +2674,10 @@ DELETE FROM table WHERE CURRENT OF curso 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 FOR UPDATE in the + cursor. For additional information see the reference page. 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 @@ @@ -213,6 +213,12 @@ DECLARE name [ BINARY ] [ INSENSITI specified, then backward fetches are disallowed in any case. + + Backward fetches are also disallowed when the query + includes FOR UPDATE or FOR SHARE; therefore + SCROLL may not be specified in this case. + + If the cursor's query includes FOR UPDATE or FOR SHARE, then returned rows are locked at the time they are first @@ -221,19 +227,40 @@ DECLARE name [ 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 sensitive cursor. It is often wise to use FOR - UPDATE if the cursor is intended to be used with UPDATE - ... WHERE CURRENT OF or 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 - FOR UPDATE, a subsequent WHERE CURRENT OF command - will have no effect if the row was changed meanwhile. + calls a sensitive cursor. (Specifying INSENSITIVE + together with FOR UPDATE or FOR SHARE is an error.) - - SCROLL may not be specified when the query - includes FOR UPDATE or FOR SHARE. - + + + It is generally recommended to use FOR UPDATE if the cursor + is intended to be used with UPDATE ... WHERE CURRENT OF or + DELETE ... WHERE CURRENT OF. Using FOR UPDATE + prevents other sessions from changing the rows between the time they are + fetched and the time they are updated. Without FOR UPDATE, + a subsequent WHERE CURRENT OF command will have no effect if + the row was changed since the cursor was created. + + + + Another reason to use FOR UPDATE is that without it, a + subsequent WHERE CURRENT OF might fail if the cursor query + does not meet the SQL standard's rules for being simply + updatable (in particular, the cursor must reference just one table + and not use grouping or 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. + + + + The main reason not to use FOR UPDATE with 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. + + 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 @@ @@ -148,10 +148,13 @@ DELETE FROM [ ONLY ] table [ [ AS ] The name of the cursor to use in a 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 DELETE's target table. Note that WHERE CURRENT OF cannot be - specified together with a Boolean condition. + specified together with a Boolean condition. See + + for more information about using cursors with + WHERE CURRENT OF. @@ -244,14 +247,14 @@ DELETE FROM films WHERE kind <> 'Musical'; Clear the table films: DELETE FROM films; - + Delete completed tasks, returning full details of the deleted rows: DELETE FROM tasks WHERE status = 'DONE' RETURNING *; - + @@ -259,7 +262,7 @@ DELETE FROM tasks WHERE status = 'DONE' RETURNING *; c_tasks is currently positioned: DELETE FROM tasks WHERE CURRENT OF c_tasks; - + 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 @@ @@ -167,10 +167,13 @@ UPDATE [ ONLY ] table [ [ AS ] The name of the cursor to use in a 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 UPDATE's target table. Note that WHERE CURRENT OF cannot be - specified together with a Boolean condition. + specified together with a Boolean condition. See + + for more information about using cursors with + WHERE CURRENT OF. @@ -331,7 +334,7 @@ COMMIT; c_films is currently positioned: UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films; - + -- cgit v1.2.3