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