Thread: FETCH FORWARD 0 and "cursor can only scan forward" error
Hi, I'm on PostgreSQL 9.2.2 and trying to use no scroll cursor for some data fetch. But when moving cursor ahead one record andthe fetching the actual record the error "cursor can only scan forward" is returned. I don't know if I'm doing somethingwrong but I don't think I'm going backward with cursor. Here is the code that demonstrate it BEGIN; DECLARE CUR_1 NO SCROLL CURSOR WITHOUT HOLD FOR SELECT * FROM GENERATE_SERIES(1, 2); FETCH FORWARD 0 FROM CUR_1; -- 0 MOVE FORWARD FROM CUR_1; FETCH FORWARD 0 FROM CUR_1; -- 1 ABORT; The line marked as "-- 0" is working OK, the line marked as "-- 1" is throwing error: ERROR: cursor can only scan forward HINT: Declare it with SCROLL option to enable backward scan. ********** Error ********** ERROR: cursor can only scan forward SQL state: 55000 Hint: Declare it with SCROLL option to enable backward scan. I want to iterate all records with cursor from beginning to end. This sample could be rewritten using FETCH FORWARD 1 ...without using MOVE but I'm interested with solution which throws error. Thank you Trigve
Hi Trigve,
Il 04/09/2013 15:06, Trigve Siver ha scritto:
Il 04/09/2013 15:06, Trigve Siver ha scritto:
Hi, I'm on PostgreSQL 9.2.2 and trying to use no scroll cursor for some data fetch. But when moving cursor ahead one record and the fetching the actual record the error "cursor can only scan forward" is returned. I don't know if I'm doing something wrong but I don't think I'm going backward with cursor. Here is the code that demonstrate it BEGIN; DECLARE CUR_1 NO SCROLL CURSOR WITHOUT HOLD FOR SELECT * FROM GENERATE_SERIES(1, 2); FETCH FORWARD 0 FROM CUR_1; -- 0 MOVE FORWARD FROM CUR_1; FETCH FORWARD 0 FROM CUR_1; -- 1 ABORT; The line marked as "-- 0" is working OK, the line marked as "-- 1" is throwing error: ERROR: cursor can only scan forward HINT: Declare it with SCROLL option to enable backward scan. ********** Error ********** ERROR: cursor can only scan forward SQL state: 55000 Hint: Declare it with SCROLL option to enable backward scan. I want to iterate all records with cursor from beginning to end. This sample could be rewritten using FETCH FORWARD 1 ... without using MOVE but I'm interested with solution which throws error.
When you fetch a record you move inherently the cursor to the next position relative to the last fetched record. Consider this example:
BEGIN
postgres=# DECLARE CUR_1 CURSOR WITHOUT HOLD FOR SELECT * FROM GENERATE_SERIES(1, 10);
DECLARE CURSOR
postgres=# FETCH FORWARD 1 FROM CUR_1;
generate_series
-----------------
1
(1 row)
postgres=# FETCH FORWARD 1 FROM CUR_1;
generate_series
-----------------
2
(1 row)
postgres=# FETCH FORWARD 0 FROM CUR_1;
generate_series
-----------------
2
(1 row)
If you specify "FORWARD 0" you move ahead of zero places instead of one; therefore you obtain the same record *that was yet fetched* by the previous FETCH statement. If the cursor is declared with the NO ROLL options, this operation is forbidden, and an error is raised, as in your case.
Giuseppe.
-- Giuseppe Broccolo - 2ndQuadrant Italy PostgreSQL Training, Services and Support giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it
>________________________________ > From: Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it> >To: pgsql-general@postgresql.org >Sent: Wednesday, September 4, 2013 6:26 PM >Subject: Re: [GENERAL] FETCH FORWARD 0 and "cursor can only scan forward" error > > > >Hi Trigve, > >Il 04/09/2013 15:06, Trigve Siver ha scritto: > >Hi, I'm on PostgreSQL 9.2.2 and trying to use no scroll cursor for some data fetch. But when moving cursor ahead one record andthe fetching the actual record the error "cursor can only scan forward" is returned. I don't know if I'm doing somethingwrong but I don't think I'm going backward with cursor. Here is the code that demonstrate it BEGIN; DECLARE CUR_1NO SCROLL CURSOR WITHOUT HOLD FOR SELECT * FROM GENERATE_SERIES(1, 2); FETCH FORWARD 0 FROM CUR_1; -- 0 MOVE FORWARDFROM CUR_1; FETCH FORWARD 0 FROM CUR_1; -- 1 ABORT; The line marked as "-- 0" is working OK, the line marked as "--1" is throwing error: ERROR: cursor can only scan forward HINT: Declare it with SCROLL option to enable backward scan. ********** Error ********** ERROR: cursor can only scan forward SQL state: 55000 Hint: Declare it with SCROLL option to enable backward scan. I want to iterate all records with cursor from beginning toend. This sample could be rewritten using FETCH FORWARD 1 ... without using MOVE but I'm interested with solution whichthrows error. >When you fetch a record you move inherently the cursor to the next position relative to the last fetched record. Consider this example: > >postgres=# BEGIN; >BEGIN >postgres=# DECLARE CUR_1 CURSOR WITHOUT HOLD FOR SELECT * FROM GENERATE_SERIES(1, 10); >DECLARE CURSOR >postgres=# FETCH FORWARD 1 FROM CUR_1; > generate_series >----------------- > 1 >(1 row) > >postgres=# FETCH FORWARD 1 FROM CUR_1; > generate_series >----------------- > 2 >(1 row) > >postgres=# FETCH FORWARD 0 FROM CUR_1; > generate_series >----------------- > 2 >(1 row) > > >If you specify "FORWARD 0" you move ahead of zero places instead of one; therefore you obtain the same record *that was yet fetched* by the previous FETCH statement. If the cursor is declared with the NO ROLL options, this operation is forbidden, and an error is raised, as in your case. > Thanks for reply, yes I know that when specifying "FORWARD 0" that no move is done and actual record is returned. But my question is why itis forbidden by "NO SCROLL"? Does it mean that FORWARD 0 is "marked" as nonsequential operation (does it move back or why?)aswritten in documentation that NO SCROLL cursor "... cannot be used to retrieve rows in a nonsequential fashion..."?Now when looking at the documentation from FETCH command "...The cursor should be declared with the SCROLL optionif one intends to use any variants of FETCH other than FETCH NEXT or FETCH FORWARD with a *positive count*.", thereis stated that only positive count could be used with NO SCROLL. But why not also 0? Because how I get it is that when I make FETCH FORWARD 1 cursor will move to the next record, and returns it. When I thencall FETCH FORWARD 0 it should only return the actual row cursor is at, no move or whatsoever. Or am I mistaken somehow? Thank you > > >Giuseppe. > >-- Giuseppe Broccolo - 2ndQuadrant Italy PostgreSQL Training, Services and Support giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it > Trigve
Trigve Siver <trigves@yahoo.com> writes:
> yes I know that when specifying "FORWARD 0" that no move is done and
> actual record is returned. But my question is why it is forbidden by "NO
> SCROLL"?
Because it's implemented as "back up 1 row and re-fetch".
regards, tom lane
> From: Tom Lane <tgl@sss.pgh.pa.us> > To: Trigve Siver <trigves@yahoo.com> > Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org> > Sent: Wednesday, September 4, 2013 7:04 PM > Subject: Re: [GENERAL] FETCH FORWARD 0 and "cursor can only scan forward" error > >T rigve Siver <trigves@yahoo.com> writes: >> yes I know that when specifying "FORWARD 0" that no move is done > and >> actual record is returned. But my question is why it is forbidden by > "NO >> SCROLL"? > > Because it's implemented as "back up 1 row and re-fetch". Thanks, So I'll try to emulate it somehow then. > regards, tom lane > Trigve
Trigve Siver wrote > I want to iterate all records with cursor from beginning to end. This > sample could be rewritten using FETCH FORWARD 1 ... without using MOVE but > I'm interested with solution which throws error. Is you interest purely academic or is there some reason you were evaluating this particular combination of commands? I find the fact that the implementation detail behind "FORWARD 0" causing it to only be useful in a scroll-able cursor to be unusual but lacking any concrete use-cases as to why "FORWARD 0" is nominally useful - let alone in a scroll-forward-only situation - convincing someone to change the behavior is difficult. While the following sentence is technically accurate: "The cursor should be declared with the SCROLL option if one intends to use any variants of FETCH other than FETCH NEXT or FETCH FORWARD with a positive count." http://www.postgresql.org/docs/9.2/static/sql-fetch.html It is not intuitively obvious to myself or the OP that "FETCH FORWARD 0" is going to require a back-scan and thus MUST (not should) be declared with "SCOLL" (or, technically, not NOT SCROLL). It may be worth an extra sentence immediately following the one above: "Note that the combination <FORWARD 0> causes a back-scan and thus may only be used in combination with a scrollable cursor." Alternatively (or in addition) where "FORWARD 0" is defined this comment exists: "Fetch the next count rows. FORWARD 0 re-fetches the current row." could be re-written "Fetch the next count rows. The special-case FORWARD 0 requires a scroll-able cursor and causes the current row to be re-fetched." Given that it is not obvious "FORWARD 0" should even work (as defined it should always return zero rows) limiting the scroll-able comment to just the section where its behavior is defined is likely sufficient. Thoughts? Any comments on why it shouldn't work in a scroll-forward only situation. Re-returning the same row again may technically be considered "re-visiting the same record" which is what is being disallowed but if "0" is special-cased anyway it shouldn't be that difficult to return a cached result of whatever last came out of the cursor. Not sure its worth the time to code and test but is there some philosophical (or standards-based) reason such an action should be prohibited? David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/FETCH-FORWARD-0-and-cursor-can-only-scan-forward-error-tp5769538p5769630.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
David Johnston <polobo <at> yahoo.com> writes: > > Trigve Siver wrote > > I want to iterate all records with cursor from beginning to end. This > > sample could be rewritten using FETCH FORWARD 1 ... without using MOVE but > > I'm interested with solution which throws error. > > Is you interest purely academic or is there some reason you were evaluating > this particular combination of commands? I was just using this command in some iterator code where I only fetched the row on iterator dereferencing. So wanted to have solo operation for "move" and for "fetch" command. > I find the fact that the implementation detail behind "FORWARD 0" causing it > to only be useful in a scroll-able cursor to be unusual but lacking any > concrete use-cases as to why "FORWARD 0" is nominally useful - let alone in > a scroll-forward-only situation - convincing someone to change the behavior > is difficult. > [...] > > Any comments on why it shouldn't work in a scroll-forward only situation. > Re-returning the same row again may technically be considered "re-visiting > the same record" which is what is being disallowed but if "0" is > special-cased anyway it shouldn't be that difficult to return a cached > result of whatever last came out of the cursor. Not sure its worth the time > to code and test but is there some philosophical (or standards-based) reason > such an action should be prohibited? I agree with you and was asking the same question in mind when writing the first message. > David J. > Trigve