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?

Answer 1

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!)