I've been doing a little more digging. I instrumented PG::Connection.async_exec to see everything that gets executed during connection initialisation. It turns out relatively few things are set on the connection[1]. Those things are:
- client_min_messages - standard_conforming_strings - time zone PgBouncer has a safe way <https://wiki.postgresql.org/wiki/PgBouncer#Feature_matrix_for_pooling_modes> of setting the last two when using transaction pooling - specifically: > Startup parameters are: client_encoding, datestyle, timezone, standard_conforming_strings and application_name. PgBouncer can detect their changes so it can guarantee they remain consistent for client. Available from PgBouncer 1.1. If the ActiveRecord maintainers are cool with the approach, I'd like to: - Set standard_conforming_strings and time zone as part of the connection string, rather than using SET statements on an already open connection - Write documentation for using ActiveRecord with PgBouncer (an explanation of the caveats, which features you can and can't use) The last debatable point is client_min_messages. In Rails 4.0, WARNING became the default setting <https://github.com/rails/rails/commit/052e415f22b98bb45a5245ac8f7aa23c6f32e478> to avoid noise in logs. I suspect the solution here is documentation - have people set it to match the Postgres default in database.yml (or conversely use PgBouncer's connect_query to match what ActiveRecord sets). I realise there's a lot of subtle stuff going on here. If anything's unclear, I'm happy to go into more detail. If not, I'd love to hear from a maintainer on whether that proposed solution is 👍 or 👎. [1]: More things may be set depending on the contents of database.yml. For instance, if a schema_search_path <https://github.com/rails/rails/blob/e4000e3aa78b06bdda39ba0a4d5f1cb5f7d21609/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L661> or extra variables <https://github.com/rails/rails/blob/e4000e3aa78b06bdda39ba0a4d5f1cb5f7d21609/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L677> have been provided, those will be set on the connection. On Thursday, October 29, 2015 at 12:11:35 PM UTC, [email protected] wrote: > > An issue people frequently run into when scaling apps that use Postgres is > the high memory use per-connection on the Postgres server (Postgres uses a > process per-connection). This is commonly solved by running PgBouncer in > transaction-pooling mode in front of Postgres to share those connections > between many clients (Heroku recommend this approach > <https://devcenter.heroku.com/articles/concurrency-and-database-connections#limit-connections-with-pgbouncer> > and > even have a buildpack for it > <https://github.com/heroku/heroku-buildpack-pgbouncer>). > > > The big caveat of transaction-pooling mode is that you can't use > session-level features of Postgres > <https://wiki.postgresql.org/wiki/PgBouncer#Feature_matrix_for_pooling_modes> > (e.g. session-level config, session-level advisory-locks). > > > Unfortunately, ActiveRecord makes use of session-level settings, which are > set during connection initialisation. In most cases, it's not a huge > problem. You can use PgBouncer's connect_query > <https://pgbouncer.github.io/config.html#connectquery> to set the same > settings as ActiveRecord (making the ActiveRecord SET commands > effectively no-op). > > > However, in a notable exception, ActiveRecord temporarily disables > reporting of query errors > <https://github.com/rails/rails/blob/f7d0a3ba7e9e676d399e7aeed7485a8bf03992f8/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L286>. > > This leaves random server connections in a bad state, where they won't > report any errors, including constraint violations (see #22101 > <https://github.com/rails/rails/pull/22101>). > > > At a minimum, it feels like #22101 > <https://github.com/rails/rails/pull/22101> should be merged, and some > documentation written for Rails users who are running PgBouncer (we have > some thoughts on what it should be[1] and we're happy to write this). > > > It may also be nice to provide a flag which stops ActiveRecord from using > connection-level settings. This would reduce instances of weird behaviour > where people have a connect_query > <https://pgbouncer.github.io/config.html#connectquery> that is out of > sync with what ActiveRecord does (which itself may change between releases). > > > To be clear, this goes beyond #22101 > <https://github.com/rails/rails/pull/22101>. ActiveRecord sets things > like time zone > <https://github.com/rails/rails/blob/0fb2d1a0ba3360e928a7ac9d20378d7f5fbf915f/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L657> > at > the connection level, so users may get weird behaviour with date/time, > depending which backend connection PgBouncer happens to use for a given > query. > > > From a quick search, it looks like other people are running into this > situation in the wild: > > - rails/rails#14864 <https://github.com/rails/rails/issues/14864> > - rails/rails#18027 <https://github.com/rails/rails/issues/18027> > - https://groups.google.com/forum/#!topic/ruby-pg/QaN4UaSvkyc > > and it would be great if we could do something to help them! > > --- > > (Originally posted as rails/rails#22102 > <https://github.com/rails/rails/issues/22102>, modified since) > > [1]: One option would be to advise that PgBouncer users: > > - Replicate all of Rails' SET commands in connect_query > <https://pgbouncer.github.io/config.html#connectquery> - it would be > useful to list what they are and give advice on values to choose > - Set server_reset_query > <https://pgbouncer.github.io/config.html#serverresetquery> to RESET ALL > <http://www.postgresql.org/docs/9.4/static/sql-reset.html> (or > potentially DISCARD ALL > <http://www.postgresql.org/docs/9.4/static/sql-discard.html>, but that > throws away more than just session settings), and set > server_reset_query_always > <https://pgbouncer.github.io/config.html#serverresetqueryalways> > > -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/rubyonrails-core. For more options, visit https://groups.google.com/d/optout.
