How do I query the database after a Postgres exception?

Question

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

@archana ActiveRecord::StatementInvalid: PG::InFailedSqlTransaction: ERROR: current transaction is aborted, commands ignored until end of transaction block : SELECT "places".* FROM "places" WHERE "places"."id" = 'some_id' LIMIT 1 — Barry Fruitman, Jan 31 '17 at 19:23
@archana So basically, the transaction hasn't ended. I didn't start a transaction but `save` did. Implementing `after_rollback` won't help because I need to return a value from `create_and_insert`. Can I roll it back from within `create_and_insert`? — Barry Fruitman, Jan 31 '17 at 19:24
Here's the solution, just a quick google search: http://stackoverflow.com/questions/21138207/activerecordstatementinvalid-pg-infailedsqltransaction — archana, Jan 31 '17 at 19:33

score 2 · Accepted Answer · answered Feb 01 '17 at 06:19

2

In PostgreSQL, you set a savepoint inside a transaction so that you can roll back the part of the transaction that caused the error; this is called a subtransaction.

You can use this in Ruby on Rails, see the documentation.

answered Feb 01 '17 at 06:19

Laurenz Albe

209,280
17
206
263

How do I query the database after a Postgres exception?

1 Answers1