Search code examples
ruby-on-rails-3postgresqlactiverecordrace-conditionselect-insert

First_or_create yet ERROR: duplicate key value violates unique constraint

I have the following code:

rating = user.recipe_ratings.where(:recipe_id => recipe.id).where(:delivery_id => delivery.id).first_or_create

Yet somehow we get occasional PG::Error: ERROR: duplicate key value violates unique constraint errors from this. I can't think of any reason that should happen, since the whole point of first_or_create is to prevent those.

Is this just a crazy race-condition? How can I solve this without a maddening series of begin...rescue blocks?

Solution

  • This seems to stem from a typical race condition for the "SELECT or INSERT" case.

    Ruby seems to choose performance over safety in its implementation. Quoting "Ruby on Rails Guides":

    The first_or_create method checks whether first returns nil or not. If it does return nil, then create is called.

    ...

    The SQL generated by this method looks like this:

    SELECT * FROM clients WHERE (clients.first_name = 'Andy') LIMIT 1
BEGIN
INSERT INTO clients (created_at, first_name, locked, orders_count, updated_at)
VALUES ('2011-08-30 05:22:57', 'Andy', 0, NULL, '2011-08-30 05:22:57')
COMMIT

    If that's the actual implementation (?), it seems completely open for race conditions. Another transaction can easily SELECT between the first transaction's SELECT and INSERT. And then try its own INSERT, which would raise the error you reported, since the first transaction has inserted the row in the meantime.

    The time frame for a race condition could be drastically reduced with a data-modifying CTE. Even a safe version would not cost that much more. But I guess they have their reasons.
    Compare this safe implementation: