postgres database errors forcing transaction to be restarted
activerecord, postgresql
Solution
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.
Problem
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.