|
Tom Lane <tgl@xxxxxxxxxxxxx> writes: 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: 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: psql (PostgreSQL) 14.7 Is this allowed maybe in Postgres 15? |
