#174 ✓ hold
Tony Tan

server side curosr scrolling out of bound does not raise any Exception (os-linux)

Reported by Tony Tan | August 15th, 2013 @ 03:16 PM

Hello I tried using psycopg's server side cursor scroll() function
When I scroll it with a out of bound value, it does not raise an Exception as the psycopg2 document states it should raise either a psycopg2.ProgrammingError or IndexError.
Instead it allows me scroll to that location and get a None result.

CentoOS Linux, psycopg2.5 (dt dec pq3 ext), postgresql 9.1

Comments and changes to this ticket

  • Daniele Varrazzo

    Daniele Varrazzo August 20th, 2013 @ 09:50 AM

    • State changed from “new” to “open”

    You are right: scrolling out-of-bound should raise an exception as per DBAPI.

    Detecting an out-of-bound movement seems easy enough looking at the status (MOVE n) returned by the MOVE command. MOVE ABSOLUTE n seems returning MOVE 1 or MOVE 0 in case of OOB. MOVE n (relative) returns MOVE x where x = abs(n) if we are still within the boundaries and x < abs(n) if we are not.

    The problem I see is that the specs say we should not move if we go OOB. This is sort of easy to do with relative movement: from a test I'm running, if we wanted to go ahead 10 and we find we went ahead 6, we should MOVE -7 and raise an exception. Similarly moving backwards, if we MOVE -10 and we get "MOVE 4" instead we must MOVE 5 to go back in place. The problem is with absolute movements: if we move and we get "MOVE 0" we don't know where to move back: we should keep the current position in the state and keep it up-to-date at every FETCH and MOVE command we pass internally. From postgres doc I don't see any command to query the current position of a cursor.

  • Daniele Varrazzo

    Daniele Varrazzo August 20th, 2013 @ 11:14 AM

    Looking back the specs, they say:

    An IndexError should be raised in case a scroll operation would leave
    the result set.  In this case, the cursor position is left undefined
    (ideal would be to not move the cursor at all).

    When I converted the dbapi specs into the psycopg docs I changed it into:

    If the scroll operation would leave the result set, a ProgrammingError
    is raised and the cursor position is not changed.

    but that's only the client-side cursor behaviour: server-side cursor moves.

    So restoring the position is not actually needed by DBAPI spec: the doc should be fixed by declaring the new position undefined.

  • Daniele Varrazzo

    Daniele Varrazzo October 16th, 2013 @ 05:46 PM

    • State changed from “open” to “hold”

    I've tried implementing this feature, but is more problematic than expected, because what returned by MOVE is actually contrived. Well, it's actually easy, but it doesn't help: it is the result of what FETCH would have returned - and records are base-1 in postgres.

    This means that scroll(1, 'absolute') -> MOVE ABSOLUTE 1 will select (and discard) the first record and move the pointer on the 2nd record, which is accidentally what expected in Python (base-0): go between the 1st and the 2nd record. Unfortunately this means that scroll(0, 'absolute') -> MOVE ABSOLUTE 0 results in an out of bound, although it is the only way to move at the beginning of the dataset.

    While scroll(0, 'absolute') can be special-cased, scroll(-n, 'relative') cannot really, while we can allow scroll(-10) to return MOVE 9 as this means the cursor is at the beginning, a scroll(-1) can return MOVE 0 both when the record was on the 2nd item (hence a valid move) but also when the record was on the 1st item (hence an out of bound).

    I've left the attempted branch open as named_cursor_oob, the last commit implementation is https://github.com/dvarrazzo/psycopg/commit/882eceda41043ddf86c6f8a...

    I will document that server-side cursor don't behave like client-side one; specifically we just forward to postgres the client request and rely on its behaviour.

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

Psycopg is the most used PostgreSQL adapter for the Python programming language. At the core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.

Shared Ticket Bins