PostgreSQL JDBC - table full scan

Author

table full scan

Khaldoun Ateyeh

2006-01-05, 7:23 am

Hi,
I am using jdbc to full scan a huge table (over 11 million records).
Unfortunately, I get an OutOfMemoryError exception. Is there any way to
tell postgre not to try to load the whole table at once?

Best Regards.

Khaldoun

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Kris Jurka

2006-01-05, 7:23 am

On Thu, 5 Jan 2006, Khaldoun Ateyeh wrote:

> I am using jdbc to full scan a huge table (over 11 million records).
> Unfortunately, I get an OutOfMemoryError exception. Is there any way to
> tell postgre not to try to load the whole table at once?
>

http://jdbc.postgresql.org/document...ery-with-cursor

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Roland Walter

2006-01-05, 7:23 am

"Khaldoun Ateyeh" <Khaldoun.Ateyeh@panoratio.de> writes:

> Hi,
> I am using jdbc to full scan a huge table (over 11 million records).
> Unfortunately, I get an OutOfMemoryError exception. Is there any way to
> tell postgre not to try to load the whole table at once?
>

Set autocommit to false, then use the method setFetchSize() of
the Statement to set the maximal rows that are read into the memory.

For example:

con. setAutoCommit(false)
;
stmt = con.createStatement();
stmt.setFetchSize(1000);
--
Roland Walter mailto: rwa (at) mosaic-ag (dot) com
MOSAIC SOFTWARE AG phone: +49 (0) 22 25 / 88 2-41 1
Am Pannacker 3 fax: +49 (0) 22 25 / 88 2-20 1
D-53340 Meckenheim http://www.mosaic-ag.com

Die in dieser E-Mail enthaltenen Nachrichten und Anhaenge sind ausschliesslich
fuer den bezeichneten Adressaten bestimmt. Sie koennen rechtlich geschuetzte,
vertrauliche Informationen enthalten. Falls Sie nicht der bezeichnete Empfaenger
oder zum Empfang dieser E-Mail nicht berechtigt sind, ist die Verwendung,
Vervielfaeltigung oder Weitergabe von Nachrichten und Anhaengen untersagt.
Falls Sie diese E-Mail irrtuemlich erhalten haben, informieren Sie bitte
unverzueglich den Absender und vernichten Sie die E-Mail.

This e-mail message and any attachment are intended exclusively for the named
addressee. They may contain confidential information which may also be protected
by professional secrecy. Unless you are the named addressee (or authorised to
receive for the addressee) you may not copy or use this message or any attachment
or disclose the contents to anyone else. If this e-mail was sent to you by mistake
please notify the sender immediately and delete this e-mail.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Khaldoun Ateyeh

2006-01-05, 7:23 am

Hi,

Which driver should I use? Because when I do what you suggested, I get
"This method is not yet implemented" ... stmt.setFetchSize(1000).

I am using the official postgresql-8.1-404.jdbc3.jar driver.

Many thanks.

Khaldoun

-----Original Message-----
From: Roland Walter & #91;mailto:rwa@mosai
c-ag.com]
Sent: Thursday, January 05, 2006 1:15 PM
To: Khaldoun Ateyeh
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] table full scan

"Khaldoun Ateyeh" <Khaldoun.Ateyeh@panoratio.de> writes:

> Hi,
> I am using jdbc to full scan a huge table (over 11 million records).
> Unfortunately, I get an OutOfMemoryError exception. Is there any way
to
> tell postgre not to try to load the whole table at once?
>

Set autocommit to false, then use the method setFetchSize() of
the Statement to set the maximal rows that are read into the memory.

For example:

con. setAutoCommit(false)
;
stmt = con.createStatement();
stmt.setFetchSize(1000);
--
Roland Walter mailto: rwa (at) mosaic-ag (dot) com
MOSAIC SOFTWARE AG phone: +49 (0) 22 25 / 88 2-41 1
Am Pannacker 3 fax: +49 (0) 22 25 / 88 2-20 1
D-53340 Meckenheim http://www.mosaic-ag.com

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

Kris Jurka

2006-01-05, 7:23 am

On Thu, 5 Jan 2006, Khaldoun Ateyeh wrote:

> Which driver should I use? Because when I do what you suggested, I get
> "This method is not yet implemented" ... stmt.setFetchSize(1000).
>
> I am using the official postgresql-8.1-404.jdbc3.jar driver.
>

Apparently not. You most likely have a 7.3 or earlier driver in your
classpath as well because this method has been implemented for quite some
time, and certainly is in 8.1.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Khaldoun Ateyeh

2006-01-05, 11:24 am

You are right! I had another older version driver in the classpath.

Thanks for your help.

Khaldoun

-----Original Message-----
From: Kris Jurka & #91;mailto:books@eju
rka.com]
Sent: Thursday, January 05, 2006 2:07 PM
To: Khaldoun Ateyeh
Cc: Roland Walter; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] table full scan

On Thu, 5 Jan 2006, Khaldoun Ateyeh wrote:

> Which driver should I use? Because when I do what you suggested, I get
> "This method is not yet implemented" ... stmt.setFetchSize(1000).
>
> I am using the official postgresql-8.1-404.jdbc3.jar driver.
>

Apparently not. You most likely have a 7.3 or earlier driver in your
classpath as well because this method has been implemented for quite
some
time, and certainly is in 8.1.

Kris Jurka

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

Sponsored Links