Postgres Exceptions and java

Question

I am using the “postgresql-9.3-1102.jdbc3.jar” in order to connect to the database. When I have one exception e.g. one null value, I can use several ways in order to catch the exception.

e.g. try { ............} catch (PSQLException seRs) { ......... }

Or

try {.......} catch (SQLException se) {.......}

Or

try { .......} catch (Exception se) { ........ }

My Goal is to catch the specific SQLState in the cases that I am interesting.

For example I want to catch the invalid NULL value of one field, the Postgress returns the value “23502” SQLState but in any of the prior “catches” I can’t do it because the Error code is inherited I can’t check it.

In the previous “catches” I can have the “.getmessage” but this is not helping me i want to check the SqlState

Thanks for any ideas.

Solution

Catch SQLExceptoin then use SQLException.getSQLState() and compare it to see if it's what you want.

catch (SQLException ex) {
   final String ss = ex.getSQLState();
   //... blah blah ...
}

See PostgreSQL error codes for SQLState details. (While most of the state categories and codes are standard across DBs not all DBs implement them the same way and throw them at the same times, and most DBs have extras that are DB specific).

There is no way to catch an exception based on the SQLState. You must, unfortunately, catch it, and if it's not what you want wrap and re-throw it. (Don't just rethrow without wrapping, you lose the original stack).

In JDBC 4 there are subclasses of SQLException like SQLNonTransientException that you can catch, but only if the JDBC driver throws those subclasses. At time of writing PgJDBC does not support those, and always simply throws SQLException, so if you try to catch them you'll never catch anything. (Patches are welcome!).

---

In the real world you're usually interested in a number of different error conditions and want to do different things based on them.

Something vaguely like the untested, written-in-the-window:

} catch (SQLException ex) {
  final String ss = ex.getSQLState();
  if (ss.equals("40001") || ss.equals("40P01")) {      
     /* It is a serialization failure or a deadlock abort. Retry the tx. */
     retry_transaction = true;
  } else if (ss.startsWith("08") || ss.startsWith("53")) {
     /* It is a connection error or resource limit. Reconnect and retry. */
     try {
        conn.close();
     } catch (SQLException ex) { 
        logger.log("Error closing suspected bad connection after SQLState " + ss, ex);
     }
     conn = null; /* App knows to reconnect if it sees a null connection */
     retry_transaction = true;
  } else {
     throw new MyAppException(ex);
  }
}

... where your app knows to reconnect if it sees a null connection, and keeps a record of the transaction it just attempted so it can retry it in a loop until it succeeds if it hits a deadlock or serialization failure.

In reality you'd be smarter than this, adding rate-limiting of retries, etc. This is just a simplistic example.

---

For more details, cast the exception to PSQLException after testing for castability, or catch it as a PSQLException in the first place. Then get details with:

ex.getServerErrorMessage()

which gives you a ServerErrorMessage with detailed fields.

Comments

[Craig Ringer]

Why would you do that, instead of using e.getSQLState()?

[ToYonos]

Yes, your way is better looking but mine works even if a NullPointerException is catched

[Elias Elias]

@Graig Thank you very much for your reply but i forgot to tell you that in my case the ex.getSQLState() returns "null" so i can't use the SQLState. I can't understand why this is happening. To help you in order to help me I am using Netbeans as IDE and in the debug I can see the SQLstate = 23502 under the se.Cause.Inherit. SQLState

[Craig Ringer]

@EliasElias Sounds like it's an error from a batch. If you're referring to a specific error, not the general case, you should show the stack trace in the question. You have to getNextException to get the nested exception if it's a batch.

[Pere]

@CraigRinger I'm facing the same problem, and I'm not in a batch. I get null as cause and getNextException() also returns null.

[Ferrybig]

throw is not special, it does not change the stack trace if you rethrow something.