Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question

Tom Lane <tgl@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?