Hi, I'm writing to submit to the PSC consideration a patch for the sql based authority factories.
Lately I've been repeatedly experiencing issues with the HSQL based EPSG factory, for some reason the connection to the EPSG database gets corrupted and GeoServer ability to decode SRS codes into CRS objects stops working, making the GeoServer instance useless until restarted. The issue have been reported almost one year ago after a report from a GeoServer users (whose experience I am finally able to reproduce, it takes a few days of GeoServer running on a Linux box, so it was hard to reproduce in a dev env): http://jira.codehaus.org/browse/GEOT-1770 I've attached a patch to that jira for anyone to review, and I'm going to provide details about it in this mail. A deeper analysis shows an architectural problem with the sql based EPSG factories, in particular, they get a hold on a single SQL connection and try to perform every operation against it, without any possibility to recover from corruptions. This affects all EPSG factories, thought of course is more serious in PostgreSQL and Oracle factories, where the database can be remote and thus the likeliness of a connection drop increases (for example, network drops, database setting timeouts on open connections). In the case of HSQL, this should not happen, but I have a pretty reproducable bug about it, after one/two days of operation the database dies and GeoServer needs restarting (I checked, the EPSG database is still available on the disk). The obvious solution is to fix the EPSG factories by using a well known jdbc interaction pattern: - getting a connection from a data source - create statements, operate against the results (one or more times) - close up everything, connection included, retuning the connection to the pool). Unfortunately the design of the EPSG classes makes it so that the full and proper solution is hard to apply, and would make for a seriously big patch. So I went for a compromise: - the classes receive a DataSource instead of a single connection - the access to the connection is encapsulated into a single method that checks the connection status before returning it. If the connection is corrupt, it's discarded and a new one is gathered from the data source - same goes for prepared statements that are cached as fields ( or inside a map), we need to check the connection attached to them before actually using them, and ditch the whole set of prepared statements and connection in case of corruption (and start over). Checking is done as in most connection pools, by running a user provided statement , different for every database, that is at the same time fast, while forcing a communication with the server. The patch has been tested against a PostgreSQL authority, so that I could check the code is able to recover after a database restart while the authority was running (this is a check of the ability of the authority to replace its connection and prepared statements once they get corrupt). The public API of the sql authorities is not broken by the patch, new constructors have been created as needed and the old ones, taking a single connection, have been bridged using a fake, single connection DataSource. Given that the original jira issue received no attention so far, and that the referencing modules are currently unmantained in GeoTools, I'm asking a PSC perspective on the matter, and permission to commit on those modules (referencing, epsg-hsql, epsg-posgtgres) on trunk and 2.5.x Cheers Andrea PS: I was unsure which of the two sql authority hierarchies to patch, if the current one, or the replacement one that uses the mediators. Unfortunately I found out that the second one does not pass various tests to start with, so I decided to go on the safe side and patch the deprecated but working classes instead. A very similar patch can be cooked for the other hierarchy. ------------------------------------------------------------------------------ Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA -OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise -Strategies to boost innovation and cut costs with open source participation -Receive a $600 discount off the registration fee with the source code: SFAD http://p.sf.net/sfu/XcvMzF8H _______________________________________________ Geotools-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/geotools-devel
