create index concurrently fails in standard migrations

[markmansour-zz]

Creating concurrent indexes cannot be done within a transaction in PG.

All AR migrations occur within a transaction.

If you try, you'll get the following error

PGError: ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

to work around this issue I needed to do the following in my migration

    execute "END"  # manually end the current transaction
    create_index 'index_appointments_on_cancellation_reason', :appointments, { :column => :cancellation_reason}, { :concurrently => true }
    execute "BEGIN" # manually start a new transaction

it would be nice to somehow remove this boilerplate.

[dark-panda]

I'll take a look at this when I have a chance. This looks like it has to do with migrations being wrapped completely in transactions to allow for rollbacks on DDL changes.

[ordinaryzelig]

I think Rails 4 will take care of this

[dark-panda]

Sweet. We aren't ready to make the jump to Rails 4 yet, but I have done some work on Rails 4 compatibility in the rails-4 branch. Wonder if this would be worth a backport.

[jaggederest]

For anyone seeing this from a google search, in modern rails you can add disable_ddl_transaction! to the top of your migration class to avoid this.

[amandakievet]

I got the same error message using Goose for GoLang and this worked --

(removing the execute commands and adding the semicolons)

END;  # manually end the current transaction
CREATE INDEX CONCURRENTLY index_appointments_on_cancellation_reason ON appointments (cancellation_reason);
BEGIN; # manually start a new transaction