How to stop Rails 3.1 migration from running in a transaction?

Question

I want to add an index to a production database. Fortunately we're running Postgres, which nicely allows concurrent indexing, so we can add an index without downtime. The catch -- concurrent indexes cannot be added from within a transaction, and rails migrations wrap everything inside a transaction.

Fortunately, there is something that looks like a really simple solution: overwrite the ActiveRecord::Migration private method ddl_transaction, as explained here.

class IndexUsersEmails < ActiveRecord::Migration
  def ddl_transaction(&block)
    block.call # do not start a transaction
  end

  def self.up
    execute "CREATE INDEX CONCURRENTLY index_users_on_email ON users(email)"
  end
end

The problem is that it does not seem to work in Rails 3.1. I do exactly what the code in the Gist does, and rails appears to completely ignore it. Any ideas on where to go with this?

Have you tried changing the method definition so that it's a class method? e.g. `def self.ddl_transaction(&block) ...` — Derek Harmel, Mar 08 '12 at 22:05

score 11 · Accepted Answer · answered Oct 22 '13 at 17:41

I just noticed that I never accepted an answer here, so I should say what I did. Turns out you can get out of the transaction like this:

class AddFbPageIdIndexToTabs < ActiveRecord::Migration
  def up
    execute "END"
    execute "CREATE INDEX CONCURRENTLY bob_lob_law_index ON bob_lob (law)"
    execute "BEGIN"
  end

  def down
    execute "END"
    execute "DROP INDEX CONCURRENTLY bob_lob_law_index"
    execute "BEGIN"
  end
end

Just run an execute "END" before the thing you want to run outside the transaction. This will end the transaction that ActiveRecord::Migration automagically set up for the migration. After you are done with the code you want to run outside the transaction, execute "BEGIN" opens a new transaction so that ActiveRecord::Migration can go through its cleanup process and close the transaction that it thinks it opened.

(I forget where online I found this trick, and can't find it now. Edits welcome to source this!)

score 1 · Answer 2 · answered Aug 08 '12 at 15:09

I'm not saying this is the "right way" to do it, but what worked for me was to run just that one migration in isolation.

rake db:migrate:up VERSION=20120801151807

where 20120801151807 is the timestamp of the CREATE INDEX CONCURRENTLY migration.

Apparently, it doesn't use a transaction when you run a single migration.

How to stop Rails 3.1 migration from running in a transaction?

2 Answers2