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