Обсуждение: SCROLLABLE/UPDATABLE cursor question
Hi All,
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?
--
Regards,
Garfield A. Lewis
Garfield Lewis <garfield.lewis@lzlabs.com> writes:
> Im not sure where to ask the question so Ill start here. Does anyone know if Postgres has any plans to support
statementslike 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> 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> 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