From: mark81 at gmail.com (Mark Mitchell)
Date: Tue, 17 Oct 2006 14:31:55 +0000 (UTC)
Subject: [Pljava-dev] trigger with postgis, slow memory leak
Message-ID: <loom.20061017T163024-526@post.gmane.org>

We have a Windows installation of postgres 8.1, with postgis 1.1.4,
and pljava 1.3 with JVM 1.5_09.  I wrote a pljava trigger to
automatically generate a postgis geometry circle after an insert (the
table basically contains the center point and a radius, and the
trigger then creates the circle from this information).

I have found though, that I have a very slow memory leak in my
trigger, and I am trying to track down the source.  So, finally
getting to my questions:

1) Have any of you ever used a pljava trigger with the
postgis_1.1.3.jar file?  I found that I had to add the postgres jdbc
driver to my pljava triggers classpath as well (since some of the
classes in postgis_1.1.3.jar subclass PGobject).  Have any of you ever
had to add the postgres jdbc driver to the classpath?


2) Basically I have to call _new.updateObject() and pass it a postgis
object to update the record with the postgis geometry object.  I don't
know if something in here is causing a problem or not.  The updates do
succeed, and the rows have the shape generated properly.



The meat of my trigger looks like this (where pgCenterUtm is org.postgis.Point):

                                                                              
rs = stmt.executeQuery("select asewkt( transform( buffer(
geomfromewkt('" + pgCenterUtm.toString() + "'), " + outerRadius + ",
16), " + WGS84_SRID + ") )");
                                                                               try {
                                                                               
       // should always return a row
                                                                               
       rs.next();
                                                                               
       Polygon circle = (Polygon)
PGgeometry.geomFromString(rs.getString(1));

                                                                               
       _new.updateObject("shape", circle);
} finally {
                                                                               
       if (rs != null)
                                                                               
                   rs.close();
}


Also note that both the statement and connection are closed in a
finally block as well.  I even close the _new ResultSet for the
trigger in another finally block, in case that was causing the leak.



3)  How do you all debug/profile pljava triggers?  I tried setting my
max memory to -Xmx2m to cause the memory error to happen more quickly,
but still don't excatly know how I should go about trying to profile
this.  I also found if I set -Xincgc (incremental garbage collection),
the memory lasts a little longer.



So to give you an idea of the slowness of the memory leak, I was able
to update approx. 256 thousand rows with geometry before the default
max memory of 64m in the connection was exceeded (and I received the
OutOfMemoryError, no more heap space error).

The other thing I noticed was that after the connection ran out of
memory, it did not close.  Is there any way to configure postgres
connections to close after the JVM runs out of memory?  (I run a jboss
connection pool where it will get more connections if they close
themselves).  Once the connection runs out of memory, all of my
inserts and updates fail on the table with the trigger...


Thanks for any help you can give,

-Mark