Exception in thread "main" java.lang.OutOfMemoryError: Java heapspace

I am not a java programmer, so it is possible I am doing something wrong:

I saw this problem initially on my test system using sequoia jdbc driver
with my postgresql 8.1.4 database servers trying
to create an index on a 27 million row table. After many suggestions
and tests, It was suggested I try using the postgresql
jdbc driver to perform the create index to try and isolate where the out
of memory condition is occurring. So I edited a
java application I was testing with and pointed it to the postgresql
jdbc driver. I used version postgresql-.2dev-503.jdbc3.jar
the first time and postgresql-9.2dev-503.jdbc3.jar the second time with
the same results: Exception in thread "main" java.lang.OutOfMemoryError:
Java heap space



Below is the java code I am using to try to do this:

**
*
*/
import java.sql.*;
/**
* @author jdavis
*
*/
public class jdbcpostgrestestcreateindex {

/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub

String url = "jdbcostgresql://10.10.101.14:3153/ange";
Connection con;

try {
Class.forName("org.postgresql.Driver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}

try {
con = DriverManager.getConnection(url,"user","XXXXXXX");
con.setAutoCommit(false);
Statement s = con.createStatement();
s.setCursorName("cursor name");
s.setFetchSize(100);
String createTableIndexString = "create index
file_name_idx on catalog using btree {file_name}";
s.executeUpdate(createTableIndexString);
con.commit();
s.close();
con.close();
} catch (SQLException ex) {
System.err.print("SQLException: ");
System.err.println(ex.getMessage());
}
}

}



If this is not a bug, any hints or examples of the correct way to do
this would be appreciated.
I am unsure how to grab a stack trace, if one is needed please send me
an example and I will
be glad to add the code and re-run the test to provide that information.

Thanks,

Jim D.

---------------------------(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

Jim Davis schrieb:
> I am not a java programmer, so it is possible I am doing something wrong:
>
> I saw this problem initially on my test system using sequoia jdbc
> driver with my postgresql 8.1.4 database servers trying
> to create an index on a 27 million row table. After many suggestions
> and tests, It was suggested I try using the postgresql
> jdbc driver to perform the create index to try and isolate where the
> out of memory condition is occurring. So I edited a
> java application I was testing with and pointed it to the postgresql
> jdbc driver. I used version postgresql-.2dev-503.jdbc3.jar
> the first time and postgresql-9.2dev-503.jdbc3.jar the second time
> with the same results: Exception in thread "main"
> java.lang.OutOfMemoryError: Java heap space
>
I have no idea, why this happens. I would not expect that. But i saw in
your code example that you use executeUpdate for a defining sql
statement. I would use just the execute method. A cursor should not be
needed.

Regards,
Roland.

--
Roland Walter mailto: rwa (at) mosaic-ag (dot) com
MOSAIC SOFTWARE AG phone: +49 (0) 22 25 / 88 2-44 9
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 4: Have you searched our list archives?

http://archives.postgresql.org

Jim Davis wrote:

I changed the executeUpdate to just an execute, saved, re-ran java
application and received the same
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
error.




Roland Walter wrote:

> Jim Davis schrieb:
>
>> I am not a java programmer, so it is possible I am doing something
>> wrong:
>>
>> I saw this problem initially on my test system using sequoia jdbc
>> driver with my postgresql 8.1.4 database servers trying
>> to create an index on a 27 million row table. After many suggestions
>> and tests, It was suggested I try using the postgresql
>> jdbc driver to perform the create index to try and isolate where the
>> out of memory condition is occurring. So I edited a
>> java application I was testing with and pointed it to the postgresql
>> jdbc driver. I used version postgresql-.2dev-503.jdbc3.jar
>> the first time and postgresql-9.2dev-503.jdbc3.jar the second time
>> with the same results: Exception in thread "main"
>> java.lang.OutOfMemoryError: Java heap space
>>
> I have no idea, why this happens. I would not expect that. But i saw
> in your code example that you use executeUpdate for a defining sql
> statement. I would use just the execute method. A cursor should not
> be needed.
>
> Regards,
> Roland.
>


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

I tried to verify, if i would see any memory increase, when running the
following program on a table with about 6.6 million rows.

I used java 1.4.2_05, java 1.5.0_07, jdbc3 driver builds number 407
and 503. The database was a 7.4.13 on a Linux-box, the java program
was executed on win-2k.

There was no increase of memory requirements. The java process always
used about 10 Megabytes, running for about 1.5 minutes. The statement
had no ResultSet.

A defining sql statement does not return a ResultSet, so I do not understand
why your process died due to exhausted memory.

BTW setting a cursor name
or use of setFetchSize or not, did not change anything.


import java.lang.ClassNotFoundException;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;


/**
* Drops and creates an index on the billing transaction table.
* In pgsql-jdbc has been a report of an OutOfMemoryException when
* creating an index on a table with 27 million rows.
*/
class DropCreateIndex {
static String user = "billtest";
static String password = "billtest";
static String url = "jdbcostgresql://aupc306:5432/billtst1";
static String driverClassName = "org.postgresql.Driver";
static String dropIndex = "DROP INDEX xitransaction3";
static String createIndex = "CREATE INDEX xitransaction3 on transaction using btree (cc_trans_id, bc_id)";

public static void main(String[] args) {
Connection con = null;
Statement st = null;

try {
Class.forName(driverClassName);
} catch (ClassNotFoundException e) {
System.out.println("Driver " + driverClassName + " is not in the CLASSPATH!");
System.exit(1);
}

try {
con = DriverManager.getConnection(url, user, password);

con.setAutoCommit(false);

st = con.createStatement();

st.setFetchSize(100);
st.setCursorName("drop_cursor");

boolean hasResultSet = st.execute(dropIndex);

if (hasResultSet) {
System.out.println("Statement \"" + dropIndex + "\" has a ResultSet");
}

System.out.println("Dropped index!");

st.setCursorName("create cursor");
hasResultSet = st.execute(createIndex);

if (hasResultSet) {
System.out.println("Statement \"" + createIndex + "\" has a ResultSet");
}

System.out.println("Created index!");

con.commit();

} catch (SQLException e) {
System.err.println("ERROR: " + e.getMessage());
e.printStackTrace();

if (con != null) {
try {
con.rollback();
} catch (SQLException e2) {
System.err.println("Rollback failed! " + e.getMessage());
}
}
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e1) {
// do nothing
}
}

if (con != null) {
try {
con.close();
} catch (SQLException e1) {
// do nothing
}
}
}
}

}

