Battling RecordNotUnique in Rails

This aims to be a quick post that:

• explains RecordNotUnique and my old, ugly patterns
• explains a newer (~2018) built-in rails helper
• explains a custom helper

What is RecordNotUnique?

Typically you attempt to SELECT the row, and then INSERT under the assumption/hope that it's rare to worry about racing INSERTs. In rails there have been longstanding helpers to reach for here:

Model.where(unique_col: val)
  .first_or_create { |new_record| ... }

This does a SELECT and then INSERT. This code might raise RecordNotUnique, and it'd mean you are INSERTing a record that violates a unique constraint in your database. You're hoping that it's rare to have multiple pieces of code doing this at the same time, for the same rows. In my experience, this hope falls apart more often than I'd like:

ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_models_on_unique_col"
DETAIL:  Key (unique_col)=(val) already exists.

Take my recent case as an example. I'm in SOA land writing rails code. The latest app is processing events from multiple sources that occur very closely together. From the start I was writing code to rescue RecordNotUnique:

begin
  Model.where(...).first_or_create { ... }
rescue ActiveRecord::RecordNotUnique => exception
  # Retry first_or_create here.
  # Blow up if still not found (broken code).
end

This gets ugly pretty quickly. Any time you retry, you make sure you're not creating an infinite loop. And you re-raise the exception if the 1st retry fails. The rescue code might look like this:

begin
  tries ||= 0
  Model.where(...).first_or_create { ... }
rescue ActiveRecord::RecordNotUnique => exception
  retry if (tries += 1) == 1
  raise exception
end

That is how I used to hope to tackle RecordNotUnique. It was rare and handled on a case-by-case basis. You end up writing complicated tests for the above that:

• test each line in your rescue
• try to test actual racing inserts if it's important (integration specs, I typically need more code/hooks to help there)

Enter create_or_find_by

first_or_create will SELECT and then INSERT, but let's say you don't want that. Let's say you rarely find anything in your SELECT and almost always INSERT. An example of this is a dedicated, high-volume ruby process whose job is to process just new records. If it's always doing the INSERT, then you are wasting the up-front SELECT.

If you can infer this up front, then in DHH's words, you might want to "lean on unique constraints":

Model.where(unique_attr: val).create_or_find_by do |new_record|
  new_record.assign_attributes(initial_attributes)
end

The quick summary here is that rails will do first_or_create in reverse. You'll see an INSERT go by with your full set of attributes. If a unique constraint gets you, you'll see a SELECT go by with just the unique_attr condition.

It's handy. I've used it in some cases. And if you can use it, you're leaning on rails to handle RecordNotUnique, letting you throw away that manual retry code above (and the associated tests).

It's not without gotchas though. I personally had problems with create_or_find_by as I leaned into it:

1. I wanted to lean harder! I needed to know how the create_or_find_by went. Did the record just get inserted? I don't know of a way to tell. If you want to answer this question, suddenly you are going back to rescue RecordNotUnique and all that ugliness.
2. I wanted to go back to SELECTing first, and still not worry about RecordNotUnique. My code doesn't INSERT often, but when it does, it's usually racing to INSERT with other code.

Custom take_or_create! Helper

So far I've been using these without any new gotchas compared to create_or_find_by. The style is a bit different: The block argument needs to return an attribute hash. It solves my situation so far:

# in application_record.rb, or in a concern/mixin

  attr_writer :created_moments_ago

  def created_moments_ago?
    @created_moments_ago
  end

  def self.create_or_take!
    where(block_given? && yield).crumby_create!
  rescue ActiveRecord::RecordNotUnique
    take!
  end

  def self.crumby_create!
    instance = transaction(requires_new: true) { create! }
    instance.created_moments_ago = true
    instance
  end

  def self.take_or_create!(&block)
    take || create_or_take!(&block)
  end

Let's do a quick tour of these helpers, and explain how they solve my 1 & 2 above.

# INSERT first. If INSERT fails, try to SELECT
Model.where(unique_attr: val)
  .create_or_take! { creation_attrs_hash }

# SELECT first, then INSERT.
# If INSERT fails, try to SELECT one more time.
Model.where(unique_attr: val)
  .take_or_create! { creation_attrs_hash }

Note: The creation_attrs_hash piece diverges from create_or_find_by, which passes in a new record to the block.

That gives me a SELECT-first path, addressing my #2 problem above. But what about #1? What about knowing how it went? If you use these helpers to create your models, you'll get a breadcrumb to read, that is true if it successfully INSERTed the record:

model = Model.where(unique: val).take_or_create! { ... }
model.created_moments_ago? # naming is hard!

That's it! These are purpose-built. Maybe you have a slightly different scenario and need it to work differently. Maybe one day I'll learn of a new rails helper that removes the need for my custom helper. Hopefully this helps someone else in their battle against RecordNotUnique.

Comments

[Jake Swanson]

If you're reading this in 2023+, it looks like rails is adding built-in support for my cases with a change in rails 7.1 ✨

[Daniel Uber]

Awesome discussion of the trade-offs here. One note:

I needed to know how the create_or_find_by went. Did the record just get inserted? I don't know of a way to tell.

AR models expose a previously_new_record? that's true when an object was new? (not yet persisted) before the last update. If you found (rather than created) the object you'd see previously_new_record? respond false, if you created it would be true.

$ rails --version
Rails 6.1.3.1
$ rails new bloog
$ cd rails
$ rails g model name name:string{30}:uniq
$ rails db:prepare
$ rails console
irb(main):001:0> n1 = Name.create_or_find_by(name: "John")
irb(main):002:0> n2 = Name.create_or_find_by(name: "John")
irb(main):003:0> n1.previously_new_record?
=> true
irb(main):004:0> n2.previously_new_record?
=> false

[Jake Swanson]

Oh wow! That method is brand new 🆕 as of Rails 6.1! Thanks for the heads up. I think that'll be killer for new repos (or my existing repos, once I can get them upgraded 😅).