From 6808f1b1de0ebcd4af558ba84c3226b2027f55ea Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 11 Jun 2007 01:16:30 +0000 Subject: Support UPDATE/DELETE WHERE CURRENT OF cursor_name, per SQL standard. Along the way, allow FOR UPDATE in non-WITH-HOLD cursors; there may once have been a reason to disallow that, but it seems to work now, and it's really rather necessary if you want to select a row via a cursor and then update it in a concurrent-safe fashion. Original patch by Arul Shaji, rather heavily editorialized by Tom Lane. --- doc/src/sgml/ref/declare.sgml | 149 +++++++++++++++++++----------------------- doc/src/sgml/ref/delete.sgml | 32 +++++++-- doc/src/sgml/ref/update.sgml | 27 +++++++- 3 files changed, 120 insertions(+), 88 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/declare.sgml b/doc/src/sgml/ref/declare.sgml index 4afe2d03b25..f823cf77bbe 100644 --- a/doc/src/sgml/ref/declare.sgml +++ b/doc/src/sgml/ref/declare.sgml @@ -1,5 +1,5 @@ @@ -27,7 +27,6 @@ PostgreSQL documentation DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR query - [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ] @@ -37,50 +36,10 @@ DECLARE name [ BINARY ] [ INSENSITI DECLARE allows a user to create cursors, which can be used to retrieve - a small number of rows at a time out of a larger query. Cursors can - return data either in text or in binary format using + a small number of rows at a time out of a larger query. + After the cursor is created, rows are fetched from it using . - - - Normal cursors return data in text format, the same as a - SELECT would produce. Since data is stored natively in - binary format, the system must do a conversion to produce the text - format. Once the information comes back in text form, the client - application might need to convert it to a binary format to manipulate - it. In addition, data in the text format is often larger in size - than in the binary format. Binary cursors return the data in a - binary representation that might be more easily manipulated. - Nevertheless, if you intend to display the data as text anyway, - retrieving it in text form will - save you some effort on the client side. - - - - As an example, if a query returns a value of one from an integer column, - you would get a string of 1 with a default cursor - whereas with a binary cursor you would get - a 4-byte field containing the internal representation of the value - (in big-endian byte order). - - - - Binary cursors should be used carefully. Many applications, - including psql, are not prepared to - handle binary cursors and expect data to come back in the text - format. - - - - - When the client application uses the extended query protocol - to issue a FETCH command, the Bind protocol message - specifies whether data is to be retrieved in text or binary format. - This choice overrides the way that the cursor is defined. The concept - of a binary cursor as such is thus obsolete when using extended query - protocol — any cursor can be treated as either text or binary. - - @@ -110,10 +69,10 @@ DECLARE name [ BINARY ] [ INSENSITI Indicates that data retrieved from the cursor should be - unaffected by updates to the tables underlying the cursor while - the cursor exists. In PostgreSQL, - all cursors are insensitive; this key word currently has no - effect and is present for compatibility with the SQL standard. + unaffected by updates to the table(s) underlying the cursor that occur + after the cursor is created. In PostgreSQL, + this is the default behavior; so this key word has no + effect and is only accepted for compatibility with the SQL standard. @@ -163,34 +122,6 @@ DECLARE name [ BINARY ] [ INSENSITI - - - FOR READ ONLY - FOR UPDATE - - - FOR READ ONLY indicates that the cursor will - be used in a read-only mode. FOR UPDATE - indicates that the cursor will be used to update tables. Since - cursor updates are not currently supported in - PostgreSQL, specifying FOR - UPDATE will cause an error message and specifying - FOR READ ONLY has no effect. - - - - - - column - - - Column(s) to be updated by the cursor. Since cursor updates are - not currently supported in - PostgreSQL, the FOR - UPDATE clause provokes an error message. - - - @@ -203,6 +134,38 @@ DECLARE name [ BINARY ] [ INSENSITI Notes + + Normal cursors return data in text format, the same as a + SELECT would produce. The BINARY option + specifies that the cursor should return data in binary format. + This reduces conversion effort for both the server and client, + at the cost of more programmer effort to deal with platform-dependent + binary data formats. + As an example, if a query returns a value of one from an integer column, + you would get a string of 1 with a default cursor, + whereas with a binary cursor you would get + a 4-byte field containing the internal representation of the value + (in big-endian byte order). + + + + Binary cursors should be used carefully. Many applications, + including psql, are not prepared to + handle binary cursors and expect data to come back in the text + format. + + + + + When the client application uses the extended query protocol + to issue a FETCH command, the Bind protocol message + specifies whether data is to be retrieved in text or binary format. + This choice overrides the way that the cursor is defined. The concept + of a binary cursor as such is thus obsolete when using extended query + protocol — any cursor can be treated as either text or binary. + + + Unless WITH HOLD is specified, the cursor created by this command can only be used within the current @@ -232,6 +195,11 @@ DECLARE name [ BINARY ] [ INSENSITI transactions. + + WITH HOLD may not be specified when the query + includes FOR UPDATE or FOR SHARE. + + The SCROLL option should be specified when defining a cursor that will be used to fetch backwards. This is required by @@ -245,6 +213,23 @@ DECLARE name [ BINARY ] [ INSENSITI specified, then backward fetches are disallowed in any case. + + If the cursor's query includes FOR UPDATE or FOR + SHARE, then returned rows are locked at the time they are first + fetched, in the same way as for a regular + command with + 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. + + The SQL standard only makes provisions for cursors in embedded SQL. The PostgreSQL @@ -280,14 +265,16 @@ DECLARE liahona CURSOR FOR SELECT * FROM films; Compatibility - The SQL standard allows cursors only in embedded - SQL and in modules. PostgreSQL - permits cursors to be used interactively. + The SQL standard specifies that by default, cursors are sensitive to + concurrent updates of the underlying data. In + PostgreSQL, cursors are insensitive by default, + and can be made sensitive by specifying FOR UPDATE. - The SQL standard allows cursors to update table data. All - PostgreSQL cursors are read only. + The SQL standard allows cursors only in embedded + SQL and in modules. PostgreSQL + permits cursors to be used interactively. diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml index f57c56ecf7e..b5c7d97f524 100644 --- a/doc/src/sgml/ref/delete.sgml +++ b/doc/src/sgml/ref/delete.sgml @@ -1,5 +1,5 @@ @@ -22,7 +22,7 @@ PostgreSQL documentation DELETE FROM [ ONLY ] table [ [ AS ] alias ] [ USING usinglist ] - [ WHERE condition ] + [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING * | output_expression [ AS output_name ] [, ...] ] @@ -134,9 +134,23 @@ DELETE FROM [ ONLY ] table [ [ AS ] condition - An expression returning a value of type - boolean, which determines the rows that are to be - deleted. + An expression that returns a value of type boolean. + Only rows for which this expression returns true + will be deleted. + + + + + + cursor_name + + + 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) + query on the DELETE's target table. + Note that WHERE CURRENT OF cannot be + specified together with a boolean condition. @@ -236,6 +250,14 @@ DELETE FROM films; Delete completed tasks, returning full details of the deleted rows: DELETE FROM tasks WHERE status = 'DONE' RETURNING *; + + + + + Delete the row of tasks on which the cursor + 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 df0bec9a1d6..2c6a480c4a5 100644 --- a/doc/src/sgml/ref/update.sgml +++ b/doc/src/sgml/ref/update.sgml @@ -1,5 +1,5 @@ @@ -24,7 +24,7 @@ UPDATE [ ONLY ] table [ [ AS ] column = { expression | DEFAULT } | ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...] [ FROM fromlist ] - [ WHERE condition ] + [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING * | output_expression [ AS output_name ] [, ...] ] @@ -160,6 +160,20 @@ UPDATE [ ONLY ] table [ [ AS ] + + cursor_name + + + 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) + query on the UPDATE's target table. + Note that WHERE CURRENT OF cannot be + specified together with a boolean condition. + + + + output_expression @@ -309,6 +323,15 @@ UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003'; COMMIT; + + + Change the kind column of the table + films in the row on which the cursor + c_films is currently positioned: + +UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films; + + -- cgit v1.2.3