mardi 31 janvier 2017

How do I query the database after a Postgres exception?

I'm having some trouble handling concurrency issues when inserting into a Postgres database. The model has a uniqueness constraint on an index column, and so does the Postgres table. Sometimes two threads attempt to insert the same record at the same time (this is unavoidable), in which case both pass the model validation, but the second one violates the Postgres validation. So I catch the exception and everything is ok. This is not the problem.

The problem is my method needs to return the object from the database, so I query the db to get the record inserted by the first thread (I can safely assume it's the same as the one in the second thread). However this fails because the transaction is still in an invalid state due to the failed insert.

My question is: how can I avoid the second exception thrown within the rescue block, and/or enable the method to return the record that was inserted by the first thread?

  class Place
      validates :index_column, uniqueness: true, allow_nil: true

    def self.create_and_insert(some_params)
      more_params = additional_params(some_params)
      place = Place.new(some_params, more_params)

      begin
        place.save  # Insert into place table. This initiates a transaction.
      rescue ActiveRecord::RecordNotUnique => e
        # Oops! Another thread beat us to it.
        # The transaction is now in an invalid state.
        place = Place.find_by(index_column: some_params.id) # This throws a new exception
      end

      place

    end
  end

Aucun commentaire:

Enregistrer un commentaire