How can I determine if my ActiveRecord object violates the unique database key / index?

Question

ActiveRecord validates_uniqueness_of is vulnerable to race conditions . To truly ensure uniqueness, extra precautions are needed. One of the suggestions from RDocs ActiveRecord is to create a unique index in the database, for example, by including in its migrations:

 add_index :recipes, :name, :unique => true 

This ensures at the database level that the name is unique. But the disadvantage of this approach is that the ActiveRecord::StatementInvalid exception that is returned when trying to save a duplicate is not very useful. You cannot be sure that you will understand this exception, that the error was generated by a double copy, and not just broken SQL.

One solution, as suggested by RDocs, is to parse the message that comes with the exception and try to find words such as "duplicate" or "unique," but this is kludgy, and the message is database specific. For SqlLite3, I understand that the message is completely general and cannot be analyzed this way at all.

Given that this is a major issue for ActiveRecord users, it would be nice to know if there is any standard approach to handling these exceptions. I will offer my suggestion below; comment or provide alternatives; thanks!

Answer 1

Parsing the error message is not so bad, but it feels stupid. The suggestion I came across (I don’t remember where) seems attractive is that in the rescue block you can check the database to see if there is actually a duplicate record. If so, then it is likely that StatementInvalid is the result of duplication, and you can handle it accordingly. If this does not happen, then StatementInvalid should be from something else, and you should handle it differently.

So, the main idea, assuming a unique index on recipe.name , as stated above:

 begin recipe.save! rescue ActiveRecord::StatementInvalid if Recipe.count(:conditions => {:name => recipe.name}) > 0 # It a duplicate else # Not a duplicate; something else went wrong end end 

I tried to automate this check as follows:

 class ActiveRecord::Base def violates_unique_index?(opts={}) raise unless connection unique_indexes = connection.indexes(self.class.table_name).select{|i|i.unique} unique_indexes.each do |ui| conditions = {} ui.columns.each do |col| conditions[col] = send(col) end next if conditions.values.any?{|c|c.nil?} and !opts[:unique_includes_nil] return true if self.class.count(:conditions => conditions) > 0 end return false end end 

So now you can use generic_record.violates_unique_index? in its escape block to decide how to process StatementInvalid.

Hope this helps! Other approaches?

Answer 2

Is this really such a big problem?

If you use a unique index along with the validates_uniqueness_of constraint, then

• Maintaining data integrity will
• In the worst case, only get an error when two separate queries try to insert a unique row at the same time

So, if you don't have an application that makes a lot of potential duplicate inserts (in this case, I would look at redesigning them), I see that this is rarely a problem in practice.