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

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 2

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