Re: very large result sets and ResultSet.relative() to jump to a desired offset

On Thu, 25 Sep 2008, Matt Magoffin wrote:

> I remember stumbling on a mailing list article that mentioned some work
> done in a 7.5 branch to support server-side cursors with scrollable
> results sets, which is the actual source of the problem here. I can't
> seem to find that message now... was there some work done for this
> previously? If I could see that as a starting point, I'd try to work on
> support for this in the 8.3 branch code.

A patch was posted here that almost worked, but never quite got there.

http://archives.postgresql.org/pgsql-jdbc/2004-05/threads.php#00004

For the 8.0 release, how server side cursors work was completely
rewritten, so that patch isn't terribly relevent anymore.  The patch was
based on the fact that server side cursors were implemented by the driver
issuing explicit SQL level DECLARE CURSOR commands and could easily be
extended to add a SCROLL option and could then move around in it by
issuing more SQL commands to fetch the data it wanted.  With the 8.0
release we no longer do anything like that at the SQL level and do it
entirely at the protocol level.  Unfortunately the protocol level "portal"
which is equivalent to a cursor does not allow you to specify the
scrollable attribute or let you do anything other than retrieve results
going forward.

The JDBC team is hoping that the next protocol version will contain
support for more portal control and navigation features so that scrollable
resultsets can be cleanly built upon the existing structure.  Who knows
when that protocol change might happen though...

Perhaps it is possible to adapt the original patch without throwing away
all of the work to use portals, but it's not something I've investigated.

Kris Jurka