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

Question

I'm running some bizarre Postgres migration code from OpenCongress and I'm getting this error:

RuntimeError: ERROR     C25001  MVACUUM cannot run inside a transaction block
Fxact.c  L2649   RPreventTransactionChain: VACUUM FULL ANALYZE;

So I'd like to try running it without getting wrapped by a transaction.

Answer 1

Rails 4 + There is a method disable_ddl_transaction!, you 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 Rails 4

Like some of answers above, there is a simple hack, you can commit the transaction and then after your migration has completed you again the begin the transaction, like 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 cant create/drop index concurrently, as these cannot be executed in a transaction. If you try you will get error "PG::ActiveSqlTransaction: ERROR: DROP INDEX CONCURRENTLY cannot run inside a transaction block."

Answer 2

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

Answer 3

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

Answer 4

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

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

so...:

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

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

Answer 5

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 migration.

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

Answer 6

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

I 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 to do in your migration is:

class PopulateTrees < ActiveRecord::Migration
  include NoMigrationTransactions
end

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

Answer 7

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

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?
  true
end

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

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