Postgres' implementation of cursors has always had a problem with doing MOVE or FETCH backwards on complex queries. It works okay for simple seqscans and indexscans, but fails for plans involving joins, aggregates, and probably other cases. This happens because the executor routines for those plan types don't cope with being asked to scan backwards.
Fixing this directly seems unreasonably difficult, so I'm currently working on fixing it by inserting a Materialize plan node at the top of the plan tree for a cursor, if the plan tree couldn't otherwise support backwards scan. The Materialize node will save aside a copy of each row as it's fetched from the underlying plan, and use this copied table if any backwards scanning is asked for. Of course, copying the data is a waste of cycles if the client never actually asks to scan backwards --- and it could even lead to failures, i.e. running out of disk space. So I think there needs to be a way to control whether Materialize gets added or not. The SQL spec has a provision for this: according to the spec, the client is supposed to say DECLARE foo SCROLL CURSOR FOR ... if he intends to do anything except sequential fetches from the cursor. Postgres presently allows the SCROLL keyword but ignores it. I'd like to set things up so that Materialize is added only when SCROLL appears (and the query plan can't handle backwards scan without it). However, this is going to create backwards-compatibility issues. We have a few options for what to do: 1. Enforce the SQL spec requirement: error out if backwards fetch is done when SCROLL wasn't given. But this will surely break a lot of existing applications that work perfectly well. 2. Error out only if a backwards fetch is actually attempted on a plan tree that can't handle it (which could only happen if SCROLL wasn't given). This is efficient and flexible, but it exposes implementation details to the user, in that whether an error occurs will depend on which plan the system happens to choose. There are cases where identical cursor definitions might allow or not allow backwards fetch depending on the planner's choices. Notice though that errors could occur only in cases that would silently fail in the present code; so existing applications that work reliably would not see such errors. 3. Create a runtime parameter (GUC variable) which when set causes us to assume SCROLL is present even if it's not stated. Setting this to TRUE would allow existing applications to work without modification; when it's FALSE, we'd enforce the spec behavior. The trouble with this is the TRUE setting would likely cause materialization costs to be paid in very many situations where the client has no intention of fetching backwards. I'm presently leaning to #2, even though it exposes implementation details. I'm open to discussion though. Any preferences? Other ideas? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])