SCROLLABLE/UPDATABLE cursor question

Garfield Lewis <garfield.lewis@lzlabs.com> writes:

I’m not sure where to ask the question so I’ll start here. Does anyone know if Postgres has any plans to support statements like FETCH/MOVE in the non-forward direction for SCROLLABLE/UPDATABLE cursors?

Doesn't that work already?

regards, tom lane

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?

On 4/24/23 08:25, Garfield Lewis wrote:

Tom Lane <tgl@sss.pgh.pa.us> writes:

Doesn't that work already?

Hi Tom,

[sysprog@nucky workspace] (h-master-LZRDB-5220-fix-WCOC-failure)*$ psql -V

psql (PostgreSQL) 14.7

Is this allowed maybe in Postgres 15?

This:

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

seems to indicate you are using some sort of Postgres fork.

Is that the case and if so what is the fork?

--
Adrian Klaver
adrian.klaver@aklaver.com

Garfield Lewis <garfield.lewis@lzlabs.com> writes:

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.

Ah. Yeah, I don't think anyone is contemplating changing that.
Scrollable cursors with side effects would have unpleasant semantic
issues about when do the side-effects happen, and can they happen
multiple times (or, perhaps, not at all if you never read all of
the query output)? FOR UPDATE would be slightly less messy than
other kinds of side-effects, since it's idempotent; but only slightly.

regards, tom lane

Adrian Klaver <adrian.klaver@aklaver.com<mailto:adrian.klaver@aklaver.com>> wrote:

This:

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

seems to indicate you are using some sort of Postgres fork.

Is that the case and if so what is the fork?

This is not a fork… it is pure Postgres 14 with an extension that checks for a certain environment and reports a NOTICE otherwise.

--
Regards,
Garfield A. Lewis

Garfield Lewis <garfield.lewis@lzlabs.com<mailto:garfield.lewis@lzlabs.com>> wrote:

This is not a fork… it is pure Postgres 14 with an extension that checks for a certain environment and reports a NOTICE otherwise.

Oops, said extension meant trigger…

--
Regards,
Garfield A. Lewis