CODEWITHSUNDEEP
postgresqlactiverecord
jsharpe
jsharpe

Reputation: 2665

postgres database errors forcing transaction to be restarted

TL;DR: Inserting a duplicate join-table record inside an AR::Base save transaction fails (because of a unique constraint) causing the save to fail and rollback. Not adding the duplicate join table record is fine. Not saving is bad.

I'm migrating a mysql app to postgres... I used to follow a pattern something like this in mysql-land to add join-table records to the DB:

class EventsSeries < ActiveRecord::Base
  #  UNIQUE KEY `index_events_series_on_event_id_and_series_id` (`event_id`,`series_id`)
  belongs_to :event
  belongs_to :series
end

class Series < ActiveRecord::Base

  has_many :events_series
  before_validation :add_new_event

private

  def add_new_event
    # boils down to something like this
    EventSeries.new.tap do |es|
      es.event_id = 1
      es.series_id = 1
      begin
        es.save!
      rescue ActiveRecord::RecordNotUnique
        # Great it exists
        # this isn't really a problem
        # please move on
      end
    end
  end
end

invoked like this:

Series.first.save 
# should not blow up on duplicate join record, cause i don't care

However, postgres blows up on this. There's a good explanation here:

http://api.rubyonrails.org/classes/ActiveRecord/Transactions/ClassMethods.html

...in the "Exception handling and rolling back" section (see the Warning)

Basically #save starts a transaction, and the duplicate record insert causes a database exception, which invalidates #save's transaction, which is sadface.

Is there a better pattern for this that can be used in postgres-land?

Thanks!

Edit:

I firmly believe it makes sense to keep this logic inside Series' save transaction... the pattern looks like this:

s = Series.new
s.new_event_id = 123 # this is just an attr_accessor
s.save # callbacks on Series know how to add the new event.

... it makes my controllers super small.

Upvotes: 4

Views: 1056

Answers (1)

Matthew Wood
Matthew Wood

Reputation: 16417

If you are inside a transaction and with to recover from an error and avoid invalidating the whole transaction, you must use savepoints.

When you use the command SAVEPOINT some-label, you can later run the command ROLLBACK TO SAVEPOINT some-label to return to that state in the transaction and ignore all actions after the savepoint was taken (including errors).

Please see my other answer at Continuing a transaction after primary key violation error for a more through explanation.

Upvotes: 4

Related Questions