When an SQL query fails in a transaction, PostgreSQL invalidates the entire transaction. In SqlRegistry.associate method failures are expected to occur, as sometimes it is possible that an already existing dataset is being associated.
The error is caught and an attempt at verifying if we are inserting a previously known dataset or now is made. The attempt consists of issuing a SELECT query on the dataset in question.
This will raise an exception on the SELECT query, even though everything seems correct, because PostgreSQL invalidated the entire transaction. See relevant code here
I have experimented with several solutions:
- enabling autocommit option on that statement
- unravelling the dataset dimensions and removing duplicates
- adding another nested transaction scope
The 1) solution does not quite work all the time because autocommit is only in effect when no transaction exists . Second potential solution works but is ugly.
Third solution seems the cleanest option as adding an additional nested transaction scope will attempt a commit and will issue a rollback on failure re-validating the transaction and allowing the SELECT query to execute.