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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Doesn't that work already?

Hi Tom,

This works perfectly well for a NON-UPDATABLE cursor:

[lzsystem(at)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(at)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(at)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(at)nucky workspace] (h-master-LZRDB-5220-fix-WCOC-failure)*$ psql -V
psql (PostgreSQL) 14.7

Is this allowed maybe in Postgres 15?