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

Re: SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Dave Crooke" <dcrooke(at)gmail(dot)com>
Cc: <pgsql-jdbc(at)postgresql(dot)org>, "pgsql-performance" <pgsql-performance(at)postgresql(dot)org>, "Scott Carey" <scott(at)richrelevance(dot)com>
Subject: Re: SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set
Date: 2010-04-20 16:32:40
Message-ID: 4BCD90D80200002500030BDD@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-jdbcpgsql-performance
Dave Crooke <dcrooke(at)gmail(dot)com> wrote:
 
> AFAICT from the Java end, ResultSet.close() is supposed to be
> final.
 
For that ResultSet.  That doesn't mean a ResultSet defines a cursor.
Such methods as setCursorName, setFetchSize, and setFetchDirection
are associated with a Statement.  Think of the ResultSet as the
result of a cursor *scan* generated by opening the cursor defined by
the Statement.
 
http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#close%28%29
 
Notice that the ResultSet is automatically closed if the Statement
that generated it is re-executed.  That is very much consistent with
Statement as the equivalent of a cursor, and not very consistent
with a ResultSet as the equivalent of a cursor.
 
> There is no way I know of in JDBC to get a handle back to the
> cursor on the server side once you have made this call - in fact,
> its sole purpose is to inform the server in a timely fashion that
> this cursor is no longer required, since the ResultSet itself is a
> Java object and thus subject to garbage collection and finalizer
> hooks.
 
Again, you're talking about the *results* from *opening* the cursor.
 
> At a pragmatic level, the PGSQL JDBC driver has a lot of odd
> behaviours which, while they may or may not be in strict
> compliance with the letter of the standard, are very different
> from any other mainstream database that I have accessed from Java
> .... what I'd consider as normative behaviour, using regular JDBC
> calls without the need to jump through all these weird hoops, is
> exhibited by all of the following: Oracle, SQL Server, DB2, MySQL,
> Apache Derby and JET (MS-Access file-based back end, the .mdb
> format)
 
Are you talking about treating the Statement object as representing
a cursor and the ResultSet representing the results from opening
the cursor, or are you thinking of something else here?
 
> In practce, this places PGSQL as the odd one out, which is a bit
> of a turn-off to expereinced Java people who are PG newbies for
> what is otherwise an excellent database.
 
Huh.  I dropped PostgreSQL into an environment with hundreds of
databases, and the applications pretty much "just worked" for us.
Of course, we were careful to write to the SQL standard and the JDBC
API, not to some other product's implementation of them. 
 
There were a few bugs we managed to hit which hadn't previously been
noticed, but those were promptly fixed.  As I recall, about the only
other things which caused me problems were:
 
(1)  Needing to setFetchSize to avoid materializing the entire
result set in RAM on the client.
 
(2)  Fixing a race condition in our software which was benign in
other products, but clearly my own bug.
 
(3)  Working around the fact that COALESCE(NULL, NULL) can't be used
everywhere NULL can.
 
> At my previous shop, we built a couple of database-backed apps
> from scratch, and despite a desire to use PG due to there being
> more certainty over its future licensing (it was just after Sun
> had bought MySQL AG), I ended up switching from PG to MySQL 5.0.47
> (last open source version) because of the difficulties I was
> having with the PG driver.
 
Just out of curiosity, did you discuss that on the PostgreSQL lists?
Can you reference the thread(s)?
 
> I consider part of the acme of great FOSS is to make it easy to
> use for newbies and thus attract a larger user base, but that is
> just my $0.02 worth.
 
Sure, but I would consider it a step away from that to follow
MySQL's interpretation of cursors rather than the standard's.
YMMV, of course.
 
-Kevin

In response to

Responses

pgsql-performance by date

Next:From: David KerrDate: 2010-04-20 17:39:36
Subject: Very high effective_cache_size == worse performance?
Previous:From: Kris JurkaDate: 2010-04-20 16:07:48
Subject: Re: SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set

pgsql-jdbc by date

Next:From: Dave CrookeDate: 2010-04-20 19:29:28
Subject: Re: SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set
Previous:From: Kris JurkaDate: 2010-04-20 16:07:48
Subject: Re: SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set

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