current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 2 down vote favorite
1

I'm using postgres 9.1, org.apache.commons.dbcp.BasicDataSource (for my connection pool) and Java 1.7. When I restart my postgres server, I get exceptions like org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command.

How can I make it so the connections automatically re-connect to the restarted database?

share|improve this question
2  
Use a try-catch to catch the exception (checking e.getMessage() for it having actually been a server restart), and then reconnect to the database from within the catch statement or another block of code triggered by it. – Vulcan Jun 4 '13 at 19:40
1  
@Vulcan Do not check the error message; use the SQLSTATE you can get from the PSQLException to check the cause of the issue. Otherwise you'll get fun problems when the message is reworded in a new version or someone's running Pg in a different language. – Craig Ringer Jun 4 '13 at 23:48
    
@CraigRinger Good point. I wasn't aware that PSQLException had such a field (I've never used PSQL before). It should definitely be used instead of the message, you're right. – Vulcan Jun 4 '13 at 23:54
    
@Vulcan getSQLState() is an SQLException method, and is standard across all DBs. never parse the error message - for any DB, not just Pg. That's what the SQLState is for. The message classes are fairly standard, too. – Craig Ringer Jun 4 '13 at 23:55
    
@CraigRinger Ah, my bad. I've only used JDBC briefly in the past. I never parse exception messages in any case, but I wasn't aware of getSQLState(). Thanks. – Vulcan Jun 4 '13 at 23:57
up vote 4 down vote

DBCP has a connection validation query option - validationQuery, according to the docs. You can set it to something like SELECT 1; and DBCP will run that before returning a connection to test it.

That said, your app really should handle this case. SQL queries can fail for all sorts of reasons and you should always do your queries in a retry loop with a time back-off and retry limit, plus some logic to decide what exceptions are recoverable on retry and which aren't (use the SQLState for that).

In particular, validation is subject to a race condition where you can have event orderings like:

  1. Validate
  2. Pass connection to app
  3. Server shutdown
  4. App runs first statement

or

  1. Validate
  2. Pass connection to app
  3. App runs first statement, opening a transaction
  4. Server shutdown
  5. App runs second statement

... so it remains important for your app to have a proper retry loop and good transaction handling.

You can get the SQLState from the SQLException: SQLException.getSQLState. The codes for PostgreSQL are in the PostgreSQL manual.

share|improve this answer
    
thanks for your answer. Will this test (the validationQuery) remove connections from the pool? – three-cups Jun 6 '13 at 12:42
    
@three-cups Yes, otherwise it'd serve no purpose. It'll cause a connection to be discarded and replaced with a new one. Your app still needs to correctly handle retries - since you might shut the server down halfway through a transaction or between validation and the start of a transaction - but validation is a useful way of discarding known-bad connections. – Craig Ringer Jun 6 '13 at 12:49
    
The only thing I don't like about it is that it adds an extra database query to on every connection borrow from the pool. I'd rather handle the exceptional case when it comes up. – three-cups Jun 6 '13 at 15:23
    
@three-cups That's my view too - since you have to handle the exceptional cases anyway, why have the validation query? You seemed to be asking for how to validate the connections, though, so that's what I explained. – Craig Ringer Jun 6 '13 at 23:45
up vote 0 down vote

In this particular case the PostgreSQL connection is telling you that the server was shut down after the connection was created. DBCP connection pool does not handle this condition with it's default configuration.

Even if you set validationQuery parameter to something like SELECT 1, it will not be used, unless you also set at least one of the testOnXXXX parameters.

I usually set both testOnCreate and testOnBorrow to true.

Also check other defaults of DBCP (in the org.apache.commons.pool2.impl.BaseObjectPoolConfig), because in my opinion they are not well suited for production environments.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.