Undeployed GeoNetwork leaves locks on a PostGIS database

[murrayh]

Version: GeoNetwork 2.7 svn rev 8285

Tomcat: 5.5.23

PostgreSQL: 8.1.23

PostGIS: 1.3.2

Java: Java(TM) SE Runtime Environment (build 1.6.0_24-b07)

When GeoNetwork is undeployed the following tables remain locked in PostgreSQL:

• metadatanotifiers
• spatialindex

Tomcat must be stopped for the locks to be removed.

We are connecting to the PostgreSQL database using the following config:

<user>redacted/user> <password>redacted</password> <driver>org.postgis.DriverWrapper</driver> <url>jdbc:postgresql_postGIS://redacted:5432/redacted</url> <poolSize>10</poolSize> <reconnectTime>3600</reconnectTime>

I suspect exceptions at the bottom of 'catalina.out' (which I will attach) are of interest:

Exception in thread "Cached Search Session Expiry Thread" java.lang.NullPointerException

at org.jzkit.search.impl.LRUCache.expunge(LRUCache.java:94) at org.jzkit.search.impl.LRUCache.run(LRUCache.java:83)

Exception in thread "Timer-2" java.lang.NullPointerException

at org.hsqldb.resources.BundleHandler.getString(Unknown Source) at org.hsqldb.Trace.getMessage(Unknown Source) at org.hsqldb.Trace.getMessage(Unknown Source) at org.hsqldb.Result.<init>(Unknown Source) at org.hsqldb.DatabaseCommandInterpreter.execute(Unknown Source) at org.hsqldb.Session.sqlExecuteDirectNoPreChecks(Unknown Source) at org.hsqldb.Session.execute(Unknown Source) at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source) at org.hsqldb.jdbc.jdbcStatement.execute(Unknown Source) at org.geotools.referencing.factory.epsg.DirectEpsgFactory.isConnectionValid(DirectEpsgFactory.java:3116) at org.geotools.referencing.factory.epsg.DirectEpsgFactory.getConnection(DirectEpsgFactory.java:3082) at org.geotools.referencing.factory.epsg.DirectEpsgFactory.dispose(DirectEpsgFactory.java:2994) at org.geotools.referencing.factory.DeferredAuthorityFactory$Disposer.run(DeferredAuthorityFactory.java:219) at java.util.TimerThread.mainLoop(Timer.java:512) at java.util.TimerThread.run(Timer.java:462)

[simonp]

Actually this is to do with 3 of 4 possible things:

• some functions that use database processes keep an active connection open (eg. spatialindexwriter and metadatanotifications) - which I can understand is ok in some cases
• at least one of these functions uses a shutdownhook to close it's active connection - this hook doesn't seem to get executed by undeploy
• closing the apache dbcp basicdatasource in ApacheDBCPool.java closes idle connections only - active connections are not closed - these are the connections you see left open
• may need to implement a servletcontextlistener to catch the undeploy event (this one I've yet to verify whether it's absolutely necessary)

I've got some fixes for this underway as a result of this analysis - it's a major pain to have to manually clean up and our servlet should behave properly.

BTW The last two exceptions you've reported are I think kind of spurious to this problem but they should also be fixed as they are a bit of a red herring otherwise.

[simonp]

Fixed in svn commit rev 8508