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
