pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SCROLLABLE/UPDATABLE cursor question
7+ messages / 3 participants
[nested] [flat]

* SCROLLABLE/UPDATABLE cursor question
@ 2023-04-22 18:05 Garfield Lewis <garfield.lewis@lzlabs.com>
  2023-04-22 19:02 ` Re: SCROLLABLE/UPDATABLE cursor question Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Garfield Lewis @ 2023-04-22 18:05 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: SCROLLABLE/UPDATABLE cursor question
  2023-04-22 18:05 SCROLLABLE/UPDATABLE cursor question Garfield Lewis <garfield.lewis@lzlabs.com>
@ 2023-04-22 19:02 ` Tom Lane <tgl@sss.pgh.pa.us>
  2023-04-24 15:25   ` Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question Garfield Lewis <garfield.lewis@lzlabs.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2023-04-22 19:02 UTC (permalink / raw)
  To: Garfield Lewis <garfield.lewis@lzlabs.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question
  2023-04-22 18:05 SCROLLABLE/UPDATABLE cursor question Garfield Lewis <garfield.lewis@lzlabs.com>
  2023-04-22 19:02 ` Re: SCROLLABLE/UPDATABLE cursor question Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-04-24 15:25   ` Garfield Lewis <garfield.lewis@lzlabs.com>
  2023-04-24 15:36     ` Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question Adrian Klaver <adrian.klaver@aklaver.com>
  2023-04-24 15:40     ` Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 7+ messages in thread

From: Garfield Lewis @ 2023-04-24 15:25 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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?

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question
  2023-04-22 18:05 SCROLLABLE/UPDATABLE cursor question Garfield Lewis <garfield.lewis@lzlabs.com>
  2023-04-22 19:02 ` Re: SCROLLABLE/UPDATABLE cursor question Tom Lane <tgl@sss.pgh.pa.us>
  2023-04-24 15:25   ` Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question Garfield Lewis <garfield.lewis@lzlabs.com>
@ 2023-04-24 15:36     ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-04-24 15:50       ` Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question Garfield Lewis <garfield.lewis@lzlabs.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Adrian Klaver @ 2023-04-24 15:36 UTC (permalink / raw)
  To: Garfield Lewis <garfield.lewis@lzlabs.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question
  2023-04-22 18:05 SCROLLABLE/UPDATABLE cursor question Garfield Lewis <garfield.lewis@lzlabs.com>
  2023-04-22 19:02 ` Re: SCROLLABLE/UPDATABLE cursor question Tom Lane <tgl@sss.pgh.pa.us>
  2023-04-24 15:25   ` Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question Garfield Lewis <garfield.lewis@lzlabs.com>
  2023-04-24 15:36     ` Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-04-24 15:50       ` Garfield Lewis <garfield.lewis@lzlabs.com>
  2023-04-24 15:57         ` Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question Garfield Lewis <garfield.lewis@lzlabs.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Garfield Lewis @ 2023-04-24 15:50 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

> 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

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question
  2023-04-22 18:05 SCROLLABLE/UPDATABLE cursor question Garfield Lewis <garfield.lewis@lzlabs.com>
  2023-04-22 19:02 ` Re: SCROLLABLE/UPDATABLE cursor question Tom Lane <tgl@sss.pgh.pa.us>
  2023-04-24 15:25   ` Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question Garfield Lewis <garfield.lewis@lzlabs.com>
  2023-04-24 15:36     ` Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question Adrian Klaver <adrian.klaver@aklaver.com>
  2023-04-24 15:50       ` Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question Garfield Lewis <garfield.lewis@lzlabs.com>
@ 2023-04-24 15:57         ` Garfield Lewis <garfield.lewis@lzlabs.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Garfield Lewis @ 2023-04-24 15:57 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

> 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

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question
  2023-04-22 18:05 SCROLLABLE/UPDATABLE cursor question Garfield Lewis <garfield.lewis@lzlabs.com>
  2023-04-22 19:02 ` Re: SCROLLABLE/UPDATABLE cursor question Tom Lane <tgl@sss.pgh.pa.us>
  2023-04-24 15:25   ` Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question Garfield Lewis <garfield.lewis@lzlabs.com>
@ 2023-04-24 15:40     ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 7+ messages in thread

From: Tom Lane @ 2023-04-24 15:40 UTC (permalink / raw)
  To: Garfield Lewis <garfield.lewis@lzlabs.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2023-04-24 15:57 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-22 18:05 SCROLLABLE/UPDATABLE cursor question Garfield Lewis <garfield.lewis@lzlabs.com>
2023-04-22 19:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-04-24 15:25   ` Garfield Lewis <garfield.lewis@lzlabs.com>
2023-04-24 15:36     ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-04-24 15:50       ` Garfield Lewis <garfield.lewis@lzlabs.com>
2023-04-24 15:57         ` Garfield Lewis <garfield.lewis@lzlabs.com>
2023-04-24 15:40     ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox