insert_conflict plugin

[PetrKaleta]

Hi,
let's say I have a model Foo with unique constraint on bar attribute.

Foo.new(bar: 'aaa').insert_conflict(target: :bar).save # => newly created instance
Foo.new(bar: 'aaa').insert_conflict(target: :bar).save.id # => nil
Foo.new(bar: 'aaa').insert_conflict(target: :bar).save.new? # => false

This behavior is kinda strange. Why id is nil and new? returns false. I am trying to make a "unique constraint safe" find or create method... So my idea is to create the model first and if it fails on UC, then return existing one...

[jeremyevans]

The insert_conflict model plugin is mostly designed to handle the case where if you are cannot insert, you should update the existing row instead. You are using it in a mode where if you cannot insert, no changes are made. No exceptions are raised because you are explicitly telling it to not raise an exception by using insert_conflict.

For what you want, it seems best to just to a normal save, rescue Sequel::UniqueConstraintViolation, and then do the retrieve the existing row in that case. That doesn't handle the case where the existing row is deleted in between, but that's a much less likely case.

[PetrKaleta]

Hey Jeremy,
I got your point. Problem is, that UniqueConstraintViolation is a DB error. So if this call will be called in context of some transaction, then that transaction will be automatically Rollbacked (no matter if I rescue Sequel::UniqueConstraintViolation). I think there should be a detection if it's in context of any outer transaction and if yes, then it should be put in a new transaction with a savepoint (so this savepoint will prevent rollback of outer transaction)...

What do you think?

[PetrKaleta]

What do you think about this?

class Foo < Sequel::Model
  def self.find_or_create(bar:)
    find_proc = proc { first bar: bar }

    create_proc = proc do
      create bar: bar
    rescue Sequel::UniqueConstraintViolation
      raise Sequel::Rollback if db.in_transaction?
    end

    (db.in_transaction? ? db.transaction(savepoint: true, &create_proc) : create_proc.call) || find_proc.call
  end
end

[jeremyevans]

I think you can use the savepoint: :only option, which will only create a savepoint if inside a transaction, it will not create a transaction if not in a transaction. That should simplify the code. Something like this (untested):

  def self.find_or_create(bar:)
   cond = {bar: bar}
    db.transaction(savepoint: :only) do
      create(cond)
    rescue Sequel::UniqueConstraintViolation
      raise Sequel::Rollback if db.in_transaction?
    end || first(cond)
  end

[PetrKaleta]

That works perfectly!

Thank you Jeremy