PgBouncer and prepared statements

[danmaz74]

I'm testing the integration of good_job into a project where we use PgBouncer in transaction mode, and we've got this configuration in db.rb:

config['prepared_statements'] = false

When trying to run a job with this configuration, I get this error:

PG::ProtocolViolation: ERROR: bind message supplies 0 parameters, but prepared statement "" requires 2

from the query executed by advisory_lock in lockable.rb

The same works if I change the config to prepared_statements = true

I didn't find anything with a search in the project issues, and I'm wondering if good_job is compatible with this setting; if not, I'm wondering if it wouldn't be worth it to make it compatible (either by default, or with some specialised code as it's already done - with a simpler case - in pg_or_jdbc_query).

[bensheldon]

Hi @danmaz74. Thanks for opening the issue. I believe the underlying issue you're experiencing is an incompatibility with PgBouncer's transaction-mode. I'm planning to add this to the readme; your feedback is invaluable:

PgBouncer compatibility

GoodJob is not compatible with PgBouncer in transaction mode. GoodJob uses connection-based advisory locks and requires a dedicated database connection for the duration of a job's execution.

With Rails 6.0's support for multiple databases, a direct connection to the database can be configured.

1. Define a direct connection to your database that is not proxied through PgBouncer, for example:

   # config/database.yml
   
   production:
     primary:
       url: postgres://pgbouncer_host/my_database
     primary_direct:
       url: postgres://database_host/my_database

2. Create a new ActiveRecord base class that uses the direct database connection

   # app/models/application_direct_record.rb
   
   class ApplicationDirectRecord < ActiveRecord::Base
     self.abstract_class = true
     connects_to database: :primary_direct
   end

3. Configure GoodJob to use the newly created ActiveRecord base class:

   # config/initializers/good_job.rb
   
   GoodJob.active_record_parent_class = "ApplicationDirectRecord"

[danmaz74]

Thanks a lot @bensheldon for the in-depth answer and quick turnaround. I just checked and found out that transaction pooling mode is also incompatible with listen/notify, which is also needed by good_job from what I read: https://www.pgbouncer.org/features.html

I'll check with our SRE team if having a direct connection for good_job is an option. Definitely a good idea to add it to the readme :)

[bensheldon]

I added these details to the Readme. Happy to continue the conversation.