Ciao Andrea, as you know this problem has been bothering us as well, we have customized geoservers in production that serve remote sensing layers acquired daily where this issue has popped up once in a while. I am +1 about applying the patch, since as you point out, right now referencing is more or less unmaintained. However I would suggest to wait 1 or 2 days to have some feedback from martin.
Simone. ------------------------------------------------------- Ing. Simone Giannecchini GeoSolutions S.A.S. Owner - Software Engineer Via Carignoni 51 55041 Camaiore (LU) Italy phone: +39 0584983027 fax: +39 0584983027 mob: +39 333 8128928 http://www.geo-solutions.it http://simboss.blogspot.com/ http://www.linkedin.com/in/simonegiannecchini ------------------------------------------------------- On Mon, Mar 2, 2009 at 5:28 PM, Andrea Aime <[email protected]> wrote: > 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 > ------------------------------------------------------------------------------ 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
