Re: PostgreSQL JDBC - Long Running Stored Procedure - Out of Memory

Hi,

I presume this function returns a large result set ?

If so there isn't enough memory for the result set.

Dave

On Thu, Jan 22, 2009 at 9:24 AM, Andrea Lombardoni <andrea(at)lombardoni(dot)ch>wrote:

> Hello.
>
> I have a strange problem when calling a long running stored procedure
> from Java JDBC.
>
> process_import_item() is a stored procedure in PostgreSQL which has
> boolean as return type.
>
> When I call the stored procedure manually (via psql), it takes about
> 15 minutes to run.
>
> When I call the stored procedure from Java/JDBC, it runs for some
> hours and then crashes the Virtual Machine with an
> "java.lang.OutOfMemoryError: Java heap space".
>
> My code is the following (autocommit is enabled):
> ...
> final CallableStatement st1 = msCon.prepareCall("{call
> process_import_item()}");
> st1.execute();
> ...
>
> The stack trace is the following:
>
> 13:47:33,588 ERROR [STDERR] java.lang.OutOfMemoryError: Java heap space
> 13:47:33,589 ERROR [STDERR] at
> java.util.Arrays.copyOf(Arrays.java:2786)
> 13:47:36,095 ERROR [STDERR] at
> java.lang.StringCoding.safeTrim(StringCoding.java:64)
> 13:47:36,096 ERROR [STDERR] at
> java.lang.StringCoding.access$300(StringCoding.java:34)
> 13:47:36,096 ERROR [STDERR] at
> java.lang.StringCoding$StringEncoder.encode(StringCoding.java:251)
> 13:47:38,585 ERROR [STDERR] at
> java.lang.StringCoding.encode(StringCoding.java:272)
> 13:47:38,585 ERROR [STDERR] at
> java.lang.String.getBytes(String.java:947)
> 13:47:41,088 ERROR [STDERR] at
> java.io.UnixFileSystem.getBooleanAttributes0(Native Method)
> 13:47:41,088 ERROR [STDERR] at
> java.io.UnixFileSystem.getBooleanAttributes(UnixFileSystem.java:228)
> 13:47:46,096 ERROR [STDERR] at java.io.File.isDirectory(File.java:754)
> 13:47:48,602 ERROR [STDERR] at
> org.jboss.net.protocol.file.FileURLLister.listFiles(FileURLLister.java:127)
> 13:47:48,602 ERROR [STDERR] at
>
> org.jboss.net.protocol.file.FileURLLister.listMembers(FileURLLister.java:75)
> 13:47:51,115 ERROR [STDERR] at
>
> org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:497)
> 13:47:53,614 ERROR [STDERR] at
>
> org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:263)
> 13:47:56,113 ERROR [STDERR] at
>
> org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.loop(AbstractDeploymentScanner.java:274)
> 13:47:56,113 ERROR [STDERR] at
>
> org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.run(AbstractDeploymentScanner.java:225)
> 13:49:05,456 ERROR [[CommandServlet]] Servlet.service() for servlet
> CommandServlet threw exception
> java.lang.OutOfMemoryError: Java heap space
> at java.util.Arrays.copyOfRange(Arrays.java:3209)
> at java.lang.String.<init>(String.java:216)
> at
> org.postgresql.util.ServerErrorMessage.<init>(ServerErrorMessage.java:49)
> at
> org.postgresql.core.v3.QueryExecutorImpl.receiveNoticeResponse(QueryExecutorImpl.java:1476)
> at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1280)
> at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:389)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:321)
> at myapplication.processData(CrmCommandHandler.java:337)
>
> Additionally, during the hours while the stored procedure runs, the
> Java VM grows slowly but steadly in the memory usage (some Mb per
> minute).
>
> Am I doing something wrong? Where should I look to diagnose/fix this issue?
>
> Thank you!
>
> My setup:
> PostgreSQL 8.2.7-1 with postgresql-8.2-507.jdbc4.jar
>
> java -version:
> java version "1.6.0"
> Java(TM) SE Runtime Environment (build 1.6.0-b105)
> Java HotSpot(TM) Client VM (build 1.6.0-b105, mixed mode, sharing)
>
> Linux 2.6.24-19-generic #1 SMP Wed Aug 20 22:56:21 UTC 2008 i686
> GNU/Linux (Ubuntu 8.10).
>
>
>
> --
> Dr. Andrea Lombardoni
> andrea(dot)lombardoni(at)oneoverzero(dot)net
>
> OneOverZero GmbH http://www.oneoverzero.net/
> Militärstrasse 76
> CH-8004 Zuerich
> T: +41 (43) 5384294
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>