Devise: PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_users_on_email"

[daraosn]

We have an app using Devise. When sending two or more simultaneous requests trying to register an account using the same email _sometimes_ we get the following error from PostgreSQL:

PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_users_on_email" DETAIL: Key (email)=([email protected]) already exists.

We already have tests for avoiding duplicated emails on Rails side (and Devise already implements validates_uniqueness_of :email), but it seems like when two requests are sent simultaneously, the first requests writes a new record on database after the upcoming request verifies if the email already exists, so then the second check passes and tries to write again and raises the PG exception, as it's not caught by the Rails app.

I wonder if Devise is using Transactions and if so is there a way to activate it?

Thanks in advance.

[josevalim]

This is a known gotcha of the validates_uniqueness_of helper, as you can still run into race conditions. It is not related to Transactions at all. The validates_uniqueness_of docs have move info about it.

[fpauser]

Could also be an "out of sync" sequence, see: http://hcmc.uvic.ca/blogs/index.php?blog=22&p=8105&more=1&c=1&tb=1&pb=1

[wojtha]

Nice explanation at StackOverflow: http://stackoverflow.com/a/690851/807647 with links to other sources as well as to Rails documentation about it.

A post with workaround at Michael Hartl's blog: http://blog.mhartl.com/2008/06/26/working-around-the-validates_uniqueness_of-bug-in-ruby-on-rails/

Mitigating the issue by preventing the double form submission: http://stackoverflow.com/a/2635675/807647

There is also interesting answer suggesting not to use { :case_sensitive => false }
http://stackoverflow.com/questions/6422211/rails-3-validating-email-uniqueness-and-case-sensitive-fails/6422771#6422771

[daraosn]

@trkrameshkumar this is from last year... I don't remember exactly, but it was a racing condition problem. Please read what @wojtha suggested.

[SixArm]

Same issue on a brand new Rails 4.2 app using default testing i.e. minitest and fixtures.

1) Error:
HomeControllerTest#test_should_get_index:
ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR:  
 duplicate key value violates unique constraint "index_users_on_email"
DETAIL:  Key (email)=() already exists.
: INSERT INTO "users" ("name", "created_at", "updated_at", "id") 
VALUES ('MyString', '2015-01-14 23:48:24', '2015-01-14 23:48:24', 298486374)

Error:
HomeControllerTest#test_should_get_index:
NoMethodError: undefined method `each' for nil:NilClass

 1 runs, 0 assertions, 0 failures, 1 errors, 0 skips

The solve this, edit tests/fixtures/users.yml and add unique email addresses:

one:
  name: Alice
  email: [email protected]
two:
  name: Bob
  email: [email protected]

[dwbutler]

I solved this problem in my application using an advisory lock.

User.with_advisory_lock(email) do
  # Registration logic moves into here
end

The first request will be granted the lock and proceed to create the account. The second request will wait for the lock, and only gets the lock after the first request is finished. The second request can then correctly find that the user account already exists.

The user might still see an error like "Account already exists". But at least the two requests won't interfere with each other and possibly cause bad side effects.

[amitpatelx]

I am facing same issue on production and I am sure there is only one request is being sent.

Please note here that I have used first_or_create! method to create user only if not found for give params.

Suprisingly this works perfectly on my local machine.

[benbonnet]

Hello;
'was batch importing some users on a fresh database with the piece of code below. Got stuck the whole day with this pg::index error.

after_save :check_mail
def check_mail
   @user =  ( User.exists?(email: self.email) ? User.find_by_email(self.email) : User.create(email: self.email, password: pwd, password_confirmation: pwd) ) if self.email.presence
  end

I could only solve it (e.g. getting the expected rails behavior) by downcasing the emails that were passed in this method (the email causing problem was a duplicate of an existing one, but only uppercased)… which seemed a weird workaround. Is this normal that the email case has such impact ?

[SixArm]

@bbnnt Yes, it's normal that the email case matters for the search. The case matters for string comparisons in general, by default, and that's what you're seeing happen. (As far as I can tell, your issue is actually not related to the Devise issue that is on this page, i.e. the race condition, and advisory lock, and minitest collision. The issue that is on this page is describing something that happens even when the case is identical.)