ActiveRecord::ConcurrentMigrationError when running Rails databases migrations, using a connection that is pooled by PgBouncer (either through the PgBouncer buildpack, or Connection Pooling).
Rails 5.2 introduced new handling of releasing an advisory lock, obtained when performing migrations, to prevent concurrent migrations (ref: https://github.com/rails/rails/pull/31189). When the lock fails to be released, an exception is raised with an error message to that effect.
As advisory locks are session-local, there is an incompatibility with PgBouncer being used in the
transaction pooling mode, which is the default for both the PgBouncer buildpack (ref: https://github.com/heroku/heroku-buildpack-pgbouncer), and Connection Pooling (ref: https://devcenter.heroku.com/articles/postgres-connection-pooling).
As a result, migrations may fail due to an advisory lock is not being found, and therefore is unable to be released. This incompatibility can also leave advisory locks active.
There are three options to resolve this:
- Open a
psqlsession to your Postgres database, and use
SELECT pid, locktype, mode FROM pg_locks WHERE locktype = 'advisory'to identify the PID of Postgres backends that are currently holding an advisory lock. You can then terminate these using either
heroku pg:kill <PID>, or
- Implement a patch to disable the use of advisory locks for migrations, conditional on an environment variable, such as the one used by Discourse (ref: https://github.com/discourse/discourse/blob/7b412727bbf84227d507f56c381318c19d983862/lib/freedom_patches/postgresql_adapter.rb).
sessionpooling for PgBouncer, where available.
What options are suitable depend on your use cases.