The following bug has been logged online:

Bug reference: 1240
Logged by: Roland Walter

Email address: rwa (AT) mosaic-ag (DOT) 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));





---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

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 (AT) cri (DOT) ensmp.fr

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Fabien COELHO schrieb:


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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly