At the moment JooqExceptionTranslator translates exception, only if it's "the innermost exception" and probably the most interesting.
Unfortunately, when using with PostgreSQL it causes different exceptions to be thrown depending on the flag preferQueryMode
A simple project/test to reproduce this can be found here The version of Spring Boot used in the example is "3.0.2", but the issue can be reproduced with "2.7.8"
Depending on if &preferQueryMode=simple is included to spring.datasource.url the exception will be either DataIntegrityViolationException by Spring, or DataAccessException by jOOQ for this particular use-case, presented in the example (batch insert that is running inside a transaction and has constraint violations)
@Test
fun `should throw spring DataIntegrityViolationException exception`() {
jooq.execute("""
create table test_table(
id varchar not null primary key,
value int not null
);
""".trimIndent())
val queries = (1..3).map { value -> "insert into test_table values ('key', $value)" }
assertThrows<DataIntegrityViolationException> {
transactionOperations.execute {
jooq.batch(*queries.toTypedArray()).execute()
}
}
}
The issue seems to be caused by the different next-next-next chain for SQLException.
The chain looks like that without the preferQueryMode=simple:
And like that with preferQueryMode=simple:
Default translator SQLErrorCodeSQLExceptionTranslator is able to handle exceptions with SQLState 23505, but fails to translate 25P02 exceptions.
Why consistency is important here: An app can rely on DataIntegrityViolationException to handle conflicting actions from different users and prevent one of them from performing an action that has been already performed by another one. Though, the constraint will most probably prevent the second action from happening, if the app relies on the exceptions to show error messages to the user, the second user will get a wrong error text (or may even see a server error 500).
Possible solution: Now JooqExceptionTranslator tries to translate for propagation the innermost exception only (all the remaining exceptions in the chain are just being logged). JooqExceptionTranslator can instead propagate the innermost exception for which there's a translation available. It would be PSQLException@9205 from the image above
Comment From: wilkinsona
Thanks for the suggestion. Rather than changing the behavior of JooqExceptionTranslator, I think that we should consider updating SQLErrorCodeSQLExceptionTranslator so that it can translate 25P02 exceptions. We'll transfer this issue to the Framework team for their consideration.
Comment From: jhoeller
It's not clear to me why the 23505 code in those exceptions above isn't being consistently translated on its own. Are we not detecting it in some cases?
The 25P02 part seems to be a follow-up error that indicates that the transaction has been aborted, so I'm not sure we should be specifically considering that part.
Comment From: snicoll
@jhoeller I've ran the sample (thanks for that @Hdgh0g by the way!) and I believe it is due to this block in doTranslate: https://github.com/spring-projects/spring-framework/blob/83870e35d1d01fc0584c172a89d2475d637c4147/spring-jdbc/src/main/java/org/springframework/jdbc/support/SQLErrorCodeSQLExceptionTranslator.java#L187-L192
In the case of a BatchUpdateExceptionlike here, we take the next exception if they carry an valid error code and state. That next exception is the 25P02. With the preferQueryMode=simple we do have an intermediate 23505 exception that we can translate.
It's a bit strange that without it we lose the intermediate exception. I don't know if that's something we should fix though.
Comment From: snicoll
@Hdgh0g unfortunately, there is nothing we can about this, please move this to JOOQ. JooqTranslator is calling us multiple times, including with the raw 25P02 exception. It shouldn't do that and let us translate the exception hierarchy ourselves.