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()
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
- 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.
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.
- 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
MOVE ABSOLUTE 1will 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
MOVE ABSOLUTE 0results in an out of bound, although it is the only way to move at the beginning of the dataset.
scroll(0, 'absolute')can be special-cased,
scroll(-n, 'relative')cannot really, while we can allow
MOVE 9as this means the cursor is at the beginning, a
MOVE 0both 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.
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.