Databases
StackOverflow2013
Postsdbo
POpostgres database errors forcing transaction to be restarted
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POpostgres database errors forcing transaction to be restarted
    primarykey
    data
    text
    <p>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.</p> <hr> <p>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:</p> <pre><code>class EventsSeries &lt; 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 &lt; 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 </code></pre> <p>invoked like this:</p> <pre><code>Series.first.save # should not blow up on duplicate join record, cause i don't care </code></pre> <p>However, postgres blows up on this. There's a good explanation here:</p> <p><a href="http://api.rubyonrails.org/classes/ActiveRecord/Transactions/ClassMethods.html" rel="nofollow">http://api.rubyonrails.org/classes/ActiveRecord/Transactions/ClassMethods.html</a></p> <p>...in the "Exception handling and rolling back" section (see the Warning)</p> <p>Basically #save starts a transaction, and the duplicate record insert causes a database exception, which invalidates #save's transaction, which is sadface.</p> <p>Is there a better pattern for this that can be used in postgres-land?</p> <p>Thanks!</p> <hr> <p>Edit:</p> <p>I firmly believe it makes sense to keep this logic inside Series' save transaction... the pattern looks like this:</p> <pre><code>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. </code></pre> <p>... it makes my controllers super small.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjsharpe
    1. USjsharpe
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POpostgres database errors forcing transaction to be restarted
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POpostgres database errors forcing transaction to be restarted
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSELECT ... 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.
      singulars
      1. POpostgres database errors forcing transaction to be restarted
      1. USfreeone3000
    2. COright, but then all my logic can't be nicely wrapped up inside of the save call
      singulars
      1. POpostgres database errors forcing transaction to be restarted
      1. USjsharpe
    3. CODoes 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
      singulars
      1. POpostgres database errors forcing transaction to be restarted
      1. USkgrittn
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload