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

rails migration belongs_to
rails migration up down
rails data migration
rails migration transaction
rails migration remove column
rails db:rollback
rails migration naming convention
rails migration join table

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.

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

How do I run a migration without starting a transaction in Rails?, So I'd like to try running it without getting wrapped by a transaction. but I'm still curious to know how to instruct Rails to run migrations without transactions. transactions can be turned off per Migration. Closes #9483. There are SQL Queries that can't run inside a transaction. Since the Migrator used to wrap all Migrations inside a transaction there was no way to run these queries within a migration. This patch adds `self.disable_ddl_transaction!` to the migration to turn transactions off when necessary.

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

Migrations, On databases that support transactions with statements that change the schema When Bob runs rake db:migrate, Rails knows that it has not run Alice's two If you are creating migrations for other purposes (for example to add a column to  In its most basic form it just runs the up or change method for all the migrations that have not yet been run. If there are no such migrations, it exits. It will run these migrations in order based on the date of the migration. Note that running the db:migrate also invokes the db:schema:dump task,

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

Disabling transaction block during migration, By default, all migrations run inside a transaction. because your entire migration will have transactions disabled, not just for DDL SQL statements. Ruby. @rafaelfranca since the migration is always wrapped inside a transaction, wouldn't we need a second connection to execute statements outside the transaction? What do you think if we take the oposite route and let the user disable the wrapping transaction for a migration. He can still choose to open transactions within the migration:

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.

Rails Migrations with Zero Downtime, Lets understand the challenge of changing a Rails database without introducing any block.call # do not start a transaction end def self.up execute "CREATE INDEX Running migrations with no downtime takes a lot of planning, and work. When you delete migration files in the db/migrate/ directory, any environment where bin/rails db:migrate was run when those files still existed will hold a reference to the migration timestamp specific to them inside an internal Rails database table named schema_migrations. This table is used to keep track of whether migrations have been executed in a specific environment.

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

Wrap Migration in Transaction - Rails, If so, why not? And, is it possible at all to wrap a rake migrate in a DB transaction​? I'v… “My Error” is thrown but the transaction is not rolled back. Is raising you do in a migration: adding/removing indexes, ALTER TABLE, creating tables. So you do in your migration will commit the transaction. Fred. If instead of the database-agnostic schema.rb file you'd like to save the database-specific SQL generated by the migrations, simply add this to your Rakefile. require 'tasks/standalone_migrations' ActiveRecord::Base.schema_format = :sql. You should see a db/structure.sql file the next time you run a migration.

Disable Rails migration Transaction wrapper, TIL is an open-source project by Hashrocket that exists to catalogue the sharing Rails Migrations are wrapped by a database transaction by default, so if you def change # run some code with no db transaction end end. Getting Started with RailsThis guide covers getting up and running with Ruby on Rails.After reading this guide, you will know: How to install Rails, create a new Rails application, and connect your application to a database. The general layout of a Rails application. The basic principles of MVC (Model, View, Controller) and RESTful design. How to quickly generate the starting pieces of a Rails

Data Migrations in Rails, The first obvious option that comes to mind is to use a Rails migration, include a description and thus we will not see the task when running rake -T . #{users.​count} users" ActiveRecord::Base.transaction do users.each do  Since this commit: rails/rails#22122 rails tries to acquire an advisory lock when running migrations; this is intended to avoid the (unlikely) case of multiple migrations running concurrently. However this fails when the app is accessing a Postgres db through pgbounce in transaction pooling mode (which is the only mode that gives reasonable

ActiveRecord::Migration, To run migrations against the currently configured database, use rails db:migrate. end def down # not much we can do to restore deleted data raise Remember that you can still open your own transactions, even if you are in a Migration  This is useful if you don't exactly know what the last migration applied to the database was or if you are deploying to multiple databases that may each be at a different migration. Apply migrations at runtime. Some apps may want to apply migrations at runtime during startup or first run. Do this using the Migrate() method.

Comments
  • Please tell a bit more about the migration you're running, the database you're using and which adapter in case it's not the default mysql/sqlite ones. That way I think a more usable answer will follow your question.
  • Sorry, just saw you're using Postgres.
  • In the case of this particular migration I found out that the VACUUM command isn't really necessary (it only does garbage collection), so removing that call worked, but I'm still curious to know how to instruct Rails to run migrations without transactions.
  • Given that my original post is over three years old, I wouldn't necessarily expect this to work anymore.
  • I have tried most of the solutions on this page, but none worked. This gist did work for Rails 3.2. Basically ended/restarted a transaction with a ddl_transaction patch.
  • Note that the referenced gist has a bug -- the override of ddl_transaction has no effect since the method is private.
  • Cleanest work-around for old rails applications that can't use the new disable_ddl_transaction!
  • Note you'd probably want to add execute("START TRANSACTION") after the while loop
  • Can anyone confirm that this works for rails ~> 3.2.6? I tried it but it had no effect.
  • Wow, none of the above solutions worked, but this is really helpful. Thanks!
  • This is actually the least hackish of all the ways I've found around :)