Details
Major Description
See:
- https://www.postgresql.org/message-id/200609241203.59292.ralf.wiebicke%40exedio.com
- https://groups.google.com/forum/#!msg/comp.databases.postgresql/NuXGXdFbA4g/LQ97ahowmxkJ
- https://www.progress.com/blogs/solution-to-current-transaction-is-aborted-error-for-postgresql-compatibility-with-odbc-jdbc
The problem is that JdbcAggregationRepository relies heavily on spring-jdbc's throwing org.springframework.dao.DataIntegrityViolationException according to its sql exception mapping.
By default, DataIntegrityViolationException is thrown if SQLState starts with 23. Check https://www.postgresql.org/docs/10/errcodes-appendix.html and see that:
23505: unique_violation
The problem specific to PostgreSQL is that hitting such exception makes the connection (and surrounding Spring transaction) unusable. There's no way to repeat an attempt to insert (or even check current) state of aggregation, because PostgreSQL will immediately throw:
Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446) ...
With SQLState - 25P02 == "in_failed_sql_transaction".
The expected (by PostgreSQL) behavior is to rollback current transaction and start over.
We're however in (potentially nested) TransactionTemplate and rollback only status is beyond (easy) control.
