cursor sensitivity misunderstanding
I think our documentation is mistaken about what it means for a cursor
to be "sensitive" or "insensitive".
The definition in SQL:2016 is:
A change to SQL-data is said to be independent of a cursor CR if and
only if it is not made by an <update statement: positioned> or a
<delete statement: positioned> that is positioned on CR.
A change to SQL-data is said to be significant to CR if and only
if it is independent of CR, and, had it been committed before CR
was opened, would have caused the sequence of rows in the result
set descriptor of CR to be different in any respect.
...
If a cursor is open, and the SQL-transaction in which the cursor
was opened makes a significant change to SQL-data, then whether
that change is visible through that cursor before it is closed is
determined as follows:
- If the cursor is insensitive, then significant changes are not
visible.
- If the cursor is sensitive, then significant changes are
visible.
- If the cursor is asensitive, then the visibility of significant
changes is implementation-dependent.
So I think a test case would be:
create table t1 (a int);
insert into t1 values (1);
begin;
declare c1 cursor for select * from t1;
insert into t1 values (2);
fetch next from c1; -- returns 1
fetch next from c1; -- ???
commit;
With a sensitive cursor, the second fetch would return 2, with an
insensitive cursor, the second fetch returns nothing. The latter
happens with PostgreSQL.
The DECLARE man page describes it thus:
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.
Which is not wrong, but it omits that this is only relevant for
changes in the same transaction.
Later in the DECLARE man page, it says:
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 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".
And that seems definitely wrong. Declaring c1 in the above example as
FOR UPDATE or FOR SHARE does not change the result. I think this
discussion is mixing up the concept of cursor sensitivity with
transaction isolation.
Thoughts?
On Thu, Feb 18, 2021 at 9:00 AM Peter Eisentraut <
peter.eisentraut@enterprisedb.com> wrote:
And that seems definitely wrong. Declaring c1 in the above example as
FOR UPDATE or FOR SHARE does not change the result. I think this
discussion is mixing up the concept of cursor sensitivity with
transaction isolation.Thoughts?
This came up on Discord in the context of pl/pgsql last month - never
really came to a conclusion.
"
open curs FOR SELECT * FROM Res FOR UPDATE;
LOOP
FETCH curs into record;
EXIT WHEN NOT FOUND;
INSERT INTO Res SELECT Type.Name
FROM Type
WHERE Type.SupClass = record.Name;
END LOOP;
"
The posted question was: "this doesn't go over rows added during the loop
despite the FOR UPDATE"
The OP was doing a course based on Oracle and was confused regarding our
behavior. The documentation failed to help me provide a useful response,
so I'd agree there is something here that needs reworking if not outright
fixing.
David J.
On 18.02.21 17:11, David G. Johnston wrote:
The OP was doing a course based on Oracle and was confused regarding our
behavior. The documentation failed to help me provide a useful
response, so I'd agree there is something here that needs reworking if
not outright fixing.
According to the piece of the standard that I posted, the sensitivity
behavior here is implementation-dependent (not even -defined), so both
implementations are correct.
But the poster was apparently also confused by the same piece of
documentation.
If you consider the implementation of MVCC in PostgreSQL, then the
current behavior makes sense. I suspect that this consideration was
much more interesting for older system with locking-based concurrency
and where "read uncommitted" was a real thing. With the current system,
insensitive cursors are essentially free and sensitive cursors would
require quite a bit of effort to implement.
On 18.02.21 19:14, Peter Eisentraut wrote:
On 18.02.21 17:11, David G. Johnston wrote:
The OP was doing a course based on Oracle and was confused regarding
our behavior. The documentation failed to help me provide a useful
response, so I'd agree there is something here that needs reworking if
not outright fixing.According to the piece of the standard that I posted, the sensitivity
behavior here is implementation-dependent (not even -defined), so both
implementations are correct.But the poster was apparently also confused by the same piece of
documentation.
I came up with the attached patch to sort this out a bit. It does not
change any cursor behavior. But the documentation now uses the terms
more correctly and explains the differences between SQL and the
PostgreSQL implementation better, I think.
Attachments:
0001-Fix-use-of-cursor-sensitivity-terminology.patchtext/plain; charset=UTF-8; name=0001-Fix-use-of-cursor-sensitivity-terminology.patch; x-mac-creator=0; x-mac-type=0Download
From 988206ec56a9def21b67f0cc871be3501e6af846 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Thu, 25 Feb 2021 16:25:57 +0100
Subject: [PATCH] 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 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 ++++++++++++++++-----------
src/backend/catalog/sql_features.txt | 2 +-
src/backend/parser/analyze.c | 19 +++++++----
src/backend/parser/gram.y | 7 ++--
src/bin/psql/tab-complete.c | 2 +-
src/include/nodes/parsenodes.h | 1 +
src/include/parser/kwlist.h | 1 +
src/test/regress/expected/portals.out | 37 +++++++++++++++++++-
src/test/regress/sql/portals.sql | 17 +++++++++-
10 files changed, 105 insertions(+), 34 deletions(-)
diff --git a/doc/src/sgml/ecpg.sgml b/doc/src/sgml/ecpg.sgml
index 9310a71166..b36c9624f6 100644
--- a/doc/src/sgml/ecpg.sgml
+++ b/doc/src/sgml/ecpg.sgml
@@ -6752,8 +6752,8 @@ <title>See Also</title>
<refsynopsisdiv>
<synopsis>
-DECLARE <replaceable class="parameter">cursor_name</replaceable> [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">prepared_name</replaceable>
-DECLARE <replaceable class="parameter">cursor_name</replaceable> [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">query</replaceable>
+DECLARE <replaceable class="parameter">cursor_name</replaceable> [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">prepared_name</replaceable>
+DECLARE <replaceable class="parameter">cursor_name</replaceable> [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">query</replaceable>
</synopsis>
</refsynopsisdiv>
diff --git a/doc/src/sgml/ref/declare.sgml b/doc/src/sgml/ref/declare.sgml
index 2152134635..8a2b8cc892 100644
--- a/doc/src/sgml/ref/declare.sgml
+++ b/doc/src/sgml/ref/declare.sgml
@@ -26,7 +26,7 @@
<refsynopsisdiv>
<synopsis>
-DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
+DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">query</replaceable>
</synopsis>
</refsynopsisdiv>
@@ -75,14 +75,25 @@ <title>Parameters</title>
</varlistentry>
<varlistentry>
+ <term><literal>ASENSITIVE</literal></term>
<term><literal>INSENSITIVE</literal></term>
<listitem>
<para>
- 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 <productname>PostgreSQL</productname>,
- 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. <literal>INSENSITIVE</literal>
+ means they are not visible, <literal>ASENSITIVE</literal> means the
+ behavior is implementation-dependent. A third behavior,
+ <literal>SENSITIVE</literal>, meaning that such changes are visible in
+ the cursor, is not available in <productname>PostgreSQL</productname>.
+ In <productname>PostgreSQL</productname>, all cursors are insensitive;
+ so these key words have no effect and are only accepted for
+ compatibility with the SQL standard.
+ </para>
+
+ <para>
+ Specifying <literal>INSENSITIVE</literal> together with <literal>FOR
+ UPDATE</literal> or <literal>FOR SHARE</literal> is an error.
</para>
</listitem>
</varlistentry>
@@ -133,7 +144,7 @@ <title>Parameters</title>
</variablelist>
<para>
- The key words <literal>BINARY</literal>,
+ The key words <literal>ASENSITIVE</literal>, <literal>BINARY</literal>,
<literal>INSENSITIVE</literal>, and <literal>SCROLL</literal> can
appear in any order.
</para>
@@ -246,10 +257,7 @@ <title>Notes</title>
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;
- therefore these options provide the equivalent of what the SQL standard
- calls a <quote>sensitive cursor</quote>. (Specifying <literal>INSENSITIVE</literal>
- together with <literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal> is an error.)
+ In addition, the returned rows will be the most up-to-date versions.
</para>
<caution>
@@ -278,7 +286,7 @@ <title>Notes</title>
<para>
The main reason not to use <literal>FOR UPDATE</literal> with <literal>WHERE
CURRENT OF</literal> 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.
</para>
@@ -318,20 +326,21 @@ <title>Examples</title>
<refsect1>
<title>Compatibility</title>
- <para>
- The SQL standard says that it is implementation-dependent whether cursors
- are sensitive to concurrent updates of the underlying data by default. In
- <productname>PostgreSQL</productname>, cursors are insensitive by default,
- and can be made sensitive by specifying <literal>FOR UPDATE</literal>. Other
- products may work differently.
- </para>
-
<para>
The SQL standard allows cursors only in embedded
<acronym>SQL</acronym> and in modules. <productname>PostgreSQL</productname>
permits cursors to be used interactively.
</para>
+ <para>
+ According to the SQL standard, changes made to insensitive cursors by
+ <literal>UPDATE ... WHERE CURRENT OF</literal> and <literal>DELETE
+ ... WHERE CURRENT OF</literal> statements are visibible in that same
+ cursor. <productname>PostgreSQL</productname> treats these statements like
+ all other data changing statements in that they are not visible in
+ insensitive cursors.
+ </para>
+
<para>
Binary cursors are a <productname>PostgreSQL</productname>
extension.
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index a24387c1e7..e2a1bfbfed 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -451,7 +451,7 @@ T211 Basic trigger capability 07 TRIGGER privilege YES
T211 Basic trigger capability 08 Multiple triggers for the same event are executed in the order in which they were created in the catalog NO intentionally omitted
T212 Enhanced trigger capability YES
T213 INSTEAD OF triggers YES
-T231 Sensitive cursors YES
+T231 Sensitive cursors NO
T241 START TRANSACTION statement YES
T251 SET TRANSACTION statement: LOCAL option NO
T261 Chained transactions YES
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 0f3a70c49a..0fb935bfb7 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -2679,14 +2679,21 @@ transformDeclareCursorStmt(ParseState *pstate, DeclareCursorStmt *stmt)
Query *result;
Query *query;
- /*
- * Don't allow both SCROLL and NO SCROLL to be specified
- */
if ((stmt->options & CURSOR_OPT_SCROLL) &&
(stmt->options & CURSOR_OPT_NO_SCROLL))
ereport(ERROR,
(errcode(ERRCODE_INVALID_CURSOR_DEFINITION),
- errmsg("cannot specify both SCROLL and NO SCROLL")));
+ /* translator: %s is a SQL keyword */
+ errmsg("cannot specify both %s and %s",
+ "SCROLL", "NO SCROLL")));
+
+ if ((stmt->options & CURSOR_OPT_ASENSITIVE) &&
+ (stmt->options & CURSOR_OPT_INSENSITIVE))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_CURSOR_DEFINITION),
+ /* translator: %s is a SQL keyword */
+ errmsg("cannot specify both %s and %s",
+ "ASENSITIVE", "INSENSITIVE")));
/* Transform contained query, not allowing SELECT INTO */
query = transformStmt(pstate, stmt->query);
@@ -2732,10 +2739,10 @@ transformDeclareCursorStmt(ParseState *pstate, DeclareCursorStmt *stmt)
/* FOR UPDATE and INSENSITIVE are not compatible */
if (query->rowMarks != NIL && (stmt->options & CURSOR_OPT_INSENSITIVE))
ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ (errcode(ERRCODE_INVALID_CURSOR_DEFINITION),
/*------
translator: %s is a SQL row locking clause such as FOR UPDATE */
- errmsg("DECLARE INSENSITIVE CURSOR ... %s is not supported",
+ errmsg("DECLARE INSENSITIVE CURSOR ... %s is not valid",
LCS_asString(((RowMarkClause *)
linitial(query->rowMarks))->strength)),
errdetail("Insensitive cursors must be READ ONLY.")));
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index dd72a9fc3c..0a7839bbd4 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -623,7 +623,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
/* ordinary key words in alphabetical order */
%token <keyword> ABORT_P ABSOLUTE_P ACCESS ACTION ADD_P ADMIN AFTER
AGGREGATE ALL ALSO ALTER ALWAYS ANALYSE ANALYZE AND ANY ARRAY AS ASC
- ASSERTION ASSIGNMENT ASYMMETRIC AT ATTACH ATTRIBUTE AUTHORIZATION
+ ASENSITIVE ASSERTION ASSIGNMENT ASYMMETRIC AT ATTACH ATTRIBUTE AUTHORIZATION
BACKWARD BEFORE BEGIN_P BETWEEN BIGINT BINARY BIT
BOOLEAN_P BOTH BREADTH BY
@@ -688,7 +688,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
ROUTINE ROUTINES ROW ROWS RULE
- SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
+ SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW
SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SQL_P STABLE STANDALONE_P
START STATEMENT STATISTICS STDIN STDOUT STORAGE STORED STRICT_P STRIP_P
@@ -11119,6 +11119,7 @@ cursor_options: /*EMPTY*/ { $$ = 0; }
| cursor_options NO SCROLL { $$ = $1 | CURSOR_OPT_NO_SCROLL; }
| cursor_options SCROLL { $$ = $1 | CURSOR_OPT_SCROLL; }
| cursor_options BINARY { $$ = $1 | CURSOR_OPT_BINARY; }
+ | cursor_options ASENSITIVE { $$ = $1 | CURSOR_OPT_ASENSITIVE; }
| cursor_options INSENSITIVE { $$ = $1 | CURSOR_OPT_INSENSITIVE; }
;
@@ -15258,6 +15259,7 @@ unreserved_keyword:
| ALSO
| ALTER
| ALWAYS
+ | ASENSITIVE
| ASSERTION
| ASSIGNMENT
| AT
@@ -15763,6 +15765,7 @@ bare_label_keyword:
| AND
| ANY
| ASC
+ | ASENSITIVE
| ASSERTION
| ASSIGNMENT
| ASYMMETRIC
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 9f0208ac49..9abdd36603 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3050,7 +3050,7 @@ psql_completion(const char *text, int start, int end)
* SCROLL, and CURSOR.
*/
else if (Matches("DECLARE", MatchAny))
- COMPLETE_WITH("BINARY", "INSENSITIVE", "SCROLL", "NO SCROLL",
+ COMPLETE_WITH("BINARY", "ASENSITIVE", "INSENSITIVE", "SCROLL", "NO SCROLL",
"CURSOR");
/*
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 236832a2ca..90e29fae8c 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2751,6 +2751,7 @@ typedef struct SecLabelStmt
#define CURSOR_OPT_SCROLL 0x0002 /* SCROLL explicitly given */
#define CURSOR_OPT_NO_SCROLL 0x0004 /* NO SCROLL explicitly given */
#define CURSOR_OPT_INSENSITIVE 0x0008 /* INSENSITIVE */
+#define CURSOR_OPT_ASENSITIVE 0x0200 /* ASENSITIVE */
#define CURSOR_OPT_HOLD 0x0010 /* WITH HOLD */
/* these planner-control flags do not correspond to any SQL grammar: */
#define CURSOR_OPT_FAST_PLAN 0x0020 /* prefer fast-start plan */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 28083aaac9..31dc65dbc0 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -44,6 +44,7 @@ PG_KEYWORD("any", ANY, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("array", ARRAY, RESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("as", AS, RESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("asc", ASC, RESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("asensitive", ASENSITIVE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("assertion", ASSERTION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("assignment", ASSIGNMENT, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("asymmetric", ASYMMETRIC, RESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/portals.out b/src/test/regress/expected/portals.out
index dc0d2ef7dd..42dc637fd4 100644
--- a/src/test/regress/expected/portals.out
+++ b/src/test/regress/expected/portals.out
@@ -1094,7 +1094,7 @@ SELECT * FROM uctest;
8 | one
(1 row)
---- sensitive cursors can't currently scroll back, so this is an error:
+--- FOR UPDATE cursors can't currently scroll back, so this is an error:
FETCH RELATIVE 0 FROM c1;
ERROR: cursor can only scan forward
HINT: Declare it with SCROLL option to enable backward scan.
@@ -1106,6 +1106,41 @@ SELECT * FROM uctest;
8 | one
(2 rows)
+-- Check insensitive cursor with INSERT
+-- (The above tests don't test the SQL notion of an insensitive cursor
+-- correctly, because per SQL standard, changes from WHERE CURRENT OF
+-- commands should be visible in the cursor. So here we make the
+-- changes with a command that is independent of the cursor.)
+BEGIN;
+DECLARE c1 INSENSITIVE CURSOR FOR SELECT * FROM uctest;
+INSERT INTO uctest VALUES (10, 'ten');
+FETCH NEXT FROM c1;
+ f1 | f2
+----+-------
+ 3 | three
+(1 row)
+
+FETCH NEXT FROM c1;
+ f1 | f2
+----+-----
+ 8 | one
+(1 row)
+
+FETCH NEXT FROM c1; -- insert not visible
+ f1 | f2
+----+----
+(0 rows)
+
+COMMIT;
+SELECT * FROM uctest;
+ f1 | f2
+----+-------
+ 3 | three
+ 8 | one
+ 10 | ten
+(3 rows)
+
+DELETE FROM uctest WHERE f1 = 10; -- restore test table state
-- Check inheritance cases
CREATE TEMP TABLE ucchild () inherits (uctest);
INSERT INTO ucchild values(100, 'hundred');
diff --git a/src/test/regress/sql/portals.sql b/src/test/regress/sql/portals.sql
index 52560ac027..bf1dff884d 100644
--- a/src/test/regress/sql/portals.sql
+++ b/src/test/regress/sql/portals.sql
@@ -382,11 +382,26 @@ CREATE TEMP TABLE uctest(f1 int, f2 text);
SELECT * FROM uctest;
UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- no-op
SELECT * FROM uctest;
---- sensitive cursors can't currently scroll back, so this is an error:
+--- FOR UPDATE cursors can't currently scroll back, so this is an error:
FETCH RELATIVE 0 FROM c1;
ROLLBACK;
SELECT * FROM uctest;
+-- Check insensitive cursor with INSERT
+-- (The above tests don't test the SQL notion of an insensitive cursor
+-- correctly, because per SQL standard, changes from WHERE CURRENT OF
+-- commands should be visible in the cursor. So here we make the
+-- changes with a command that is independent of the cursor.)
+BEGIN;
+DECLARE c1 INSENSITIVE CURSOR FOR SELECT * FROM uctest;
+INSERT INTO uctest VALUES (10, 'ten');
+FETCH NEXT FROM c1;
+FETCH NEXT FROM c1;
+FETCH NEXT FROM c1; -- insert not visible
+COMMIT;
+SELECT * FROM uctest;
+DELETE FROM uctest WHERE f1 = 10; -- restore test table state
+
-- Check inheritance cases
CREATE TEMP TABLE ucchild () inherits (uctest);
INSERT INTO ucchild values(100, 'hundred');
--
2.30.1
On Thu, Feb 25, 2021 at 8:37 AM Peter Eisentraut <
peter.eisentraut@enterprisedb.com> wrote:
On 18.02.21 19:14, Peter Eisentraut wrote:
On 18.02.21 17:11, David G. Johnston wrote:
The OP was doing a course based on Oracle and was confused regarding
our behavior. The documentation failed to help me provide a useful
response, so I'd agree there is something here that needs reworking if
not outright fixing.According to the piece of the standard that I posted, the sensitivity
behavior here is implementation-dependent (not even -defined), so both
implementations are correct.But the poster was apparently also confused by the same piece of
documentation.I came up with the attached patch to sort this out a bit. It does not
change any cursor behavior. But the documentation now uses the terms
more correctly and explains the differences between SQL and the
PostgreSQL implementation better, I think.
thanks!, though this seems like the wrong approach. Simply noting that our
cursor is not standard compliant (or at least we don't implement a
standard-compliant sensitive cursor) should suffice. I don't really get
the point of adding ASENSITIVE if we don't have SENSITIVE too. I'm also
unfamiliar with the standard default behaviors to comment on where we
differ there - but that should be easy enough to address.
I would suggest limiting the doc change to pointing out that we do allow
for a standard-compliant INSENSITIVE behaving cursor - one that precludes
local sensitively via the FOR SHARE and FOR UPDATE clauses - by adding that
keyword. Otherwise, while the cursor is still (and always) insensitive
globally the cursor can become locally sensitive implicitly by including a
FOR UPDATE or FOR SHARE clause in the query. Then maybe consider improving
the notes section through subtraction once a more clear initial
presentation has been made to the reader.
David J.
On 09.03.21 00:22, David G. Johnston wrote:
I came up with the attached patch to sort this out a bit. It does not
change any cursor behavior. But the documentation now uses the terms
more correctly and explains the differences between SQL and the
PostgreSQL implementation better, I think.thanks!, though this seems like the wrong approach. Simply noting that
our cursor is not standard compliant (or at least we don't implement a
standard-compliant sensitive cursor) should suffice.
Well, we could just say, our behavior wrong/different. But I think it's
actually right, we were just looking at an incorrect premise and making
additional claims about it that are not accurate.
I don't really get
the point of adding ASENSITIVE if we don't have SENSITIVE too. I'm also
unfamiliar with the standard default behaviors to comment on where we
differ there - but that should be easy enough to address.
ASENSITIVE is merely a keyword to select the default behavior. Other
SQL implementations also have it, so it seems sensible to add it while
we're polishing this.