Modify UPDATE/DELETE WHERE CURRENT OF to use the FOR UPDATE infrastructure to

locate the target row, if the cursor was declared with FOR UPDATE or FOR
SHARE.  This approach is more flexible and reliable than digging through the
plan tree; for instance it can cope with join cursors.  But we still provide
the old code for use with non-FOR-UPDATE cursors.  Per gripe from Robert Haas.

2 files changed, 68 insertions, 0 deletions

diff --git a/src/test/regress/expected/portals.out b/src/test/regress/expected/portals.out
index 66563615d8..95dcea5a1d 100644
--- a/src/test/regress/expected/portals.out
+++ b/src/test/regress/expected/portals.out
@@ -1154,6 +1154,47 @@ SELECT * FROM uctest;
  110 | hundred
 (3 rows)
 
+-- Can update from a self-join, but only if FOR UPDATE says which to use
+BEGIN;
+DECLARE c1 CURSOR FOR SELECT * FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5;
+FETCH 1 FROM c1;
+ f1 | f2  | f1 |  f2   
+----+-----+----+-------
+ 18 | one | 13 | three
+(1 row)
+
+UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;  -- fail
+ERROR:  cursor "c1" is not a simply updatable scan of table "uctest"
+ROLLBACK;
+BEGIN;
+DECLARE c1 CURSOR FOR SELECT * FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5 FOR UPDATE;
+FETCH 1 FROM c1;
+ f1 | f2  | f1 |  f2   
+----+-----+----+-------
+ 18 | one | 13 | three
+(1 row)
+
+UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;  -- fail
+ERROR:  cursor "c1" has multiple FOR UPDATE/SHARE references to table "uctest"
+ROLLBACK;
+BEGIN;
+DECLARE c1 CURSOR FOR SELECT * FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5 FOR SHARE OF a;
+FETCH 1 FROM c1;
+ f1 | f2  | f1 |  f2   
+----+-----+----+-------
+ 18 | one | 13 | three
+(1 row)
+
+UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;
+SELECT * FROM uctest;
+ f1  |   f2    
+-----+---------
+  13 | three
+  28 | one
+ 110 | hundred
+(3 rows)
+
+ROLLBACK;
 -- Check various error cases
 DELETE FROM uctest WHERE CURRENT OF c1;  -- fail, no such cursor
 ERROR:  cursor "c1" does not exist
@@ -1166,6 +1207,11 @@ DELETE FROM uctest WHERE CURRENT OF c;  -- fail, cursor on wrong table
 ERROR:  cursor "c" is not a simply updatable scan of table "uctest"
 ROLLBACK;
 BEGIN;
+DECLARE c CURSOR FOR SELECT * FROM tenk2 FOR SHARE;
+DELETE FROM uctest WHERE CURRENT OF c;  -- fail, cursor on wrong table
+ERROR:  cursor "c" does not have a FOR UPDATE/SHARE reference to table "uctest"
+ROLLBACK;
+BEGIN;
 DECLARE c CURSOR FOR SELECT * FROM tenk1 JOIN tenk2 USING (unique1);
 DELETE FROM tenk1 WHERE CURRENT OF c;  -- fail, cursor is on a join
 ERROR:  cursor "c" is not a simply updatable scan of table "tenk1"
diff --git a/src/test/regress/sql/portals.sql b/src/test/regress/sql/portals.sql
index b53eaac786..4265aaa43c 100644
--- a/src/test/regress/sql/portals.sql
+++ b/src/test/regress/sql/portals.sql
@@ -404,6 +404,24 @@ FETCH 1 FROM c1;
 COMMIT;
 SELECT * FROM uctest;
 
+-- Can update from a self-join, but only if FOR UPDATE says which to use
+BEGIN;
+DECLARE c1 CURSOR FOR SELECT * FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5;
+FETCH 1 FROM c1;
+UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;  -- fail
+ROLLBACK;
+BEGIN;
+DECLARE c1 CURSOR FOR SELECT * FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5 FOR UPDATE;
+FETCH 1 FROM c1;
+UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;  -- fail
+ROLLBACK;
+BEGIN;
+DECLARE c1 CURSOR FOR SELECT * FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5 FOR SHARE OF a;
+FETCH 1 FROM c1;
+UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;
+SELECT * FROM uctest;
+ROLLBACK;
+
 -- Check various error cases
 
 DELETE FROM uctest WHERE CURRENT OF c1;  -- fail, no such cursor
@@ -414,6 +432,10 @@ DECLARE c CURSOR FOR SELECT * FROM tenk2;
 DELETE FROM uctest WHERE CURRENT OF c;  -- fail, cursor on wrong table
 ROLLBACK;
 BEGIN;
+DECLARE c CURSOR FOR SELECT * FROM tenk2 FOR SHARE;
+DELETE FROM uctest WHERE CURRENT OF c;  -- fail, cursor on wrong table
+ROLLBACK;
+BEGIN;
 DECLARE c CURSOR FOR SELECT * FROM tenk1 JOIN tenk2 USING (unique1);
 DELETE FROM tenk1 WHERE CURRENT OF c;  -- fail, cursor is on a join
 ROLLBACK;