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

I digest this down to "this is the best that can be achieved on a connection
that's single threaded"

I think the big difference with Oracle is this:

i. in Oracle, a SELECT does not have to be a transaction, in the sense that
PG's SELECT does ... but in Oracle, a SELECT can fail mid-stream if you wait
too long and the UNDO tablespace wraps (ORA-600), i.e. Oracle does not lock
on SELECT. Oracle is optimized for lots of small transactions that typically
commit, PG supports arbitrary transaction mixes of any size, but is less
efficient at the workload for which Oracle is specialized.

ii. SELECT always creates an implicit cursor in Oracle, but access to these
cursors can be interleaved arbitrarily on one connection both with each
other and transactions (writes)

After consiering the context you offered, I'd recommend the following two
minor changes to the PG driver ....

a. Make setFetchSize(10000) the default

b. If someone does call rs.close() before the end of the ResultSet, and has
not created an explicit cursor at the JDBC level, flag the query / lock /
virtual transaction in some way in the JDBC driver that tells it that it can
just dump the cursor on a subsequent stmt.close(), conn.commit() or
conn.close() call without sucking down the rest of the data.

AFAICT, this will make the behaviour more like other DB's without sacrifcing
anything, but I don't know what default behaviour expectations might be out
there in PG land.

Cheers
Dave

On Tue, Apr 20, 2010 at 3:22 PM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:
(Lots of good explanatory stuff)