Postgis leaking Resources

Description

Validation Processor serves as a great abuse of the Postgis DataStore implementation. What we have is two loops, both using FeatureReaders from PostGIS against the same table. This is basically doing a simple join by hand.

As you can imagine this results in a lot of FeatureReader creation - what you may not imagine is how badly the server suffers.

Problem 1: The above scenario exhuasts the available connection pool when using Transaction.AUTO_COMMIT.

DefaultTransaction - make use of our own DefaultTransaction so that everything happens on the same connection (rather than each request abusing the conneciton pool).

The above hack allows us to get to problem two ...

Problem 2: Not closing statements

The PostGIS logs show that we are creating a lot of statements and not closing them. Initially this was true (our new GT2 developers did not close their FeatureReaders causing the Server to run out of resources at around 30% of the way through).

After explicitly closing the FeatureReaders we progressed to 70% but statements are still not being closed. Talking with Andrea has led us to think that the problem is with the Postgres JDBCDriver. While the server would free all statements at the end of the Transaction it exhuasts memory resources before the end of the Transaction is reached.

We are not sure at this time if this is:
a) a JDBC Postgres Driver problem
b) a PostGIS problem

Tests were performed with Chris Holmes customized JDBC Driver (adding charset support) - which I think is based on 7.2?

We know that an update to 7.3 is required due to default assumptions for 7.3 (assumes Transaction by default?). We hope that updating to 7.3 would also take care of this resource leak.

This is a BLOCKER as it has taken down the work of 10 developers, and has halted GeoServer development at refractions for a month now.

Environment

None

Activity

Show:
codehaus
April 10, 2015, 3:06 PM

CodeHaus Comment From: jgarnett - Time: Mon, 31 May 2004 18:35:32 -0500
---------------------
From Email:

We updated the changes that Andrea made to the PostGIS datastore to close connections and clean itself up.

We then tested our large dataset again but still ran out of memory. After that, we dug a little deeper and inspected the postgres log file and found that it is opening cursor after cursor and never closing them. Now all that we seem to be dealing with on our end, at this higher level, are result sets. So it seems that the cursor problem could be in the JDBC driver.

Below is a sample of the code I run (where I close the reader, the file is org.geotools.validation.relate.org.geotools.validation.relate) and some of the postgres log file, where the cursors are opened and never closed:

fr2 = featureResults2.reader();

               try {

                   //System.out.println("featureResults length = " + featureResults2.getCount()); while (fr2 != null && fr2.hasNext())

                   {

                       ...

                    }

               }finally{

                   if (fr2 != null)

                       fr2.close();

               }

LOG: statement: SELECT stats FROM GEOMETRY_COLUMNS WHERE attrelid=17409 AND varattnum=16

LOG: statement: SELECT stats FROM GEOMETRY_COLUMNS WHERE attrelid=17409 AND varattnum=16

LOG: statement: DECLARE JDBC_CURS_6 CURSOR FOR SELECT road_id, "class", "type", "surface", "numlanes", "travdir", "divided", "const", "origin", "cap_method", "orig_date", "check_out_date", "region_id", "status_conflated", "standalone", AsText(force_2d("lineargeometry&quot) FROM "trim_vi" WHERE NOT ((NOT (intersects("lineargeometry", GeometryFromText('POLYGON ((-128.199741662686 50.8195262135391, -128.199741662686 50.8233411477222, -128.194792467251 50.8233411477222, -128.194792467251 50.8195262135391, -128.199741662686 50.8195262135391))', 6269)) AND "lineargeometry" && GeometryFromText('POLYGON ((-128.199741662686 50.8195262135391, -128.199741662686 50.8233411477222, -128.194792467251 50.8233411477222, -128.194792467251 50.8195262135391, -128.199741662686 50.8195262135391))', 6269)))); FETCH FORWARD 200 FROM JDBC_CURS_6

LOG: statement: SELECT stats FROM GEOMETRY_COLUMNS WHERE attrelid=17409 AND varattnum=16

LOG: statement: SELECT stats FROM GEOMETRY_COLUMNS WHERE attrelid=17409 AND varattnum=16

LOG: statement: DECLARE JDBC_CURS_7 CURSOR FOR SELECT road_id, "class", "type", "surface", "numlanes", "travdir", "divided", "const", "origin", "cap_method", "orig_date", "check_out_date", "region_id", "status_conflated", "standalone", AsText(force_2d("lineargeometry&quot) FROM "trim_vi" WHERE NOT ((NOT (intersects("lineargeometry", GeometryFromText('POLYGON ((-128.199997630002 50.819500990025, -128.199997630002 50.8195266029636, -128.199741662686 50.8195266029636, -128.199741662686 50.819500990025, -128.199997630002 50.819500990025))', 6269)) AND "lineargeometry" && GeometryFromText('POLYGON ((-128.199997630002 50.819500990025, -128.199997630002 50.8195266029636, -128.199741662686 50.8195266029636, -128.199741662686 50.819500990025, -128.199997630002 50.819500990025))', 6269)))); FETCH FORWARD 200 FROM JDBC_CURS_7

LOG: statement: SELECT stats FROM GEOMETRY_COLUMNS WHERE attrelid=17409 AND varattnum=16

LOG: statement: SELECT stats FROM GEOMETRY_COLUMNS WHERE attrelid=17409 AND varattnum=16

LOG: statement: DECLARE JDBC_CURS_8 CURSOR FOR SELECT road_id, "class", "type", "surface", "numlanes", "travdir", "divided", "const", "origin", "cap_method", "orig_date", "check_out_date", "region_id", "status_conflated", "standalone", AsText(force_2d("lineargeometry&quot) FROM "trim_vi" WHERE NOT ((NOT (intersects("lineargeometry", GeometryFromText('POLYGON ((-128.194792467251 50.8233411477222, -128.194792467251 50.8330411933652, -128.186531250794 50.8330411933652, -128.186531250794 50.8233411477222, -128.194792467251 50.8233411477222))', 6269)) AND "lineargeometry" && GeometryFromText('POLYGON ((-128.194792467251 50.8233411477222, -128.194792467251 50.8330411933652, -128.186531250794 50.8330411933652, -128.186531250794 50.8233411477222, -128.194792467251 50.8233411477222))', 6269)))); FETCH FORWARD 200 FROM JDBC_CURS_8

codehaus
April 10, 2015, 3:06 PM

CodeHaus Comment From: jgarnett - Time: Wed, 4 Aug 2004 12:20:29 -0500
---------------------
The update from fid exp allowed us to update to a newer postgres driver which solved the problem.

codehaus
April 10, 2015, 3:06 PM

CodeHaus Comment From: - Time: Thu, 26 May 2005 23:18:03 -0500
---------------------
<a href="http://www.sneerzine.com/">http://www.sneerzine.com/</a> &lt;a href=&quot;<a href="http://www.sneerzine.com/">http://www.sneerzine.com/</a>&quot;&gt;internet casinos&lt;/a&gt; thanks

Assignee

Unassigned

Reporter

codehaus

Triage

None

Priority

Highest
Configure