From 048efc25e46d95f6a6dad20d65f6d9dd10c640d4 Mon Sep 17 00:00:00 2001
From: Tom Lane
Date: Wed, 24 Oct 2007 23:27:08 +0000
Subject: Disallow scrolling of FOR UPDATE/FOR SHARE cursors, so as to avoid
 problems in corner cases such as re-fetching a just-deleted row.  We may be
 able to relax this someday, but let's find out how many people really care
 before we invest a lot of work in it.  Per report from Heikki and subsequent
 discussion.

While in the neighborhood, make the combination of INSENSITIVE and FOR UPDATE
throw an error, since they are semantically incompatible.  (Up to now we've
accepted but just ignored the INSENSITIVE option of DECLARE CURSOR.)
---
 src/test/regress/expected/portals.out | 31 +++++++++++--------------------
 src/test/regress/sql/portals.sql      |  4 +---
 2 files changed, 12 insertions(+), 23 deletions(-)

(limited to 'src/test')

diff --git a/src/test/regress/expected/portals.out b/src/test/regress/expected/portals.out
index b6673073cdf..527550eabde 100644
--- a/src/test/regress/expected/portals.out
+++ b/src/test/regress/expected/portals.out
@@ -1073,40 +1073,31 @@ SELECT * FROM uctest;
  23 | three
 (2 rows)
 
--- sensitive cursor should show effects of updates or deletes
--- XXX current behavior is WRONG
-FETCH RELATIVE 0 FROM c1;
+DELETE FROM uctest WHERE CURRENT OF c1;
+SELECT * FROM uctest;
  f1 | f2  
 ----+-----
   8 | one
 (1 row)
 
-DELETE FROM uctest WHERE CURRENT OF c1;
-SELECT * FROM uctest;
- f1 |  f2   
-----+-------
- 23 | three
-(1 row)
-
 DELETE FROM uctest WHERE CURRENT OF c1; -- no-op
 SELECT * FROM uctest;
- f1 |  f2   
-----+-------
- 23 | three
+ f1 | f2  
+----+-----
+  8 | one
 (1 row)
 
 UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- no-op
 SELECT * FROM uctest;
- f1 |  f2   
-----+-------
- 23 | three
+ f1 | f2  
+----+-----
+  8 | one
 (1 row)
 
+--- sensitive cursors can't currently scroll back, so this is an error:
 FETCH RELATIVE 0 FROM c1;
- f1 | f2 
-----+----
-(0 rows)
-
+ERROR:  cursor can only scan forward
+HINT:  Declare it with SCROLL option to enable backward scan.
 ROLLBACK;
 SELECT * FROM uctest;
  f1 |  f2   
diff --git a/src/test/regress/sql/portals.sql b/src/test/regress/sql/portals.sql
index bdf5956d69c..8275ed78c84 100644
--- a/src/test/regress/sql/portals.sql
+++ b/src/test/regress/sql/portals.sql
@@ -376,15 +376,13 @@ UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;
 SELECT * FROM uctest;
 UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;
 SELECT * FROM uctest;
--- sensitive cursor should show effects of updates or deletes
--- XXX current behavior is WRONG
-FETCH RELATIVE 0 FROM c1;
 DELETE FROM uctest WHERE CURRENT OF c1;
 SELECT * FROM uctest;
 DELETE FROM uctest WHERE CURRENT OF c1; -- no-op
 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:
 FETCH RELATIVE 0 FROM c1;
 ROLLBACK;
 SELECT * FROM uctest;
-- 
cgit v1.2.3