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.)

2 files changed, 12 insertions, 23 deletions

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;