Postgres Pro
Downloads
Home   >   mailing lists

Thread: JDBC memory usage

JDBC memory usage

From
Bill Schneider
Date:
Hello,

I noticed that I keep running out of memory when trying to run a query
that returns 100,000 rows or so.

Searching the archives, it seems that the JDBC driver reads the entire
ResultSet into memory at once rather than using cursors.  This is
definitely not the desired behavior for this particular query.

Has this been fixed recently?  I'm using PostgreSQL 7.3.4 with the
corresponding JDBC driver.

Thanks!

-- Bill
--
Bill Schneider
Chief Architect

Vecna Technologies
5004 Lehigh Rd., Suite B
College Park, MD 20740
bschneider@vecna.com
t: 301-864-7253 x1140
f: 301-699-3180

Re: JDBC memory usage

From
jao@geophile.com
Date:
Quoting Bill Schneider <bschneider@vecna.com>:

> Hello,
>
> I noticed that I keep running out of memory when trying to run a query
> that returns 100,000 rows or so.
>
> Searching the archives, it seems that the JDBC driver reads the entire
> ResultSet into memory at once rather than using cursors.  This is
> definitely not the desired behavior for this particular query.
>
> Has this been fixed recently?  I'm using PostgreSQL 7.3.4 with the
> corresponding JDBC driver.

I ran into this problem. The driver developers informed me that
this problem is fixed in 7.4, and that the new driver can be used
with a 7.3 server. (This exchange should be in the pgsql-jdbc
archives.)

Jack Orenstein

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

Re: JDBC memory usage

From
Oliver Jowett
Date:
Bill Schneider wrote:
> Hello,
>
> I noticed that I keep running out of memory when trying to run a query
> that returns 100,000 rows or so.
>
> Searching the archives, it seems that the JDBC driver reads the entire
> ResultSet into memory at once rather than using cursors.  This is
> definitely not the desired behavior for this particular query.
>
> Has this been fixed recently?  I'm using PostgreSQL 7.3.4 with the
> corresponding JDBC driver.

Do all of:

- use current development drivers and a 7.4 server, or pre-build-302
drivers and a 7.2/7.3/7.4 server
- call Connection.setAutoCommit(false)
- create statements that produce resultsets of type TYPE_FORWARD_ONLY
- call Statement.setFetchSize(<positive integer>)
- use queries that consist of a single SELECT with no trailing ';' (only
a requirement if using older drivers)

Then the driver should use cursors to batch access to the resultset.

-O