Desugaring foreign key violation errors from postgresql jdbc

When working with applications using relational databases (like PostgreSQL in this example), it is quite probably that you are using foreign key constraints (if not, you probably should) to protect the integrity of your data.

You usually validate the data before storing it but sometimes it is unavoidable to get these kind of errors once in a while while working on concurrent environments.

It is a good practice to differentiate between errors and failures, while using PostgreSQL JDBC you can't differentiate them easily because you always get a PSQLException, while I love PostgreSQL I hate this driver for this reason.

Desugaring the exception into specific errors with the related values is quite useful to decide if we can do anything, like retrying the operation, blaming the user (error) or blaming the server (failure).

I'll describe the way that I've used for some time to detect foreign key violations from the PostgreSQL driver, while the presented code it is Scala, it could be applicable to Java users as well.

Looking to the documentation you will find that PSQLException could contain a ServerErrorMessage, it contains the logic for parsing the server error into several nullable fields.

The important parts from the exception for this task are the SQLState (which just wraps a String) and the detail retrieved from the ServerErrorMessage.

The SQLState basically give us an error code where Integrity Constraint Violation errors start with "23", being "23503" the code for the Foreign Key Violation error.

When we get a Foreign Key Violation error, the detail retrieved from the ServerErrorMessage gives a message like this:
- Key (column)=(given_value) is not present in table "table".

In this case column could be user_id, given_value could be 1, and table being users.

Using this knowledge, we can create a mapper function that gives the specific error having the column name which could be useful to decide what to do.



Just to give you a real example, I have been using this approach with Anorm to return specific error messages to the user in a web application, I have defined AnormPostgresDAL which has the reusable error mapping to give me an application specific error.

Creating a fixed price alert, requires a foreign key to the users table (the alert owner) and the currencies table (the related currency), detecting if the user or the currency constraint is violated, and then map it to the proper error is now a simple task, see FixedPriceAlertPostgresDataHandler#create.



Disclaimer, while I call my error class PostgresIntegrityViolationError, at the moment it just holds Foreign Key Violation errors.