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

When executing a large query, I am to understand that for server-side
cursors to be used (so to not run out of memory by loading the entire
result set into memory at once) I need to specify
ResultSet.TYPE_FORWARD_ONLY and then stmt.setFetchSize(x).

However, I'd like to be able to jump to the Xth result and display only Y
results, i.e. support pages of results and allow jumping to arbitrary
pages and thus arbitrary offsets in the result query. In code I used to do
set ResultSet.TYPE_SCROLL_INSENSITIVE, stmt.setFetchSize(Y), and then
rs.relative(X) where stmt is a Statement and rs is a ResultSet.

This causes the entire result set to get loaded into memory. To use a
FORWARD_ONLY result, I can't use ResultSet.relative() anymore. Is there
any alternative, other than perhaps calling ResultSet.next() X times
before returning the results? For very large results (millions of rows)
this seems like a slow solution, as I assume that all results will
actually get passed through JDBC just to jump to the desired row.

-- m@

Matt Magoffin wrote:

> However, I'd like to be able to jump to the Xth result and display only Y
> results, i.e. support pages of results and allow jumping to arbitrary
> pages and thus arbitrary offsets in the result query.

I'd suggest using LIMIT/OFFSET in your query.

-O

> I'd suggest using LIMIT/OFFSET in your query.
>
> -O

Hmm, I had thought of that but was hoping to stay just in generic JDBC,
without Postgres specific SQL changes.

-- m@

> That is the sick of Java developers when dealing with databases, do take
> advantages of the RDMS engine.

That's great for Postgres-specific apps, sure. But when writing general
apps that need to work with a variety of database systems, that just makes
for more work. This kind of stuff often can/should be handled in the JDBC
driver.

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.

-- m@

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

> 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

Yes, that was the thread I spotted before, thanks for finding that. It's a
bummer that the current protocol won't cleanly support scrollable result
sets! I'd be glad to help out in any way possible for this feature... even
if it's just testing.

-- m@

Matt Magoffin wrote:
>> I'd suggest using LIMIT/OFFSET in your query.
>>
>> -O
>
> Hmm, I had thought of that but was hoping to stay just in generic JDBC,
> without Postgres specific SQL changes.

Doing it in the query does have the advantage that the server can
potentially come up with a better query plan.. assuming that it's common
to page through less than the full query output.

-O