Tom Lane <t...@sss.pgh.pa.us> writes: > Doesn't that work already?
Hi Tom, This works perfectly well for a NON-UPDATABLE cursor: [lzsystem@nucky LZRDB-5220] $ psql -U lzpgsupr -d wdbs -f curs.pgs -e BEGIN; BEGIN CREATE TABLE t0(c0 int); psql:curs.pgs:2: NOTICE: DDL was performed without updating catalog tables: Note that CREATE TABLE from a non-SDM client does not maintain LzRelational catalog tables CREATE TABLE INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15); INSERT 0 16 DECLARE cur0 SCROLL CURSOR FOR SELECT * FROM t0; DECLARE CURSOR MOVE FORWARD 10 IN cur0; MOVE 10 MOVE FORWARD -3 IN cur0; MOVE 3 MOVE BACKWARD 3 IN cur0; MOVE 3 FETCH PRIOR FROM cur0; c0 ---- 2 (1 row) ROLLBACK; ROLLBACK However, adding FOR UPDATE gets me this: [lzsystem@nucky LZRDB-5220] $ psql -U lzpgsupr -d wdbs -f curs.pgs -e BEGIN; BEGIN CREATE TABLE t0(c0 int); CREATE TABLE INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15); INSERT 0 16 DECLARE cur0 /*SCROLL*/ CURSOR FOR SELECT * FROM t0 FOR UPDATE; DECLARE CURSOR MOVE FORWARD 10 IN cur0; MOVE 10 MOVE FORWARD -3 IN cur0; psql:curs.pgs:7: ERROR: cursor can only scan forward HINT: Declare it with SCROLL option to enable backward scan. MOVE BACKWARD 3 IN cur0; psql:curs.pgs:8: ERROR: current transaction is aborted, commands ignored until end of transaction block FETCH PRIOR FROM cur0; psql:curs.pgs:9: ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK; ROLLBACK In fact, adding both SCROLL and FOR UPDATE specifically says they are not compatible: [lzsystem@nucky LZRDB-5220] $ psql -U lzpgsupr -d wdbs -f curs.pgs -e BEGIN; BEGIN CREATE TABLE t0(c0 int); INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15); INSERT 0 16 DECLARE cur0 SCROLL CURSOR FOR SELECT * FROM t0 FOR UPDATE; psql:curs.pgs:4: ERROR: DECLARE SCROLL CURSOR ... FOR UPDATE is not supported DETAIL: Scrollable cursors must be READ ONLY. MOVE FORWARD 10 IN cur0; psql:curs.pgs:6: ERROR: current transaction is aborted, commands ignored until end of transaction block MOVE FORWARD -3 IN cur0; psql:curs.pgs:7: ERROR: current transaction is aborted, commands ignored until end of transaction block MOVE BACKWARD 3 IN cur0; psql:curs.pgs:8: ERROR: current transaction is aborted, commands ignored until end of transaction block FETCH PRIOR FROM cur0; psql:curs.pgs:9: ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK; ROLLBACK We are running Postgres 14: [sysprog@nucky workspace] (h-master-LZRDB-5220-fix-WCOC-failure)*$ psql -V psql (PostgreSQL) 14.7 Is this allowed maybe in Postgres 15?
