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

user_105132 0
Viewed 13289

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.

migration postgresql transactions ruby-on-rails database
7 Answers

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

user_25086510
Related