Re: FETCH FORWARD 0 and "cursor can only scan forward" error

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.