Thread: absolute positioning
Hi, After reviewing the JDBC API, I thought absolute() was intended to reposition the cursor across the entire result set. Looking at the pgjdbc implementation though, it appears to be positioning the cursor in the cached results, which may be a subset of all results if setFetchSize() != 0. I assume the implementation should be doing "FETCH ABSOLUTE pos". If this is true, comparable methods are also not correctly implemented for this case. Is this an issue, or am I misinterpreting the API? thanks, Indra
Indra Heckenbach wrote: > Hi, > > After reviewing the JDBC API, I thought absolute() was intended to > reposition the cursor across the entire result set. Looking at the > pgjdbc implementation though, it appears to be positioning the cursor in > the cached results, which may be a subset of all results if > setFetchSize() != 0. I assume the implementation should be doing > "FETCH ABSOLUTE pos". If this is true, comparable methods are also not > correctly implemented for this case. Is this an issue, or am I > misinterpreting the API? You need to be using a scrollable resultset for absolute() to be available (although it looks like the current driver does not enforce this). If a scrollable resultset is requested, the driver disables the use of cursors regardless of the current fetchsize, so the "cached" resultset is always the complete resultset and there is no problem. This behaviour is an interim measure until a proper implementation that deals with scrollable resultsets and cursors correctly gets done. There are some backend issues to sort out first -- some (non-obvious set of) queries produce forward-access-only cursors, IIRC. -O
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? thanks, Indra Oliver Jowett wrote: > Indra Heckenbach wrote: > >> Hi, >> >> After reviewing the JDBC API, I thought absolute() was intended to >> reposition the cursor across the entire result set. Looking at the >> pgjdbc implementation though, it appears to be positioning the cursor >> in the cached results, which may be a subset of all results if >> setFetchSize() != 0. I assume the implementation should be doing >> "FETCH ABSOLUTE pos". If this is true, comparable methods are also >> not correctly implemented for this case. Is this an issue, or am I >> misinterpreting the API? > > > You need to be using a scrollable resultset for absolute() to be > available (although it looks like the current driver does not enforce > this). If a scrollable resultset is requested, the driver disables the > use of cursors regardless of the current fetchsize, so the "cached" > resultset is always the complete resultset and there is no problem. > > This behaviour is an interim measure until a proper implementation > that deals with scrollable resultsets and cursors correctly gets done. > There are some backend issues to sort out first -- some (non-obvious > set of) queries produce forward-access-only cursors, IIRC. > > -O >
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
Hi Oliver, <br /><br /> Thanks for pointing me to this discussion. From what I can tell, work must have been done on thepostgres backend to improve support for cursors. I have been using cursors with fairly complex queries (joins and nestedsubqueries and order bys) without any problems. <br /><br /> I went ahead and patched the JDBC jar from the CVS headon gborg. This patch should enable scrollable cursor support, and provide absolute() w/o loading all results into theJDBC buffer. Unfortunately, the patch involves changing some core aspects of the driver. I have not reviewed the existingcode thoroughly, so I could have introduced problems. I have also not tested extensively. However, the patch worksfor me, and I could use the patched driver with existing applications. Is there a test suite for the driver? At thispoint I hope to get some feedback.<br /><br /> Here is a summary of the patch:<br /> AbstractJdbc1ResultSet<br /> - added "buffer_row" and "cursor_row" to track cursor and buffer position. remove "current_row", and use theseinstead.<br /> - use a new variable "needsRefresh" to control reload state explicitly<br /> - convertnext() method to load on demand instead of pre-load. the former method does not work with cursor positioning, orwould require a hack to load data twice, and support multiple modes of loading.<br /><br /> Abstract1Jdbc1Statement<br/> - to prevent pre-load, I made the while loop over reallyResultSet() occur only if cursorsare disabled. I don't think this is needed in this context for cursors, but I'm not certain.<br /> - addedDECLARE SCROLL CURSOR to handle scrollable case.<br /><br /> AbstractJdbc2ResultSet<br /> - use a new variable"internalIndex" in AbstractJdbc2ResultSet to track absolute position, to avoid overlapping repositioning.<br /> - implement absolute() as FETCH ABSOLUTE. relative() uses this code, so it should be supported as well.<br /><br/> thanks,<br /> Indra<br /><br /> Oliver Jowett wrote:<br /><blockquote cite="mid405E5CDF.4000308@opencloud.com" type="cite">IndraHeckenbach wrote: <br /><blockquote type="cite">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? <br /></blockquote><br /> There was a discussion aboutimplementing this a couple of months ago that might be useful to you, starting at: <a class="moz-txt-link-freetext" href="http://archives.postgresql.org/pgsql-jdbc/2004-01/msg00288.php">http://archives.postgresql.org/pgsql-jdbc/2004-01/msg00288.php</a><br /><br/> -O <br /><br /></blockquote><br />
Indra Heckenbach wrote:
> 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.
Context diff ('cvs diff -c') output is preferred as it's a lot more
readable..
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.
There is indeed a test suite -- try 'ant test'. You'll need the JUnit
jar available in Ant's classpath or it fails silently (we should fix
that..). You also need a database called 'test' and a user called 'test'
on a server running on localhost (these are tweakable -- see build.xml
for details). The test cases are in org/postgresql/test.
With your patch I get a couple of failures from the test suite that I
don't get with CVS HEAD:
> [junit] Testcase: testScrollableFetch(org.postgresql.test.jdbc2.CursorFetchTest): FAILED
> [junit] ran out of rows doing a reverse fetch on iteration 1/0 at position 49 with fetch size 1
> [junit] junit.framework.AssertionFailedError: ran out of rows doing a reverse fetch on iteration 1/0 at position
49with fetch size 1
> [junit] at org.postgresql.test.jdbc2.CursorFetchTest.testScrollableFetch(CursorFetchTest.java:100)
> [junit] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> [junit] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> [junit] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> [junit] Testcase: testScrollableAbsoluteFetch(org.postgresql.test.jdbc2.CursorFetchTest): Caused an ERROR
> [junit] Result set not positioned properly, perhaps you need to call next().
> [junit] org.postgresql.util.PSQLException: Result set not positioned properly, perhaps you need to call next().
> [junit] at org.postgresql.jdbc1.AbstractJdbc1ResultSet.checkResultSet(AbstractJdbc1ResultSet.java:859)
> [junit] at org.postgresql.jdbc1.AbstractJdbc1ResultSet.getString(AbstractJdbc1ResultSet.java:222)
> [junit] at org.postgresql.jdbc1.AbstractJdbc1ResultSet.getFixedString(AbstractJdbc1ResultSet.java:823)
> [junit] at org.postgresql.jdbc1.AbstractJdbc1ResultSet.getInt(AbstractJdbc1ResultSet.java:310)
> [junit] at org.postgresql.test.jdbc2.CursorFetchTest.testScrollableAbsoluteFetch(CursorFetchTest.java:127)
> [junit] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> [junit] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> [junit] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
-O
On Wed, 24 Mar 2004, Indra Heckenbach wrote: > 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. > I'll admit I haven't actually looked at the patch, but first, patches should be submitted in context diff format (diff -c) and there is a test suite that can be run with "ant test", this requires setting up a test database with a test user and making plpgsql available to it. Right now there are two failures from your patch. Also this is only testing the existing functionality, most of the tests are testing the FORWARD_ONLY ResultSet mode, so I would hope to see some additions or modifications to the test suite in your next patch. Kris Jurka
Thanks for the notes on junit testing. I neglected previous() method entirely. I've extended that, and still working throughsome issues. I don't know what I'll have time for, but I would like to complete support for it. I'll let you knowhow things develop...<br /><br /> Indra<br /><br /> Kris Jurka wrote:<br /><blockquote cite="midPine.BSO.4.56.0403241257030.18530@leary.csoft.net"type="cite"><pre wrap=""> On Wed, 24 Mar 2004, Indra Heckenbach wrote: </pre><blockquote type="cite"><pre wrap="">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. </pre></blockquote><pre wrap=""> I'll admit I haven't actually looked at the patch, but first, patches should be submitted in context diff format (diff -c) and there is a test suite that can be run with "ant test", this requires setting up a test database with a test user and making plpgsql available to it. Right now there are two failures from your patch. Also this is only testing the existing functionality, most of the tests are testing the FORWARD_ONLY ResultSet mode, so I would hope to see some additions or modifications to the test suite in your next patch. Kris Jurka </pre></blockquote><br />