Migrations run against pgbouncer in transaction-pooling mode can fail with `ActiveRecord::ConcurrentMigrationError`
Summary
When pgbouncer is in transaction pooling mode, migrations can fail with ActiveRecord::ConcurrentMigrationError
:
WARNING: you don't own a lock of type ExclusiveLock
rake aborted!
ActiveRecord::ConcurrentMigrationError:
Failed to release advisory lock
This happens beginning from 12.1 as we upgraded to Rails 5.2, which now throws an exception instead of failing silently.
More details here: https://help.heroku.com/UYH8N2WW/why-do-i-receive-activerecord-concurrentmigrationerror-when-running-rails-migrations-using-pgbouncer
The current workaround is to do the following in PSQL:
SELECT pid, locktype, mode FROM pg_locks WHERE locktype = 'advisory';
SELECT pg_terminate_backend(<PID>);
Might have to retry a few times before it runs to completion.
Alternatively, the migrations may have run to completion but we're having trouble deleting the lock. Use gitlab-rake db:migrate:status | grep down
to check and repeat the steps above to release the lock.
Other workarounds are documented in the Heroku link above.
This was reported (Zendesk, internal use) by a 4000-seat premium customer.
Additional reading here: https://blog.saeloun.com/2019/09/09/rails-6-disable-advisory-locks.html
Steps to reproduce
N/A
Example Project
N/A
What is the current bug behavior?
(What actually happens)
What is the expected correct behavior?
(What you should see instead)
Relevant logs and/or screenshots
(Paste any relevant logs - please use code blocks (```) to format console output, logs, and code as it's tough to read otherwise.)
Output of checks
(If you are reporting a bug on GitLab.com, write: This bug happens on GitLab.com)
Results of GitLab environment info
Expand for output related to GitLab environment info
(For installations with omnibus-gitlab package run and paste the output of:
sudo gitlab-rake gitlab:env:info
)(For installations from source run and paste the output of:
sudo -u git -H bundle exec rake gitlab:env:info RAILS_ENV=production
)
Results of GitLab application Check
Expand for output related to the GitLab application check
(For installations with omnibus-gitlab package run and paste the output of:
sudo gitlab-rake gitlab:check SANITIZE=true
)(For installations from source run and paste the output of:
sudo -u git -H bundle exec rake gitlab:check RAILS_ENV=production SANITIZE=true
)(we will only investigate if the tests are passing)
Possible fixes
One thing that we might consider doing is to include a monkey patch to disable migration advisory lock based on the presence of an ENV VAR, as documented in the Heroku link. Here's an example: https://github.com/discourse/discourse/blob/7b412727bbf84227d507f56c381318c19d983862/lib/freedom_patches/postgresql_adapter.rb