From 18004101acb98d8fefe7dda1c9f010cceff83b6d Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 16 Nov 2008 17:34:28 +0000 Subject: 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. --- src/test/regress/expected/portals.out | 46 +++++++++++++++++++++++++++++++++++ src/test/regress/sql/portals.sql | 22 +++++++++++++++++ 2 files changed, 68 insertions(+) (limited to 'src/test') 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; -- cgit v1.2.1