--
Roland Walter mailto: rwa (at) mosaic-ag (dot) com
MOSAIC SOFTWARE AG phone: +49 (0) 22 25 / 88 2-44 9
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 4: Have you searched our list archives?

http://archives.postgresql.org

Jim,

What happens when you try to do this in psql. It's possible that
there are notify messages filling up memory.

Dave
On 25-Aug-06, at 8:10 AM, Jim Davis wrote:

> Jim Davis wrote:
>
> I changed the executeUpdate to just an execute, saved, re-ran java
> application and received the same
> Exception in thread "main" java.lang.OutOfMemoryError: Java heap
> space error.
>
>
>
>
> Roland Walter wrote:
>
>> Jim Davis schrieb:
>>
>>> I am not a java programmer, so it is possible I am doing
>>> something wrong:
>>>
>>> I saw this problem initially on my test system using sequoia jdbc
>>> driver with my postgresql 8.1.4 database servers trying
>>> to create an index on a 27 million row table. After many
>>> suggestions and tests, It was suggested I try using the postgresql
>>> jdbc driver to perform the create index to try and isolate where
>>> the out of memory condition is occurring. So I edited a
>>> java application I was testing with and pointed it to the
>>> postgresql jdbc driver. I used version postgresql-.
>>> 2dev-503.jdbc3.jar
>>> the first time and postgresql-9.2dev-503.jdbc3.jar the second
>>> time with the same results: Exception in thread "main"
>>> java.lang.OutOfMemoryError: Java heap space
>>>
>> I have no idea, why this happens. I would not expect that. But i
>> saw in your code example that you use executeUpdate for a defining
>> sql statement. I would use just the execute method. A cursor
>> should not be needed.
>>
>> Regards,
>> Roland.
>>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>


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

Roland Walter <rwa@mosaic-ag.com> writes:

> static String createIndex = "CREATE INDEX xitransaction3 on transaction using btree (cc_trans_id, bc_id)";

The problem was in Jim's create index statement. He used brackets
instead of parenthesis around the column names. Jim corrected it and
an OutOfMemoryError did not occur anymore. The statement took about 48
minutes.

Regards,
Roland.
--
Roland Walter mailto: rwa (at) mosaic-ag (dot) com
MOSAIC SOFTWARE AG phone: +49 (0) 22 25 / 88 2-44 9
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 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

Roland Walter wrote:
> Roland Walter <rwa@mosaic-ag.com> writes:
>
>> static String createIndex = "CREATE INDEX xitransaction3 on transaction using btree (cc_trans_id, bc_id)";
>
> The problem was in Jim's create index statement. He used brackets
> instead of parenthesis around the column names. Jim corrected it and
> an OutOfMemoryError did not occur anymore. The statement took about 48
> minutes.

Sounds like there is a bug in the driver's escape sequence parsing
somewhere then. An invalid {...} escape shouldn't cause an OOME.

-O

---------------------------(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

On Wed, 30 Aug 2006, Kris Jurka wrote:

> On Mon, 28 Aug 2006, Oliver Jowett wrote:
>
>> Sounds like there is a bug in the driver's escape sequence parsing
>> somewhere then. An invalid {...} escape shouldn't cause an OOME.
>>
>
> Indeed. The attached patch fixes this case for me. In general the
> handling of escapes that aren't correct doesn't look great and should
> probably be looked at it the future. For example the fact that it only
> checks the first letter of some escapes and doesn't complain if the
> second isn't found doesn't look good. Anyway as gborg is down, cvs is
> down, so I'll commit this one later.
>

Applied to 8.0, 8.1 and HEAD.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq