find_or_create race conditions

I'm trying to use ActiveRecord's find_or_create_by_*column*, but I'm getting errors from Postgres letting me know that it occasionally fails to find the model, and tries to insert one anyways. It's really important that I keep this table unique, so I added a :unique => true attribute to its migration, so that Postgres would know that I was serious about it.

And, fail:

ActiveRecord::StatementInvalid: PGError: ERROR: duplicate key value violates unique constraint "index_marketo_leads_on_person_id" DETAIL: Key (person_id)=(9968932) already exists. : INSERT INTO "marketo_leads" ("mkt_person_id", "synced_at", "person_updated_at", "person_id") VALUES(NULL, NULL, '2011-05-06 12:57:02.447018', 9968932) RETURNING "id"

I have models like so:

class User < AR::Base
  has_one :marketo_lead

  before_save :update_marketo_lead

  def update_marketo_lead
    if marketo_lead
      if (User.marketo_columns &开发者_如何转开发amp; self.changes.keys).any?  
        marketo_lead.touch(:person_updated_at) 
      end
    elsif self.id
      marketo_lead = MarketoLead.find_or_create_by_person_id(:person_updated_at => Time.now, :person_id => self.id) 
    end
  end
end

class MarketoLead
  belongs_to :user, :foreign_key => 'person_id'
end

The second model is used for linking our users accounts to the Marketo email server, and keeping a record of the last time certain fields of the user was modified, so that we can push changed records in batched background tasks.

I can't think of any reason for this callback, update_marketo_lead to fail, other than some kind of race condition that I can't quite imagine.

(please ignore the horribleness of 'user' sharing a primary key with 'person') (using Rails 2.3.11, Postgres 9.0.3)

Its quite possible that when find_or_create was executed, matching person_id was not found, so create logic was used, however its possible that between find_or_create and actual user.save, another request managed to complete save transaction and at that point your Database constraint caused this exception.

What I would recommend is to catch StatementInvalid exception and to retry saving(up to a finite number of times...

begin
   user.save!
rescue ActiveRecord::StatementInvalid => error
  @save_retry_count =  (@save_retry_count || 5)
  retry if( (@save_retry_count -= 1) > 0 )
  raise error
end

Note this should be executed wherever you try to save the user. All callbacks and validations are happening within save! transaction

P.S. Im assuming your version of rails supports transactions :) In Rails 3 its unnecessary to wrap save! in transaction because it already uses one internally

I'm hitting this inside a sidekick job that retries and gets the error repeatedly and eventually clears itself. I'm not convinced its a race condition from another request or it would be really rare and only happen once or twice but not 11 consecutive times like I'm seeing. The best explanation I've found is on a blog post here. The gist is that postgres keeps an internally stored value for incrementing the primary key that gets messed up somehow. This rings true for me because I'm setting the primary key and not just using an incremented value so maybe that's how this cropped up. The solution from the comments in the link above appears to be to call ActiveRecord::Base.connection.reset_pk_sequence!(table_name)

I can't verify this yet because I couldn't repro the issue, but my attempted fix, modified from Vladimir's fix above is:

begin
   user.save!
rescue ActiveRecord::StatementInvalid => error
   @save_retry_count =  (@save_retry_count || 1)
   ActiveRecord::Base.connection.reset_pk_sequence!(:user)
   retry if( (@save_retry_count -= 1) >= 0 )
   raise error
end

So if this doesn't fix it on the first try I'll see an error raised