Why does the PG::InFailedSqlTransaction occur on the 2nd failed attempt,even though a savepoint is set?

[wuarmin]

Hey 👋,

please checkout following example. The outer transaction has auto_savepoint: true. I think that no PG::InFailedSqlTransaction should take place because of this. But it happens, but only on the 2nd failing attempt.

    DB.create_table :models do
      primary_key :id
      String :code, index: { unique: true }
    end

    def crt(code)
      puts "ATTEMPTING TO INSERT #{code}"
      begin
        DB[:models].insert(code: code)
      rescue Sequel::UniqueConstraintViolation
        puts "Duplicate code: #{code}"
      end
    end

    DB.transaction(rollback: :always, auto_savepoint: true) do
      crt("a")
      crt("a") # is rescued correctly, because of auto_savepoint: true
      crt("a") #  fails with PG::InFailedSqlTransaction
    end

To explain how I came across this problem:
I have this problem in a test suite. Records with unique codes are to be inserted in the code to be tested. If a generated code already exists, another attempt should be started.

Thanks

[jeremyevans]

You need to have a separate savepoint around each call that can fail:

    def crt(code)
      puts "ATTEMPTING TO INSERT #{code}"
      begin
        DB.transaction(rollback: :always, auto_savepoint: true, savepoint: true) do
          DB[:models].insert(code: code)
        end
      rescue Sequel::UniqueConstraintViolation
        puts "Duplicate code: #{code}"
      end
    end

      crt("a")
      crt("a")
      crt("a") 

[wuarmin]

Ok, thanks for the info, but I don't quite understand that.. I wanted to set auto_savepoint: true in the code itself, only if I have nested transactions in the implementation. Do you mean that I should just always set auto_savepoint: true? But then why is it not the default.

Do I also need savepoint: true? It works for me even without setting it. I'm on PostgreSQL.

[jeremyevans]

:auto_savepoint means #transaction calls inside the block default to having :savepoint set. :savepoint means to use a savepoint for the current transaction block. Which one of these you should use depends on the situation. In the above example, you don't need either :savepoint or :auto_savepoint, because you are not nesting transaction calls, so separate transactions are used for each insert call.

[wuarmin]

Thanks @jeremyevans, now it is clearer. But why is model creation without a transaction (my initial example) only a problem with the second failing attempt? The first ist rescued properly? Do you have an explanation?

[jeremyevans]

The first failing attempt raises Sequel::UniqueConstraintViolation, so it is rescued. The second failing attempt raises a different exception class, so it is not rescued.

[wuarmin]

Oh yes, now I understand. The following explanation also helped me:

PostgreSQL transactions have two main states: a "normal" state and a "failed" state. If any error occurs within a transaction, the transaction enters a failed state, and all subsequent SQL statements within that transaction will fail with PG::InFailedSqlTransaction until the transaction is either rolled back or committed.