Skip site navigation (1) Skip section navigation (2)

Re: absolute positioning

From: Indra Heckenbach <indra(at)macnica(dot)com>
To: Oliver Jowett <oliver(at)opencloud(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: absolute positioning
Date: 2004-03-24 07:29:36
Message-ID: 406138E0.8090501@macnica.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Hi Oliver,



Thanks for pointing me to this discussion. From what I can
tell, work must have been done on the postgres backend to improve
support for cursors. I have been using cursors with fairly
complex queries (joins and nested subqueries and order bys) without
any problems.



I went ahead and patched the JDBC jar from the CVS head on
gborg. This patch should enable scrollable cursor support,
and provide absolute() w/o loading all results into the JDBC
buffer. Unfortunately, the patch involves changing some core
aspects of the driver. I have not reviewed the existing code
thoroughly, so I could have introduced problems. I have also
not tested extensively. However, the patch works for me, and
I could use the patched driver with existing applications. Is
there a test suite for the driver? At this point I hope to
get some feedback.



Here is a summary of the patch:

 AbstractJdbc1ResultSet

  - added "buffer_row" and
"cursor_row" to track cursor and buffer position. remove
"current_row", and use these instead.

  - use a new variable
"needsRefresh" to control reload state explicitly

  - convert next() method to
load on demand instead of pre-load. the former method does
not work with cursor positioning, or would require a hack to load
data twice, and support multiple modes of loading.



 Abstract1Jdbc1Statement

  - to prevent pre-load, I made
the while loop over reallyResultSet() occur only if cursors are
disabled. I don't think this is needed in this context for
cursors, but I'm not certain.

  - added DECLARE SCROLL CURSOR
to handle scrollable case.



 AbstractJdbc2ResultSet

  - use a new variable
"internalIndex" in AbstractJdbc2ResultSet to track absolute
position, to avoid overlapping repositioning.

  - implement absolute() as
FETCH ABSOLUTE. relative() uses this code, so it should be
supported as well.



thanks,

Indra



Oliver Jowett wrote:

Indra
Heckenbach wrote:

Thanks for the info. So the implementation is
functional, but does so by preventing cursors. Unfortunately,
I am dealing with a large result set, so I need to use
cursors. I started modifying the jdbc driver. Any other
suggestions?



There was a discussion about implementing this a couple of months
ago that might be useful to you, starting at: http://archives.postgresql.org/pgsql-jdbc/2004-01/msg00288.php




-O






Attachment: cursor_patch.diff.gz
Description: application/x-gzip (2.1 KB)
Attachment: unknown_filename
Description: text/html (3.3 KB)

In response to

Responses

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2004-03-24 09:16:46
Subject: Re: absolute positioning
Previous:From: dinakarDate: 2004-03-24 04:57:35
Subject: Re: Postmaster not dropping connections on tomcat restart.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group