5

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?

three-cups
  • 4,375
  • 3
  • 31
  • 41
  • 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. – FThompson Jun 04 '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 04 '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. – FThompson Jun 04 '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 04 '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. – FThompson Jun 04 '13 at 23:57

2 Answers2

5

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • thanks for your answer. Will this test (the validationQuery) remove connections from the pool? – three-cups Jun 06 '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 06 '13 at 12:49
  • 1
    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 06 '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 06 '13 at 23:45
3

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.

Oleg Muravskiy
  • 705
  • 5
  • 8