PG::InFailedSqlTransaction after rescuing UniqueConstraintViolation

[elado]

After rescuing from Sequel::UniqueConstraintViolation I'm getting PG::InFailedSqlTransaction on every following query. Bug?

# migration
Sequel.migration do
  change do
    create_table :models do
      primary_key :id
      String :a, limit: 100, index: {unique: true}
    end
  end
end

# model
class Model < Sequel::Model
end

# code
begin
  Model.create(a: a)
rescue Sequel::UniqueConstraintViolation => e
  puts "x"
end

Model.count # raises PG::InFailedSqlTransaction: ERROR:  current transaction is aborted, commands ignored until end of transaction block

[jeremyevans]

I'm not able to reproduce here:

Your database is stored in DB...
irb(main):001:0> DB.create_table :models do
irb(main):002:1*       primary_key :id
irb(main):003:1>       String :a, limit: 100, index: {unique: true}
irb(main):004:1>     end
=> nil
irb(main):005:0> class Model < Sequel::Model
irb(main):006:1> end
=> nil
irb(main):007:0> begin
irb(main):008:1*   Model.create(a: 'a')
irb(main):009:1> rescue Sequel::UniqueConstraintViolation => e
irb(main):010:1>   puts "x"
irb(main):011:1> end
=> #<Model @values={:id=>1, :a=>"a"}>
irb(main):012:0> begin
irb(main):013:1*   Model.create(a: 'a')
irb(main):014:1> rescue Sequel::UniqueConstraintViolation => e
irb(main):015:1>   puts "x"
irb(main):016:1> end
x
=> nil
irb(main):017:0> Model.count
=> 1

The only thing I think that could cause this is is you have some transaction around the entire code, so you are still inside the transaction, in which case the behavior you are seeing is expected. Rescuing exceptions inside transactions and not reraising an exception is a bad idea. Put your rescue block outside of any transaction block.

If you can come up with a self-contained, reproducible example that shows the behavior you describe (where it can just be pasted into bin/sequel), please post it and I'll reopen.

[elado]

I'm in a spec, and realized it's because DatabaseCleaner:

  config.before :suite do
    DatabaseCleaner.strategy = :transaction
    DatabaseCleaner.clean_with(:truncation)
  end

  config.around :each do |example|
    DatabaseCleaner.cleaning do
      example.run
    end
  end

Looks like it supports only transaction strategy.
Any idea how to solve that?

[jeremyevans]

The simplest solution is using the transaction :auto_savepoint option in the outer transaction, so that inner transactions automatically use savepoints. Not sure if DatabaseCleaner supports that, but using a transaction around spec examples is so trivial I'm not even sure why there is a whole library devoted to it. See http://sequel.jeremyevans.net/rdoc/files/doc/testing_rdoc.html for details.

An alternative solution would be to use a uniqueness validation, which will check for duplicate rows before trying to insert. The auto_validations plugin that ships with Sequel should be able to add one automatically for you.

[elado]

Thank you! Removed DatabaseCleaner and used the code from the docs and it works.

[wuarmin]

@jeremyevans I have a similar issue in specs. I'm using your transaction-code, to rollback after each spec. It works as you say, but only for the first time. At the second create it fails with:

 # --- Caused by: ---
 # PG::InFailedSqlTransaction:
 #   ERROR:  current transaction is aborted, commands ignored until end of transaction block
 #   /usr/local/bundle/gems/sequel-5.82.0/lib/sequel/adapters/postgres.rb:171:in `exec'

def create
  retry_count = 0
  begin
    result = try_insert
    retry_count += 1
  end while result.failure? && retry_count < 5
end

def try_insert
  Sucess(Model.create(code: gen_new_code))
rescue Sequel::UniqueConstraintViolation
  Failure("code is already taken! Retry!")
end

def gen_code
  #gen random code
end

Do you know why that is?
Thanks

[jeremyevans]

You likely need to wrap in a savepoint, as explained above in #908 (comment)

If you still need help, please open a new discussion and provide a minimal self contained reproducible example.

[wuarmin]

You likely need to wrap in a savepoint, as explained above in #908 (comment)

It is already wrapped in an auto_savepoint: true. If this is not the case, the 1st attempt already fails.

RSpec.configure do |c|
  c.around(:each) do |example|
    DB.transaction(rollback: :always, auto_savepoint: true){example.run}
  end
end

[jeremyevans]

If you still need help, please open a new discussion and provide a minimal self contained reproducible example.

[wuarmin]

Thanks I started a discussion at #2206