Tom, > Postgres' implementation of cursors has always had a problem with doing > MOVE or FETCH backwards on complex queries.
Coincidnetally enough, I was just chatting with one of my contractors yesterday about how the one thing that Transact-SQL has to offer is a really good cursor implementation. It would be lovely to improve ours to match. > 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. Sounds good to me. It's also very similar to what T-SQL does for a STATIC or KEYSET cursor, and works very well in their implementation. (FWIW, T-SQL's cursor types, such as DYNAMIC and KEYSET, are unnecessary for Postgres due to MVCC) > 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 <snip> > I'm presently leaning to #2, even though it exposes implementation > details. I'm open to discussion though. Any preferences? Other ideas? This sounds like a good idea to me in a staggered-implementation sense if it's doable. That is, we'd implement the behavior in #2 in the next version of Postgresql, and the behavior in #1 or in #3 in the version after that. If, however, the implementation of #2 is too difficult, then I think #3 would be a good choice. >From my perspective, the "SCROLL" declaration has *always* been the SQL-spec, and it is the behaviour used by other databases, even if it's been superflous in PostgreSQL until now. So from that point of view, developers who have been not using "SCROLL" have been sloppy and can reasonably expect to have to audit their code in future versions of PostgreSQL. On the other hand, I don't use cursors much in Postgres, so I'm kind of a priest doing marriage counselling as far as that's concerned. PL/pgSQL's "FOR record IN query" is currently both easier and faster than cursors so I use that 90% of the time. -- Josh Berkus Aglio Database Solutions San Francisco ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster