search

gocardless / activerecord-safer_migrations

Safer ActiveRecord migrations for Postgres
MIT License
117 stars 9 forks source link

Proposal: Support for pgBouncer in transaction-pooling mode? #89

Closed dmagliola closed 3 years ago

dmagliola commented 3 years ago

Hi y'all!

This is not so much an "issue" as a proposal, I'd like to get your thoughts on whether a contribution would be a terrible idea / whether you'd accept it into the gem, before I go and write the code.

As you know (and clearly state in the README), this gem doesn't work with pgBouncer in transaction-pooling mode, because it sets session-level settings. Unfortunately for us, the way our Heroku app is set up, this means that we can't use this gem, and i would really, really like to.

My main question is... Could we get around that by using SET LOCAL for the lock_timeout and statement_timeout settings?

The proposal being I could add a PgBouncerTransactionPoolingAdapter (name TBD), analogous to PostgreSQLAdapter, that does SET LOCAL instead of UPDATE pg_settings. And the user can configure which one they want to use in an initializer, just like they configure the default timeouts.

I realize this won't work when using disable_ddl_transaction!, but I believe we could detect that, raise, and tell the user to also disable the timeouts if they disable transactions. This is not ideal, but it still protects us on the vast majority of our migrations.

Is there a reason why SET LOCAL wouldn't work as I expect, that'd make this a bad idea? And if not, would you accept a PR that implements this?

Thanks!

dmagliola commented 3 years ago

So, it turns out, there is an easier way to do this, which I didn't originally find while scouring the docs, but it's there 🤦‍♂️. You can actually set some types of dynos to connect directly to the database, so this becomes moot.

So, for future reference if anyone has this problem...

If you are using the heroku pgBouncer buildpack to have a pgBouncer per dyno, and you are running your migrations in a release dyno, you can make just that dyno have different pgBouncer settings, by adding inline environment variables to the Procfile.

release: PGBOUNCER_POOL_MODE=session your_release_script

I'm gonna go ahead and close this issue now.