Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-21210

On transaction abort, PostgreSQL ignores commands in the entire transaction block.

    XMLWordPrintable

    Details

    • Team:
      External

      Description

      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:

      1. enabling autocommit option on that statement
      2. unravelling the dataset dimensions and removing duplicates
      3. 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.

        Attachments

          Activity

          Hide
          dinob Dino Bektesevic added a comment -

          Jenkins #30408 passed. Changes merged.

          Show
          dinob Dino Bektesevic added a comment - Jenkins #30408 passed. Changes merged.

            People

            Assignee:
            dinob Dino Bektesevic
            Reporter:
            dinob Dino Bektesevic
            Reviewers:
            Jim Bosch, Kian-Tat Lim
            Watchers:
            Dino Bektesevic, Jim Bosch, Kian-Tat Lim
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                CI Builds

                No builds found.