src/test/regress/expected/portals_updatable_optimizer.out

--
-- Tests for updatable cursors
-- 
-- Deterministic test runs will be a little tricky in the case of updatable
-- cursors. By definition, these cursors cannot be ordered, yet we will be
-- issuing UPDATE and DELETE based exclusively on cursor position.
--
CREATE TEMP TABLE uctest(f1 int, f2 int, f3 text) DISTRIBUTED BY (f1);
CREATE TEMP TABLE uctest2(f1 int, f2 int, f3 text) DISTRIBUTED BY (f1);
INSERT INTO uctest VALUES (1, 1, 'one'), (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
SELECT * FROM uctest ORDER BY 1, 2, 3;
 f1 | f2 |  f3   
----+----+-------
  1 |  1 | one
  1 |  1 | one
  2 |  2 | two
  3 |  3 | three
(4 rows)

--
-- DELETE ... WHERE CURRENT
---
BEGIN;
DECLARE c1 CURSOR FOR SELECT * FROM uctest WHERE f1 = 1;
FETCH 1 FROM c1;
 f1 | f2 | f3  
----+----+-----
  1 |  1 | one
(1 row)

DELETE FROM uctest WHERE CURRENT OF c1;
-- should show deletion
SELECT * FROM uctest ORDER BY 1, 2, 3;
 f1 | f2 |  f3   
----+----+-------
  1 |  1 | one
  2 |  2 | two
  3 |  3 | three
(3 rows)

-- cursor did not move
FETCH ALL FROM c1;
 f1 | f2 | f3  
----+----+-----
  1 |  1 | one
(1 row)

COMMIT;
-- should still see deletion
SELECT * FROM uctest ORDER BY 1, 2, 3;
 f1 | f2 |  f3   
----+----+-------
  1 |  1 | one
  2 |  2 | two
  3 |  3 | three
(3 rows)

--
-- UPDATE ... WHERE CURRENT against SELECT ... FOR UPDATE
--
BEGIN;
DECLARE c1 CURSOR FOR SELECT * FROM uctest WHERE f1 = 2 FOR UPDATE;
FETCH 1 FROM c1;
 f1 | f2 | f3  
----+----+-----
  2 |  2 | two
(1 row)

UPDATE uctest SET f2 = 8 WHERE CURRENT OF c1;
SELECT * FROM uctest ORDER BY 1, 2, 3;
 f1 | f2 |  f3   
----+----+-------
  1 |  1 | one
  2 |  8 | two
  3 |  3 | three
(3 rows)

COMMIT;
SELECT * FROM uctest ORDER BY 1, 2, 3;
 f1 | f2 |  f3   
----+----+-------
  1 |  1 | one
  2 |  8 | two
  3 |  3 | three
(3 rows)

-- 
-- UPDATE ... WHERE CURRENT against SELECT ... FOR SHARE
--
BEGIN;
DECLARE c1 CURSOR FOR SELECT * FROM uctest WHERE f1 = 3 FOR SHARE;
FETCH 1 FROM c1;
 f1 | f2 |  f3   
----+----+-------
  3 |  3 | three
(1 row)

UPDATE uctest SET f2 = 9 WHERE CURRENT OF c1;
SELECT * FROM uctest ORDER BY 1, 2, 3;
 f1 | f2 |  f3   
----+----+-------
  1 |  1 | one
  2 |  8 | two
  3 |  9 | three
(3 rows)

COMMIT;
SELECT * FROM uctest ORDER BY 1, 2, 3;
 f1 | f2 |  f3   
----+----+-------
  1 |  1 | one
  2 |  8 | two
  3 |  9 | three
(3 rows)

--
-- Scan paths
--
CREATE INDEX uctest_index ON uctest USING btree(f1);
SET enable_tidscan=on; SET enable_seqscan=off; SET enable_indexscan=off;
BEGIN;
DECLARE a CURSOR FOR SELECT * FROM uctest WHERE f1 = 3;
FETCH 1 FROM a;
 f1 | f2 |  f3   
----+----+-------
  3 |  9 | three
(1 row)

UPDATE uctest SET f2 = 10 WHERE CURRENT OF a;
COMMIT;
SELECT * FROM uctest ORDER BY 1, 2, 3;
 f1 | f2 |  f3   
----+----+-------
  1 |  1 | one
  2 |  8 | two
  3 | 10 | three
(3 rows)

SET enable_tidscan=off; SET enable_seqscan=on; SET enable_indexscan=off;
BEGIN;
DECLARE a CURSOR FOR SELECT * FROM uctest WHERE f1 = 3;
FETCH 1 FROM a;
 f1 | f2 |  f3   
----+----+-------
  3 | 10 | three
(1 row)

UPDATE uctest SET f2 = 11 WHERE CURRENT OF a;
COMMIT;
SELECT * FROM uctest ORDER BY 1, 2, 3;
 f1 | f2 |  f3   
----+----+-------
  1 |  1 | one
  2 |  8 | two
  3 | 11 | three
(3 rows)

SET enable_tidscan=off; SET enable_seqscan=off; SET enable_indexscan=on;
BEGIN;
DECLARE a CURSOR FOR SELECT * FROM uctest WHERE f1 = 3;
FETCH 1 FROM a;
 f1 | f2 |  f3   
----+----+-------
  3 | 11 | three
(1 row)

UPDATE uctest SET f2 = 12 WHERE CURRENT OF a;
COMMIT;
SELECT * FROM uctest ORDER BY 1, 2, 3;
 f1 | f2 |  f3   
----+----+-------
  1 |  1 | one
  2 |  8 | two
  3 | 12 | three
(3 rows)

RESET enable_tidscan; RESET enable_seqscan; RESET enable_indexscan;
--
-- System attributes already residing in targetlist
--
BEGIN;
DECLARE c1 CURSOR FOR SELECT f1 as gp_segment_id, * FROM uctest WHERE f1 = 3; 
FETCH 1 FROM c1;
 gp_segment_id | f1 | f2 |  f3   
---------------+----+----+-------
             3 |  3 | 12 | three
(1 row)

UPDATE uctest SET f2 = -1 * f2 WHERE CURRENT OF c1;
COMMIT;
SELECT * FROM uctest ORDER BY 1, 2, 3;
 f1 | f2  |  f3   
----+-----+-------
  1 |   1 | one
  2 |   8 | two
  3 | -12 | three
(3 rows)

BEGIN;
DECLARE c1 CURSOR FOR SELECT 100 as ctid, * FROM uctest WHERE f1 = 3;
FETCH 1 FROM c1;
 ctid | f1 | f2  |  f3   
------+----+-----+-------
  100 |  3 | -12 | three
(1 row)

UPDATE uctest SET f2 = -1 * f2 WHERE CURRENT OF c1;
COMMIT;
SELECT * FROM uctest ORDER BY 1, 2, 3;
 f1 | f2 |  f3   
----+----+-------
  1 |  1 | one
  2 |  8 | two
  3 | 12 | three
(3 rows)

--
-- Repeated updates
--
BEGIN;
DECLARE c CURSOR FOR SELECT * from uctest WHERE f1 = 3;
FETCH 1 from c;
 f1 | f2 |  f3   
----+----+-------
  3 | 12 | three
(1 row)

UPDATE uctest SET f2 = f2 + 10 WHERE CURRENT of c;
SELECT * from uctest ORDER BY 1, 2, 3;
 f1 | f2 |  f3   
----+----+-------
  1 |  1 | one
  2 |  8 | two
  3 | 22 | three
(3 rows)

UPDATE uctest SET f2 = f2 + 10 WHERE CURRENT of c;
SELECT * from uctest ORDER BY 1, 2, 3;
 f1 | f2 |  f3   
----+----+-------
  1 |  1 | one
  2 |  8 | two
  3 | 32 | three
(3 rows)

COMMIT;
SELECT * from uctest ORDER BY 1, 2, 3;
 f1 | f2 |  f3   
----+----+-------
  1 |  1 | one
  2 |  8 | two
  3 | 32 | three
(3 rows)

--
-- UPDATE with FROM and subqueries
--
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM uctest WHERE f1 = 3;
FETCH 1 FROM c;
 f1 | f2 |  f3   
----+----+-------
  3 | 32 | three
(1 row)

UPDATE uctest SET f2 = other.f2 + 10 FROM
    (SELECT * FROM uctest WHERE f1 = 3) AS other
    WHERE CURRENT OF c;
COMMIT;
SELECT * FROM uctest ORDER BY 1, 2, 3;
 f1 | f2 |  f3   
----+----+-------
  1 |  1 | one
  2 |  8 | two
  3 | 42 | three
(3 rows)

BEGIN;
DECLARE c CURSOR FOR SELECT * FROM uctest WHERE f1 = 3;
FETCH 1 FROM c;
 f1 | f2 |  f3   
----+----+-------
  3 | 42 | three
(1 row)

UPDATE uctest SET f2 = (SELECT f2 - 10 FROM uctest WHERE f1 = 3) WHERE CURRENT OF c;
COMMIT;
SELECT * FROM uctest ORDER BY 1, 2, 3;
 f1 | f2 |  f3   
----+----+-------
  1 |  1 | one
  2 |  8 | two
  3 | 32 | three
(3 rows)

--
-- Partitioning
--
-- Partitioning: ensure each part has overlapping gp_segment_id/ctid, to test the 
--				 use of tableoid as qual
CREATE TABLE portals_updatable_rank(id int, rank int, f int)
	DISTRIBUTED BY (id)
	PARTITION BY RANGE (rank)
		(START (0) END (10) EVERY (1),
		 DEFAULT PARTITION extra );
INSERT INTO portals_updatable_rank (SELECT x, x, x FROM generate_series(0, 10) x);
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM portals_updatable_rank WHERE id = 1;
FETCH 1 FROM c;
 id | rank | f 
----+------+---
  1 |    1 | 1
(1 row)

UPDATE portals_updatable_rank SET f = f * -1 WHERE CURRENT OF c;
SELECT * FROM portals_updatable_rank ORDER BY 1, 2, 3;
 id | rank | f  
----+------+----
  0 |    0 |  0
  1 |    1 | -1
  2 |    2 |  2
  3 |    3 |  3
  4 |    4 |  4
  5 |    5 |  5
  6 |    6 |  6
  7 |    7 |  7
  8 |    8 |  8
  9 |    9 |  9
 10 |   10 | 10
(11 rows)

COMMIT;
SELECT * FROM portals_updatable_rank ORDER BY 1, 2, 3;
 id | rank | f  
----+------+----
  0 |    0 |  0
  1 |    1 | -1
  2 |    2 |  2
  3 |    3 |  3
  4 |    4 |  4
  5 |    5 |  5
  6 |    6 |  6
  7 |    7 |  7
  8 |    8 |  8
  9 |    9 |  9
 10 |   10 | 10
(11 rows)

-- Partitioning: what if range tables of DECLARE CURSOR and CURRENT OF differ? with one
--				 pointing at logical table and the other pointing merely at a part
-- Partitioning: DECLARE CURSOR against portals_updatable_rank_1_prt_extra, CURRENT OF against portals_updatable_rank_1_prt_extra
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM portals_updatable_rank_1_prt_extra;
FETCH 1 FROM c;
 id | rank | f  
----+------+----
 10 |   10 | 10
(1 row)

UPDATE portals_updatable_rank_1_prt_extra set f = f * -1 WHERE CURRENT OF c;
COMMIT;
SELECT * FROM portals_updatable_rank_1_prt_extra ORDER BY 1, 2, 3;
 id | rank |  f  
----+------+-----
 10 |   10 | -10
(1 row)

-- Partitioning: DECLARE CURSOR against portals_updatable_rank, CURRENT OF against portals_updatable_rank_1_prt_extra
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM portals_updatable_rank WHERE rank = 10; 
DELETE FROM portals_updatable_rank_1_prt_extra WHERE CURRENT OF c;	-- error out on wrong table
ERROR:  cursor "c" is not a simply updatable scan of table "portals_updatable_rank_1_prt_extra"
ROLLBACK;
-- Partitioning: DECLARE CURSOR against portals_updatable_rank_1_prt_extra, CURRENT OF against portals_updatable_rank
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM portals_updatable_rank_1_prt_extra WHERE rank = 10; 
DELETE FROM portals_updatable_rank WHERE CURRENT OF c;	-- error out on wrong table
ERROR:  cursor "c" is not a simply updatable scan of table "portals_updatable_rank"
ROLLBACK;
-- Partitioning, cursor-agnostic: move tuple across partitions
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM portals_updatable_rank WHERE rank = 1;
FETCH 1 FROM c;
 id | rank | f  
----+------+----
  1 |    1 | -1
(1 row)

UPDATE portals_updatable_rank SET rank = rank + 1 WHERE CURRENT OF c;
SELECT tableoid::regclass, * FROM portals_updatable_rank;
              tableoid              | id | rank |  f  
------------------------------------+----+------+-----
 portals_updatable_rank_1_prt_7     |  5 |    5 |   5
 portals_updatable_rank_1_prt_8     |  6 |    6 |   6
 portals_updatable_rank_1_prt_11    |  9 |    9 |   9
 portals_updatable_rank_1_prt_extra | 10 |   10 | -10
 portals_updatable_rank_1_prt_4     |  2 |    2 |   2
 portals_updatable_rank_1_prt_5     |  3 |    3 |   3
 portals_updatable_rank_1_prt_6     |  4 |    4 |   4
 portals_updatable_rank_1_prt_9     |  7 |    7 |   7
 portals_updatable_rank_1_prt_10    |  8 |    8 |   8
 portals_updatable_rank_1_prt_2     |  0 |    0 |   0
 portals_updatable_rank_1_prt_4     |  1 |    2 |  -1
(11 rows)

ROLLBACK;
-- Partitioning: AO part
CREATE TABLE aopart (LIKE portals_updatable_rank) WITH (appendonly=true) DISTRIBUTED BY (id);
INSERT INTO aopart SELECT * FROM portals_updatable_rank_1_prt_2;
ALTER TABLE portals_updatable_rank EXCHANGE PARTITION FOR (0) WITH TABLE aopart;
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM portals_updatable_rank WHERE rank = 0;
FETCH 1 FROM c;
 id | rank | f 
----+------+---
  0 |    0 | 0
(1 row)

DELETE FROM portals_updatable_rank WHERE CURRENT OF c;
ERROR:  "portals_updatable_rank_1_prt_2" is not simply updatable
ROLLBACK;
-- Partitioning: AO/CO part
CREATE TABLE aocopart (LIKE portals_updatable_rank) WITH (appendonly=true, orientation=column)
DISTRIBUTED BY (id);
INSERT INTO aocopart SELECT * FROM portals_updatable_rank_1_prt_2;
ALTER TABLE portals_updatable_rank EXCHANGE PARTITION FOR (0) WITH TABLE aocopart;
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM portals_updatable_rank WHERE rank = 0;
FETCH 1 FROM c;
 id | rank | f 
----+------+---
  0 |    0 | 0
(1 row)

DELETE FROM portals_updatable_rank WHERE CURRENT OF c;
ERROR:  "portals_updatable_rank_1_prt_2" is not simply updatable
ROLLBACK;
-- Partitioning: mostly AO parts
--  Despite some non-determinism, this should cover the case in which AO parts
--  are scanned by the segments. Despite our grand attempts to disallow AO/CO tables,
--  the executor will need to cope with AO tuples, for cases just like this.
DROP TABLE aopart;
CREATE TABLE aopart (LIKE portals_updatable_rank) WITH (appendonly=true) DISTRIBUTED BY (id);
INSERT INTO aopart SELECT * FROM portals_updatable_rank_1_prt_2;
ALTER TABLE portals_updatable_rank EXCHANGE PARTITION FOR (0) WITH TABLE aopart;
DROP TABLE aopart;
CREATE TABLE aopart (LIKE portals_updatable_rank) WITH (appendonly=true) DISTRIBUTED BY (id);
INSERT INTO aopart SELECT * FROM portals_updatable_rank_1_prt_3;
ALTER TABLE portals_updatable_rank EXCHANGE PARTITION FOR (1) WITH TABLE aopart;
DROP TABLE aopart;
CREATE TABLE aopart (LIKE portals_updatable_rank) WITH (appendonly=true) DISTRIBUTED BY (id);
INSERT INTO aopart SELECT * FROM portals_updatable_rank_1_prt_4;
ALTER TABLE portals_updatable_rank EXCHANGE PARTITION FOR (2) WITH TABLE aopart;
DROP TABLE aopart;
CREATE TABLE aopart (LIKE portals_updatable_rank) WITH (appendonly=true) DISTRIBUTED BY (id);
INSERT INTO aopart SELECT * FROM portals_updatable_rank_1_prt_5;
ALTER TABLE portals_updatable_rank EXCHANGE PARTITION FOR (3) WITH TABLE aopart;
DROP TABLE aopart;
CREATE TABLE aopart (LIKE portals_updatable_rank) WITH (appendonly=true) DISTRIBUTED BY (id);
INSERT INTO aopart SELECT * FROM portals_updatable_rank_1_prt_6;
ALTER TABLE portals_updatable_rank EXCHANGE PARTITION FOR (4) WITH TABLE aopart;
DROP TABLE aopart;
CREATE TABLE aopart (LIKE portals_updatable_rank) WITH (appendonly=true) DISTRIBUTED BY (id);
INSERT INTO aopart SELECT * FROM portals_updatable_rank_1_prt_7;
ALTER TABLE portals_updatable_rank EXCHANGE PARTITION FOR (5) WITH TABLE aopart;
DROP TABLE aopart;
CREATE TABLE aopart (LIKE portals_updatable_rank) WITH (appendonly=true) DISTRIBUTED BY (id);
INSERT INTO aopart SELECT * FROM portals_updatable_rank_1_prt_8;
ALTER TABLE portals_updatable_rank EXCHANGE PARTITION FOR (6) WITH TABLE aopart;
DROP TABLE aopart;
CREATE TABLE aopart (LIKE portals_updatable_rank) WITH (appendonly=true) DISTRIBUTED BY (id);
INSERT INTO aopart SELECT * FROM portals_updatable_rank_1_prt_9;
ALTER TABLE portals_updatable_rank EXCHANGE PARTITION FOR (7) WITH TABLE aopart;
DROP TABLE aopart;
CREATE TABLE aopart (LIKE portals_updatable_rank) WITH (appendonly=true) DISTRIBUTED BY (id);
INSERT INTO aopart SELECT * FROM portals_updatable_rank_1_prt_10;
ALTER TABLE portals_updatable_rank EXCHANGE PARTITION FOR (8) WITH TABLE aopart;
DROP TABLE aopart;
CREATE TABLE aopart (LIKE portals_updatable_rank) WITH (appendonly=true) DISTRIBUTED BY (id);
INSERT INTO aopart SELECT * FROM portals_updatable_rank_1_prt_11;
ALTER TABLE portals_updatable_rank EXCHANGE PARTITION FOR (9) WITH TABLE aopart;
ANALYZE portals_updatable_rank;
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM portals_updatable_rank WHERE rank = 10;    -- isolate the remaining heap part
FETCH 1 FROM c;
 id | rank |  f  
----+------+-----
 10 |   10 | -10
(1 row)

UPDATE portals_updatable_rank SET f = f * -1 WHERE CURRENT OF c;
COMMIT;
SELECT * FROM portals_updatable_rank ORDER BY 1, 2, 3;
 id | rank | f  
----+------+----
  0 |    0 |  0
  1 |    1 | -1
  2 |    2 |  2
  3 |    3 |  3
  4 |    4 |  4
  5 |    5 |  5
  6 |    6 |  6
  7 |    7 |  7
  8 |    8 |  8
  9 |    9 |  9
 10 |   10 | 10
(11 rows)

-- Partitioning, sub-partitioning
CREATE TABLE bar (a int, b int, c int, d int)
    DISTRIBUTED BY (a)
    PARTITION BY RANGE(b)
    SUBPARTITION BY LIST( c)
        SUBPARTITION TEMPLATE (
            DEFAULT SUBPARTITION subothers,
            SUBPARTITION s1 VALUES(0,1,2),
            SUBPARTITION s2 VALUES(3,4,5)
        )
    ( DEFAULT PARTITION others, START(0) END(6) EVERY(1) );
INSERT INTO bar (SELECT x, x % 6, x % 6, x FROM generate_series(0, 11) x);
BEGIN;
DECLARE a CURSOR FOR SELECT * FROM bar WHERE a = 0;
FETCH 1 FROM a;
 a | b | c | d 
---+---+---+---
 0 | 0 | 0 | 0
(1 row)

UPDATE bar SET d = -1000 WHERE CURRENT OF a;
COMMIT;
SELECT * FROM bar ORDER BY 1, 2, 3, 4;
 a  | b | c |   d   
----+---+---+-------
  0 | 0 | 0 | -1000
  1 | 1 | 1 |     1
  2 | 2 | 2 |     2
  3 | 3 | 3 |     3
  4 | 4 | 4 |     4
  5 | 5 | 5 |     5
  6 | 0 | 0 |     6
  7 | 1 | 1 |     7
  8 | 2 | 2 |     8
  9 | 3 | 3 |     9
 10 | 4 | 4 |    10
 11 | 5 | 5 |    11
(12 rows)

-- Partitioning, update distribution key
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM portals_updatable_rank  WHERE rank = 10;
FETCH 1 FROM c;
 id | rank | f  
----+------+----
 10 |   10 | 10
(1 row)

UPDATE portals_updatable_rank SET id = id + 1 WHERE CURRENT OF c;
COMMIT;
SELECT * FROM portals_updatable_rank  ORDER BY 1, 2, 3;
 id | rank | f  
----+------+----
  0 |    0 |  0
  1 |    1 | -1
  2 |    2 |  2
  3 |    3 |  3
  4 |    4 |  4
  5 |    5 |  5
  6 |    6 |  6
  7 |    7 |  7
  8 |    8 |  8
  9 |    9 |  9
 11 |   10 | 10
(11 rows)

-- 
-- Expected Failure
--
-- WHERE CURRENT OF against SELECT ... READ ONLY
-- UPDATE succeeds despite READ ONLY designation
--
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM uctest WHERE f1 = 3 FOR READ ONLY;
FETCH 1 FROM c;
 f1 | f2 |  f3   
----+----+-------
  3 | 32 | three
(1 row)

UPDATE uctest SET f2 = f2 + 10 WHERE CURRENT OF c; 
COMMIT;
SELECT * FROM uctest ORDER BY 1, 2, 3;
 f1 | f2 |  f3   
----+----+-------
  1 |  1 | one
  2 |  8 | two
  3 | 42 | three
(3 rows)

-- gp_dist_random, edge case treated as special RTE_RELATION
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM gp_dist_random('uctest') WHERE f1 = 3;
FETCH 1 FROM c;
 f1 | f2 |  f3   
----+----+-------
  3 | 42 | three
(1 row)

UPDATE uctest SET f2 = f2 + 10 WHERE CURRENT OF c;
COMMIT;
SELECT * FROM uctest ORDER BY 1, 2, 3;
 f1 | f2 |  f3   
----+----+-------
  1 |  1 | one
  2 |  8 | two
  3 | 52 | three
(3 rows)

-- naive PREPARE usage
PREPARE ucplan AS UPDATE uctest SET f2 = f2 * -1 WHERE CURRENT OF a;
BEGIN;
DECLARE a CURSOR FOR SELECT * FROM uctest WHERE f1 = 3;
FETCH 1 FROM a;
 f1 | f2 |  f3   
----+----+-------
  3 | 52 | three
(1 row)

EXECUTE ucplan;
COMMIT;
SELECT * FROM uctest ORDER BY 1, 2, 3;
 f1 | f2  |  f3   
----+-----+-------
  1 |   1 | one
  2 |   8 | two
  3 | -52 | three
(3 rows)

--
-- Negative
--
-- Negative: no such cursor
DELETE FROM uctest WHERE CURRENT OF c1; 
ERROR:  cursor "c1" does not exist
-- Negative: wrong UPDATE syntax
UPDATE uctest SET f2 = 5 WHERE CURRENT OF c1 AND f3 = 10;
ERROR:  syntax error at or near "AND"
LINE 1: UPDATE uctest SET f2 = 5 WHERE CURRENT OF c1 AND f3 = 10;
                                                     ^
-- Negative: can't use held cursor
DECLARE cx CURSOR WITH HOLD FOR SELECT * FROM uctest;
DELETE FROM uctest WHERE CURRENT OF cx;  
ERROR:  cursor "cx" is held from a previous transaction
-- Negative: cursor on wrong table
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM uctest where f1 = 3;
FETCH 1 from c;
 f1 | f2  |  f3   
----+-----+-------
  3 | -52 | three
(1 row)

DELETE FROM uctest2 WHERE CURRENT OF c; 
ERROR:  cursor "c" is not a simply updatable scan of table "uctest2"
ROLLBACK;
-- Negative: cursor is ordered
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM uctest ORDER BY f1;
DELETE FROM uctest WHERE CURRENT OF c;
ERROR:  cursor "c" is not a simply updatable scan of table "uctest"
ROLLBACK;
-- Negative: cursor is on a join
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM uctest JOIN uctest2 USING (f1);
DELETE FROM uctest WHERE CURRENT OF c;
ERROR:  cursor "c" is not a simply updatable scan of table "uctest"
ROLLBACK;
-- Negative: cursor with subquery
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM uctest WHERE f1 in (SELECT f1 FROM uctest);
DELETE FROM uctest WHERE CURRENT OF c;
ERROR:  cursor "c" is not a simply updatable scan of table "uctest"
ROLLBACK;
-- Negative: cursor is on aggregation
BEGIN;
DECLARE c CURSOR FOR SELECT count(*) FROM uctest;
DELETE FROM uctest WHERE CURRENT OF c;
ERROR:  cursor "c" is not a simply updatable scan of table "uctest"
ROLLBACK;
BEGIN;
DECLARE c CURSOR FOR SELECT f1,count(*) FROM uctest GROUP BY f1;
DELETE FROM uctest WHERE CURRENT OF c; 
ERROR:  cursor "c" is not a simply updatable scan of table "uctest"
ROLLBACK;
BEGIN;
DECLARE c CURSOR FOR SELECT f1,count(*) FROM uctest GROUP BY f1 HAVING count(*) = 1;
DELETE FROM uctest WHERE CURRENT OF c;
ERROR:  cursor "c" is not a simply updatable scan of table "uctest"
ROLLBACK;
-- Negative: cursor with distinct clause
BEGIN;
DECLARE c CURSOR FOR SELECT DISTINCT f1, f2 FROM uctest;
DELETE FROM uctest WHERE CURRENT OF c;
ERROR:  cursor "c" is not a simply updatable scan of table "uctest"
ROLLBACK;
-- Negative: cursor with limit/offset
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM uctest LIMIT 1;
DELETE FROM uctest WHERE CURRENT OF c;
ERROR:  cursor "c" is not a simply updatable scan of table "uctest"
ROLLBACK;
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM uctest OFFSET 1;
DELETE FROM uctest WHERE CURRENT OF c;
ERROR:  cursor "c" is not a simply updatable scan of table "uctest"
ROLLBACK;
-- Negative: cursor with set operations
-- MPP-16559 - DECLARE CURSOR with set operations will hit assertion
-- BEGIN;
-- DECLARE c CURSOR FOR SELECT f1 FROM uctest UNION SELECT f2 FROM uctest2;
-- DELETE FROM uctest WHERE CURRENT OF c;
-- ROLLBACK;
-- BEGIN;
-- DECLARE c CURSOR FOR SELECT f1 FROM uctest UNION ALL SELECT f2 FROM uctest2;
-- DELETE FROM uctest WHERE CURRENT OF c;
-- ROLLBACK;
-- BEGIN;
-- DECLARE c CURSOR FOR SELECT f1 FROM uctest EXCEPT SELECT f2 FROM uctest2;
-- DELETE FROM uctest WHERE CURRENT OF c;
-- ROLLBACK;
-- Negative: cursor with window clauses
BEGIN;
DECLARE c1 CURSOR FOR SELECT f1, rank() OVER (ORDER BY f1 DESC) FROM uctest ORDER BY f1;
DELETE FROM uctest WHERE CURRENT OF c1;
ERROR:  cursor "c1" is not a simply updatable scan of table "uctest"
ROLLBACK;
-- Negative: cursor is not positioned
BEGIN;
DECLARE c1 CURSOR FOR SELECT * FROM uctest;
DELETE FROM uctest WHERE CURRENT OF c1; 
ERROR:  cursor "c1" is not positioned on a row
ROLLBACK;
BEGIN;
DECLARE c1 CURSOR FOR SELECT * FROM uctest;
FETCH ALL FROM c1;
 f1 | f2  |  f3   
----+-----+-------
  2 |   8 | two
  3 | -52 | three
  1 |   1 | one
(3 rows)

FETCH 1 FROM c1;
 f1 | f2 | f3 
----+----+----
(0 rows)

DELETE FROM uctest WHERE CURRENT OF c1;
ERROR:  cursor "c1" is not positioned on a row
ROLLBACK;
-- Negative: cursor on views
CREATE TEMP VIEW ucview AS SELECT * FROM uctest;
CREATE RULE ucrule AS ON DELETE TO ucview DO INSTEAD
  DELETE FROM uctest WHERE f2 = OLD.f2;
BEGIN;
DECLARE c1 CURSOR FOR SELECT * FROM ucview WHERE f1 = 3;
FETCH 1 FROM c1;
 f1 | f2  |  f3   
----+-----+-------
  3 | -52 | three
(1 row)

DELETE FROM ucview WHERE CURRENT OF c1;
ERROR:  WHERE CURRENT OF on a view is not implemented
ROLLBACK;
-- Negative, cursor-agnostic: cannot update external tables
CREATE EXTERNAL WEB TABLE ucexttest (x text) EXECUTE 'echo "foo";' FORMAT 'TEXT';
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM ucexttest;
FETCH 1 from c;
  x  
-----
 foo
(1 row)

UPDATE ucexttest SET x = 'bar' WHERE CURRENT OF c;
ERROR:  "ucexttest" is not simply updatable
ROLLBACK;
DROP EXTERNAL TABLE ucexttest;
-- Negative, cursor-agnostic: cannot update AO
CREATE TEMP TABLE aotest (a int, b text)
	WITH (appendonly=true);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM aotest;
DELETE FROM aotest WHERE CURRENT OF c;
ERROR:  "aotest" is not simply updatable
ROLLBACK;
-- Negative, cursor-agnostic: cannot update AO/CO
CREATE TEMP TABLE aocotest (a int, b text)
	WITH (appendonly=true, orientation=column);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM aocotest;
DELETE FROM aocotest WHERE CURRENT OF c;
ERROR:  "aocotest" is not simply updatable
ROLLBACK;
-- Negative: cursor with limit/offset against ordinary table
-- The issue: https://github.com/greenplum-db/gpdb/issues/9838
CREATE TABLE tidscan_9838(id integer);
INSERT INTO tidscan_9838 (id) VALUES (1), (2), (3);
BEGIN;
DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan_9838 LIMIT 1;
UPDATE tidscan_9838 SET id = -id WHERE CURRENT OF c;
ERROR:  cursor "c" is not a simply updatable scan of table "tidscan_9838"
ROLLBACK;
BEGIN;
DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan_9838 OFFSET 1;
UPDATE tidscan_9838 SET id = -id WHERE CURRENT OF c;
ERROR:  cursor "c" is not a simply updatable scan of table "tidscan_9838"
ROLLBACK;
--
-- PL/pgSQL cursors
--
-- Test that cursors opened in PL/pgSQL can also be updated.
-- (Not supported by ORCA, as of this writing.)
create temp table uctest3 as
  select n as i, n as j from generate_series(1, 5) n distributed randomly;
create or replace function plpgsql_uc_test() returns void as $$
declare
  c cursor for select * from uctest3 where i = 3;
  r record;
begin
  open c;
  fetch c into r;
  raise notice '%, %', r.i, r.j;
  update uctest3 set i = i * 100, j = r.j * 2 where current of c;
end;
$$ language plpgsql;
select plpgsql_uc_test();
NOTICE:  3, 3
 plpgsql_uc_test 
-----------------
 
(1 row)

select * from uctest3;
  i  | j 
-----+---
   5 | 5
   1 | 1
   2 | 2
   4 | 4
 300 | 6
(5 rows)