Thread: BUG #1240: memory leak in JDBC driver build 215

The following bug has been logged online:

Bug reference:      1240
Logged by:          Roland Walter

Email address:      rwa@mosaic-ag.com

PostgreSQL version: 7.4.3

Operating system:   SuSE Linux 9.0, Windows CYGWIN, J2SDK 1.4.2_x

Description:        memory leak in JDBC driver build 215

Details:

Hello!

I used the JDBC driver build 215 that was given by postgresql 7.4.5. (The
webinterface for bug reports
has not the version 7.4.5 yet.)

I wanted to execute the following statement with JDBC on a database table
named transaction with 3.945.773 rows:

SELECT * FROM transaction WHERE transaction_date >=
to_timestamp('01.01.2002', 'DD.MM.YYYY') AND transaction_date <
to_timestamp('01.01.2003', 'DD.MM.YYYY')

But I got the following error message from the JVM:

java.lang.OutOfMemoryError

Here the java-code:

stmt = con.createStatement();
log.debug("executing SQL-Stmt: " + stmtString);
rs = stmt.executeQuery(stmtString);
log.debug("getting metadata");
ResultSetMetaData rsMeta = rs.getMetaData();

And here the output I got:

328   [main] DEBUG com.mosaicag.rwa.dbutil.standard.DefaultCsvExport  -
executing SQL-Stmt: SELECT * FROM transaction WHERE transaction_date >=
to_timestamp('01.01.2002', 'DD.MM.YYYY') AND transaction_date <
to_timestamp('01.01.2003', 'DD.MM.YYYY')
java.lang.OutOfMemoryError
Exception in thread "main"

As you can see, the log.debug("getting metadata"); was
never reached. A count with the same where-clause gave 387.665 rows, that
should have been iterated with a
while(rs.next()) loop. The JVM used the standard heap size, there were no
big memory allocations in the previous code.

The table definition is as follows:

create table transaction (
transaction_id bigint not null,
bc_id bigint,
recipient_iln varchar(20) not null,
transaction_date timestamp,
transaction_type varchar(20),
transaction_state varchar(20),
productive char(2),
remark varchar(255),
origin char(2),
cc_trans_id numeric(38),
constraint xpktransaction primary key (transaction_id));

> 328   [main] DEBUG com.mosaicag.rwa.dbutil.standard.DefaultCsvExport  -
> executing SQL-Stmt: SELECT * FROM transaction WHERE transaction_date >=
> to_timestamp('01.01.2002', 'DD.MM.YYYY') AND transaction_date <
> to_timestamp('01.01.2003', 'DD.MM.YYYY')
> java.lang.OutOfMemoryError
> Exception in thread "main"

Maybe the JDBC drivier tries to allocate the whole result of the query?
If so, it is not a memory leak, it's a big memory need;-)

You might try using a cursor manually (well, if it is the problem, then it
just shows that jdbc should do it by default). Something like:

DECLARE CURSOR foo FOR your-select-query...;
FETCH FORWARD 10 FROM foo;
FETCH FORWARD 10 FROM foo;
FETCH FORWARD 10 FROM foo;
...
CLOSE foo;

Hope this help.

--
Fabien Coelho - coelho@cri.ensmp.fr

Fabien COELHO schrieb:

>
>> 328   [main] DEBUG com.mosaicag.rwa.dbutil.standard.DefaultCsvExport  -
>> executing SQL-Stmt: SELECT * FROM transaction WHERE transaction_date >=
>> to_timestamp('01.01.2002', 'DD.MM.YYYY') AND transaction_date <
>> to_timestamp('01.01.2003', 'DD.MM.YYYY')
>> java.lang.OutOfMemoryError
>> Exception in thread "main"
>
>
> Maybe the JDBC drivier tries to allocate the whole result of the query?
> If so, it is not a memory leak, it's a big memory need;-)
>
> You might try using a cursor manually (well, if it is the problem,
> then it just shows that jdbc should do it by default).


Using a cursor avoids the out of memory error. I used:

stmt.setFetchSize(1000);

after creation of the statement now.

Thanks, that helped. But for me this behaviour is still a bug.

--
Roland Walter
MOSAIC SOFTWARE AG
Telefon: 02225/882-411 Fax: 02225/882-201
http://www.mosaic-ag.com