4

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.

jsharpe
  • 2,546
  • 3
  • 26
  • 42
  • SELECT ... FOR SHARE comes to mind, and checking to see if it's already there. What I turned out doing for possible-duplicate data is simply making each INSERT a separate transaction. – freeone3000 Apr 24 '12 at 23:45
  • right, but then all my logic can't be nicely wrapped up inside of the save call – jsharpe Apr 24 '12 at 23:55
  • Does ActiveRecord give you any way to access PostgreSQL subtransaction -- like savepoints? http://www.postgresql.org/docs/current/interactive/sql-savepoint.html ... or EXCEPTION clauses in plpgsql? http://www.postgresql.org/docs/current/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING – kgrittn Apr 25 '12 at 02:22
  • 1
    The intended behaviour for a DBMS on a constraint violation is to rollback the current transaction (and close all cursors). It is the only sane thing to do. If you don't want that to happen, then don't attempt to insert duplicates, or instruct your framework not to insert duplicates. – wildplasser Apr 25 '12 at 10:17
  • okay, so that's fine, my pattern wasn't sane (no surprise there). but given that it still makes sense to keep this inside the #save transaction, what pattern can i follow in postgresland? – jsharpe Apr 25 '12 at 15:05

1 Answers1

4

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.

Community
  • 1
  • 1
Matthew Wood
  • 16,017
  • 5
  • 46
  • 35