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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| import org.postgresql.util.PSQLException | |
| object PostgresErrorMapper { | |
| // see https://www.postgresql.org/docs/9.6/static/errcodes-appendix.html | |
| def isForeignKeyViolationError(e: PSQLException): Boolean = "23503" == e.getSQLState | |
| def createForeignKeyViolationError(e: PSQLException): PostgresIntegrityViolationError = { | |
| // assumes not null | |
| val detail = e.getServerErrorMessage.getDetail | |
| // expected format = [Key (column)=(given_value) is not present in table "table".] | |
| val regex = raw"Key (.*)=.*".r | |
| detail match { | |
| case regex(dirtyColumn, _*) => | |
| val column = dirtyColumn.substring(1, dirtyColumn.length - 1) | |
| PostgresForeignKeyViolationError(Some(column), e) | |
| case _ => PostgresForeignKeyViolationError(None, e) | |
| } | |
| } | |
| } | |
| class PostgresForeignKeyViolationError(column: Option[String], cause: PSQLException) |
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // from https://github.com/AlexITC/crypto-coin-alerts/blob/master/alerts-server/app/com/alexitc/coinalerts/data/anorm/AnormPostgresDAL.scala#L24 | |
| def withConnection[A](block: Connection => ApplicationResult[A]): ApplicationResult[A] = { | |
| try { | |
| database.withConnection(block) | |
| } catch { | |
| case e: PSQLException if isIntegrityConstraintViolationError(e) => | |
| Bad(createIntegrityConstraintViolationError(e)).accumulating | |
| } | |
| } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // from https://github.com/AlexITC/crypto-coin-alerts/blob/master/alerts-server/app/com/alexitc/coinalerts/data/anorm/FixedPriceAlertPostgresDataHandler.scala#L32 | |
| def create(createAlertModel: CreateFixedPriceAlertModel, userId: UserId): ApplicationResult[FixedPriceAlertWithCurrency] = { | |
| val result = withConnection { implicit conn => | |
| val fixedPriceAlert = alertPostgresDAO.create(createAlertModel, userId) | |
| // the alert has a FK to the currency, hence it must exist | |
| val exchangeCurrency = exchangeCurrencyPostgresDAO.getBy(createAlertModel.exchangeCurrencyId).get | |
| val alert = FixedPriceAlertWithCurrency.from(fixedPriceAlert, exchangeCurrency) | |
| Good(alert) | |
| } | |
| result.badMap { errors => | |
| errors.map { | |
| case PostgresIntegrityViolationError(Some("currency_id"), _) => UnknownExchangeCurrencyIdError | |
| case PostgresIntegrityViolationError(Some("user_id"), _) => VerifiedUserNotFound | |
| case e => e | |
| } | |
| } | |
| } |
Disclaimer, while I call my error class PostgresIntegrityViolationError, at the moment it just holds Foreign Key Violation errors.
