How do I run a migration without starting a transaction in Rails?

There's now a method disable_ddl_transaction! that allows this, e.g.:

class AddIndexesToTablesBasedOnUsage < ActiveRecord::Migration  disable_ddl_transaction!  def up    execute %{      CREATE INDEX CONCURRENTLY index_reservations_subscription_id ON reservations (subscription_id);    }  end  def down    execute %{DROP INDEX index_reservations_subscription_id}  endend

ActiveRecord::Migration has the following private method that gets called when running migrations:

def ddl_transaction(&block)  if Base.connection.supports_ddl_transactions?    Base.transaction { block.call }  else    block.call  endend

As you can see this will wrap the migration in a transaction if the connection supports it.

In ActiveRecord::ConnectionAdapters::PostgreSQLAdapter you have:

def supports_ddl_transactions?  trueend

SQLite version 2.0 and beyond also support migration transactions.In ActiveRecord::ConnectionAdapters::SQLiteAdapter you have:

def supports_ddl_transactions?  sqlite_version >= '2.0.0'end

So then, to skip transactions, you need to somehow circumvent this.Something like this might work, though I haven't tested it:

class ActiveRecord::Migration  class << self    def no_transaction      @no_transaction = true    end    def no_transaction?      @no_transaction == true    end  end  private    def ddl_transaction(&block)      if Base.connection.supports_ddl_transactions? && !self.class.no_transaction?        Base.transaction { block.call }      else        block.call      end    endend

You could then set up your migration as follows:

class SomeMigration < ActiveRecord::Migration  no_transaction  def self.up    # Do something  end  def self.down    # Do something  endend

An extremely simple, Rails-version-independent (2.3, 3.2, 4.0, doesn't matter) way about this is to simply add execute("commit;") to the beginning of your migration, and then write SQL.

This immediately closes the Rails-started transaction, and allows you to write raw SQL that can create its own transactions. In the below example, I use an .update_all and a subselect LIMIT to handle updating a huge database table.

As an example,

class ChangeDefaultTabIdOfZeroToNilOnUsers < ActiveRecord::Migration  def self.up    execute("commit;")    while User.find_by_default_tab_id(0).present? do      User.update_all %{default_tab_id = NULL}, %{id IN (        SELECT id FROM users WHERE default_tab_id = 0 LIMIT 1000      )}.squish!    end  end  def self.down    raise ActiveRecord::IrreversibleMigration  endend