When I execute a query with a specified query timeout, for example via JPA :
If the query takes longer than 1s, I expect the database driver to throw an exception and hibernate to translate that exception to an instance of org.hibernate.QueryTimeoutException
Hibernate does honour this when dealing with Mysql.
The mysql driver throws a com.mysql.cj.jdbc.exceptions.MySQLTimeoutException which implements the standard jdk exception java.sql.SQLTimeoutException and for which Hibernate is translating to org.hibernate.QueryTimeoutException at https://github.com/hibernate/hibernate-orm/blob/39e576cea49c62763e651237f3666f97205406f6/hibernate-core/src/main/java/org/hibernate/exception/internal/SQLExceptionTypeDelegate.java#L66
So all is fine for Mysql.
However for PostgreSQL, the postgresql driver throws a generic exception org.postgresql.util.PSQLException which only implements java.sql.SQLException with a vendor specific sql state of 57014.
The issue is that hibernate does not translate that exception to a QueryTimeoutException.
In consequence, it triggers a rollback of the underlying transaction in ExceptionConverterImpl after defaulting to a generic PersistenceException, getting passed to the handlePersistenceException then calling sharedSessionContract.markForRollbackOnly(); which is not desirable when the query timeout is expected.
By digging a bit into the sql exception translators, I've found this class: SQLStateConversionDelegate
It has two additional custom conversion cases to map to the org.hibernate.QueryTimeoutException from a generic sql state error code.
To fix this issue, in order to have hibernate properly translate and rethrow org.hibernate.QueryTimeoutException, would it be preferable to have either ?
Add another "or" clause to the sqlState if condition in SQLStateConversionDelegate
Fix the PostgreSQL95Dialect by overriding its buildSQLExceptionConversionDelegate method in order to implement this sqlState only for postgresql
Something else ?
For my issue, I was about to override the buildSQLExceptionConversionDelegate in the postgresql dialect because I thought it was less invasive. I'd be glad to propose a pull request and a test case it you think it can be assigned to a new contributor.