From dd13ad9d39a1ba41cf329b6fe408b49be57c7b88 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Wed, 7 Apr 2021 07:49:27 +0200 Subject: Fix use of cursor sensitivity terminology Documentation and comments in code and tests have been using the terms sensitive/insensitive cursor incorrectly relative to the SQL standard. (Cursor sensitivity is only relevant for changes made in the same transaction as the cursor, not for concurrent changes in other sessions.) Moreover, some of the behavior of PostgreSQL is incorrect according to the SQL standard, confusing the issue further. (WHERE CURRENT OF changes are not visible in insensitive cursors, but they should be.) This change corrects the terminology and removes the claim that sensitive cursors are supported. It also adds a test case that checks the insensitive behavior in a "correct" way, using a change command not using WHERE CURRENT OF. Finally, it adds the ASENSITIVE cursor option to select the default asensitive behavior, per SQL standard. There are no changes to cursor behavior in this patch. Discussion: https://www.postgresql.org/message-id/flat/96ee8b30-9889-9e1b-b053-90e10c050e85%40enterprisedb.com --- doc/src/sgml/ecpg.sgml | 4 ++-- doc/src/sgml/ref/declare.sgml | 49 +++++++++++++++++++++++++------------------ 2 files changed, 31 insertions(+), 22 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ecpg.sgml b/doc/src/sgml/ecpg.sgml index e2e757668a7..56f5d9b5db1 100644 --- a/doc/src/sgml/ecpg.sgml +++ b/doc/src/sgml/ecpg.sgml @@ -6792,8 +6792,8 @@ EXEC SQL DEALLOCATE DESCRIPTOR mydesc; -DECLARE cursor_name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR prepared_name -DECLARE cursor_name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR query +DECLARE cursor_name [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR prepared_name +DECLARE cursor_name [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR query diff --git a/doc/src/sgml/ref/declare.sgml b/doc/src/sgml/ref/declare.sgml index 2152134635e..8a2b8cc8929 100644 --- a/doc/src/sgml/ref/declare.sgml +++ b/doc/src/sgml/ref/declare.sgml @@ -26,7 +26,7 @@ PostgreSQL documentation -DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] +DECLARE name [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR query @@ -75,14 +75,25 @@ DECLARE name [ BINARY ] [ INSENSITI + ASENSITIVE INSENSITIVE - Indicates that data retrieved from the cursor should be - 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. + Cursor sensitivity determines whether changes to the data underlying the + cursor, done in the same transaction, after the cursor has been + declared, are visible in the cursor. INSENSITIVE + means they are not visible, ASENSITIVE means the + behavior is implementation-dependent. A third behavior, + SENSITIVE, meaning that such changes are visible in + the cursor, is not available in PostgreSQL. + In PostgreSQL, all cursors are insensitive; + so these key words have no effect and are only accepted for + compatibility with the SQL standard. + + + + Specifying INSENSITIVE together with FOR + UPDATE or FOR SHARE is an error. @@ -133,7 +144,7 @@ DECLARE name [ BINARY ] [ INSENSITI - The key words BINARY, + The key words ASENSITIVE, BINARY, INSENSITIVE, and SCROLL can appear in any order. @@ -246,10 +257,7 @@ DECLARE name [ BINARY ] [ INSENSITI fetched, in the same way as for a regular SELECT 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. (Specifying INSENSITIVE - together with FOR UPDATE or FOR SHARE is an error.) + In addition, the returned rows will be the most up-to-date versions. @@ -278,7 +286,7 @@ DECLARE name [ BINARY ] [ INSENSITI 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 + isolated from concurrent updates (that is, continue to show the old data). If this is a requirement, pay close heed to the caveats shown above. @@ -318,20 +326,21 @@ DECLARE liahona CURSOR FOR SELECT * FROM films; Compatibility - - The SQL standard says that it is implementation-dependent whether cursors - are sensitive to concurrent updates of the underlying data by default. In - PostgreSQL, cursors are insensitive by default, - and can be made sensitive by specifying FOR UPDATE. Other - products may work differently. - - The SQL standard allows cursors only in embedded SQL and in modules. PostgreSQL permits cursors to be used interactively. + + According to the SQL standard, changes made to insensitive cursors by + UPDATE ... WHERE CURRENT OF and DELETE + ... WHERE CURRENT OF statements are visibible in that same + cursor. PostgreSQL treats these statements like + all other data changing statements in that they are not visible in + insensitive cursors. + + Binary cursors are a PostgreSQL extension. -- cgit v1.2.3