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 & 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With