Why am I getting ExclusiveLock errors during a rails migration on postgres

Question

getting error when trying to run a migration

WARNING: you don't own a lock of type ExclusiveLock rake aborted! StandardError: An error has occurred, this and all later migrations canceled:

the migration looks something like

def up
    ActiveRecord::Base.establish_connection
    ActiveRecord::Base.connection.execute(
      File.read(File.expand_path('setup.sql'))
    )

    change_column :staff_members, :id, :integer, auto_increment: true
end 

The sql file branches into different databases, and this particular table needs to redefine its id column that's all.

Answer 1

The solution I discovered was to manually close the connection ActiveRecord::Base.connection.close

Answer 2

I had the same issue but under different circumstances.

I wrote a generic rake task that allows me to run any SQL script file and found that it would work fine when the rake task was run directly, but would fail if run as part of a migration, resulting in the ExclusiveLock warning and error reported by the OP.

I tried explicitly closing the connection in the rake task but that didn't solve the problem for me.

Eventually, with some extra logging, I figured out that the connection was already established and that there was no need for me to establish it explicitly in that context (I originally got the idea to do that from this SO question). My environment: Rails 5.1.4 on Ruby 2.3.4

Removing the ActiveRecord::Base.establish_connection call solved the issue for me and my rake task now runs happily inside or outside of a migration.

I later noticed PG::ConnectionBad: connection is closed: ROLLBACK and PG::ConnectionBad: connection is closed: COMMIT errors while going over some logs, but these may have been caused by explicitly closing the connection (it looks like the migration tries to COMMIT but fails because the connection is closed, then tries to ROLLBACK but fails for the same reason).