How to create a record only if it does not exist, avoid duplication and does not cause any error?

Question

It is well known that Model.find_or_create_by(X) valid:

• select X
• if nothing is found → create by X
• return the record (found or created).

and between stages 1 and 2. there may be a race condition. To avoid duplicating X in the database, you should use a unique index in the set of fields X. But if you use a unique index, one of the competing transactions will fail (when you try to create a copy of X).

How can I implement the "safe version" #find_or_create_by , which would never throw exceptions and always work as expected?

Answer 1

The answer is in the doc

Whether this is a problem or not depends on the application logic, but in a particular case where strings have a UNIQUE constraint, an exception may be thrown, just try again:

 begin CreditAccount.find_or_create_by(user_id: user.id) rescue ActiveRecord::RecordNotUnique retry end 

---

Solution 1

In your model (s) or Concern, you can implement the following: if you need to stay dry

 def self.find_or_create_by(*) super rescue ActiveRecord::RecordNotUnique retry end 

Usage: Model.find_or_create_by(X)

---

Decision 2

Or, if you do not want to overwrite find_or_create_by , you can add the following model (s)

 def self.safe_find_or_create_by(*args, &block) find_or_create_by *args, &block rescue ActiveRecord::RecordNotUnique retry end 

Usage: Model.safe_find_or_create_by(X)

Answer 2

Rails has a method called find_or_create_by

This link will help you better understand it.

But personally, I prefer to find first, and if nothing is found, then create (I think it has more control)

 Ex: user = User.find(params[:id]) #User.create(#attributes) unless user 

NTN

Answer 3

This is a recurring SELECT-or-INSERT issue, closely related to the popular UPSERT issue. Upcoming Postgres 9.5 Delivers New INSERT .. ON CONFLICT DO NOTHING | UPDATE INSERT .. ON CONFLICT DO NOTHING | UPDATE to provide clean solutions for everyone.

Implementation for Postgres 9.4

I am currently proposing this bulletproof implementation using the two server side plpgsql functions. Only the helper function for INSERT implements the more expensive error trapping and is called only if SELECT not performed.

This one never throws an exception due to a unique violation, but always returns a string.

Assumptions:

• Suppose a table named tbl has a column x of the text data type. Adapt accordingly to your business.

• x defined by UNIQUE or PRIMARY KEY .

• You want to return the entire row from the base table ( return a record (found or created) ).

• In many cases, the string already exists. (There should not be most cases, SELECT much cheaper than INSERT .) Otherwise, it might be more efficient to try INSERT .

Auxiliary function:

 CREATE OR REPLACE FUNCTION f_insert_x(_x text) RETURNS SETOF tbl AS $func$ BEGIN RETURN QUERY INSERT INTO tbl(x) VALUES (_x) RETURNING *; EXCEPTION WHEN UNIQUE_VIOLATION THEN -- catch exception, no row is returned -- do nothing END $func$ LANGUAGE plpgsql; 

The main function:

 CREATE OR REPLACE FUNCTION f_x(_x text) RETURNS SETOF tbl AS $func$ BEGIN LOOP RETURN QUERY SELECT * FROM tbl WHERE x = _x UNION ALL SELECT * FROM f_insert_x(_x) -- only executed if x not found LIMIT 1; EXIT WHEN FOUND; -- else keep looping END LOOP; END $func$ LANGUAGE plpgsql; 

Call:

 SELECT * FROM f_x('foo'); 

SQL Fiddle Script

The function is based on what I developed in this related answer:

• Is SELECT or INSERT a function prone to race conditions?

Detailed explanation and links there.

We could also create a generic function with polymorphic return type and dynamic SQL to work in any columns and tables (but beyond the scope of this question):

• Refactoring the PL / pgSQL function to return the output of various SELECT queries

The basics for UPSERT in this related Craig Ringer answer:

• How to run UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?