[ACCEPTED]-How do I run a migration without starting a transaction in Rails?-migration

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}
  end
end

0

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

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

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

In ActiveRecord::ConnectionAdapters::PostgreSQLAdapter you have:

def supports_ddl_transactions?
  true
end

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

def supports_ddl_transactions?
  sqlite_version >= '2.0.0'
end

So then, to skip 4 transactions, you need to somehow circumvent 3 this. Something like this might work, though 2 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
    end
end

You could then set up 1 your migration as follows:

class SomeMigration < ActiveRecord::Migration
  no_transaction

  def self.up
    # Do something
  end

  def self.down
    # Do something
  end
end

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

This 5 immediately closes the Rails-started transaction, and 4 allows you to write raw SQL that can create 3 its own transactions. In the below example, I 2 use an .update_all and a subselect LIMIT to handle updating 1 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
  end
end

Rails 4 + There is a method disable_ddl_transaction!, you 11 can use it in your migration file like below.

class AddIndexToTable < ActiveRecord::Migration
  disable_ddl_transaction!

  def change
    add_index :table, :column, algorithm: :concurrently
  end
end

Below 10 Rails 4

Like some of answers above, there 9 is a simple hack, you can commit the transaction 8 and then after your migration has completed 7 you again the begin the transaction, like 6 below

class AddIndexToTable < ActiveRecord::Migration
  def change
    execute "COMMIT;"

    add_index :table, :column, algorithm: :concurrently

    # start a new transaction after the migration finishes successfully
    execute "BEGIN TRANSACTION;"
  end
end

This can be helpful in case where we 5 cant create/drop index concurrently, as 4 these cannot be executed in a transaction. If 3 you try you will get error "PG::ActiveSqlTransaction: ERROR: DROP 2 INDEX CONCURRENTLY cannot run inside a transaction 1 block."

The above answer is broken for Rails 3 as 11 ddl_transaction was moved into ActiveRecord::Migrator. I 10 could not figure out a way to monkey patch 9 that class, so here is an alternate solution:

I 8 added a file under lib/

module NoMigrationTransactions
  def self.included(base)                                                                                                                  
    base.class_eval do
      alias_method :old_migrate, :migrate

      say "Disabling transactions"

      @@no_transaction = true
      # Force no transactions
      ActiveRecord::Base.connection.instance_eval do
        alias :old_ddl :supports_ddl_transactions?

        def supports_ddl_transactions?
          false
        end
      end

      def migrate(*args)
        old_migrate(*args)

        # Restore
        if @@no_transaction
          say "Restoring transactions"
          ActiveRecord::Base.connection.instance_eval do
            alias :supports_ddl_transactions? :old_ddl
          end
        end
      end
    end
  end
end

Then all you have 7 to do in your migration is:

class PopulateTrees < ActiveRecord::Migration
  include NoMigrationTransactions
end

What this does 6 is disable transactions when the migration 5 class is loaded (hopefully after all previous 4 ones were loaded and before any future ones 3 are loaded), then after the migration, restore 2 whatever old transaction capabilities there 1 were.

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

rake db:migrate:up VERSION=20120801151807

where 3 20120801151807 is the timestamp of the migration.

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

As hacky as this is adding 'commit;' to 5 the beginning of my sql worked for me, but 4 that's for SQL Server, not sure if this 3 works for postgres...

Example: CREATE FULLTEXT INDEX ... is illegal 2 inside a sql-server user transaction.

so...:

execute <<-SQL
    commit;
    create fulltext index --...yada yada yada
SQL

works 1 fine... We'll see if I regret it later.