sql connection auto closing on postgres reset

[Kallin Nagelberg]

Hey,

I've got an application running in production talking to a postgres database. The database is periodically stopped, has it's data reimported, and restarted. After this has completed I see these exceptions in grails:

java.sql.SQLException: Already closed.

        at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:114)
        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:191)

The code is simply using the defined datasource:

def sql = new Sql(dataSource)

def row = sql.firstRow("select * from mytable)

Any suggestions? 

Cheers,

-Kallin Nagelberg

[typoknig]

The table name isn't "User" is it?  See step 13.

http://www.ericonjava.com/?p=293

[Kallin Nagelberg]

Nope, though I did encounter that same error when using spring security myself. Thanks!

On Fri, Jul 6, 2012 at 2:25 PM, typoknig <[hidden email]> wrote:

The table name isn't "User" is it?  See step 13.

http://www.ericonjava.com/?p=293

--
View this message in context: http://grails.1312388.n4.nabble.com/sql-connection-auto-closing-on-postgres-reset-tp4631153p4631222.html
Sent from the Grails - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[ideasculptor]

On Thu, Jul 5, 2012 at 11:49 AM, Kallin Nagelberg <[hidden email]> wrote:

Hey,

I've got an application running in production talking to a postgres database. The database is periodically stopped, has it's data reimported, and restarted. After this has completed I see these exceptions in grails:

java.sql.SQLException: Already closed.

        at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:114)
        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:191)

The code is simply using the defined datasource:

def sql = new Sql(dataSource)

def row = sql.firstRow("select * from mytable)

The connection pool isn't testing the connection when handing out connections, so the first time you use an old connection, you get an error.  You can either write code to handle the error by getting new connections in a loop until exceeding a maximum count or a good connection is found or configure the connection pool to test connections for you before handing them out.  The dbcp connection pools all have a configurable query that can be used to test connections - "select 1;" works for postgres - and you can specify that connections be tested periodically, when connections are returned to the pool, and when connections are requested from the pool, or any combination thereof.

See properties: validationQuery, testWhileIdle, testOnBorrow, testOnReturn

Given that most architectures use a single connection to service an entire requests and many requests will have more than one query, the overhead of having the pool run "select 1" against the db at the start of every request is pretty minimal and not likely to be relevant in applications where every extraneous millisecond of latency isn't tuned away.

[Kallin Nagelberg]

Thanks for such a thorough response Samuel!

On Fri, Jul 6, 2012 at 4:42 PM, Samuel Gendler <[hidden email]> wrote:

On Thu, Jul 5, 2012 at 11:49 AM, Kallin Nagelberg <[hidden email]> wrote:

Hey,

I've got an application running in production talking to a postgres database. The database is periodically stopped, has it's data reimported, and restarted. After this has completed I see these exceptions in grails:

java.sql.SQLException: Already closed.

        at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:114)
        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:191)

The code is simply using the defined datasource:

def sql = new Sql(dataSource)

def row = sql.firstRow("select * from mytable)

The connection pool isn't testing the connection when handing out connections, so the first time you use an old connection, you get an error.  You can either write code to handle the error by getting new connections in a loop until exceeding a maximum count or a good connection is found or configure the connection pool to test connections for you before handing them out.  The dbcp connection pools all have a configurable query that can be used to test connections - "select 1;" works for postgres - and you can specify that connections be tested periodically, when connections are returned to the pool, and when connections are requested from the pool, or any combination thereof.

See properties: validationQuery, testWhileIdle, testOnBorrow, testOnReturn

Given that most architectures use a single connection to service an entire requests and many requests will have more than one query, the overhead of having the pool run "select 1" against the db at the start of every request is pretty minimal and not likely to be relevant in applications where every extraneous millisecond of latency isn't tuned away.