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?
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":
If that's the actual implementation (?), it seems completely open for race conditions. Another transaction can easily
SELECT
between the first transaction'sSELECT
andINSERT
. And then try its ownINSERT
, 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: