Graceful fallback to polling when LISTEN/NOTIFY isn't available

[ollym]

We're using PgBouncer in production and while you do have a workaround available we'd still prefer to run good_job connections through PgBouncer and not have to split traffic.

While LISTEN/NOTIFY is great and will trigger immediate execution of the job, this isn't a critical requirement, we're using a background job specifically because we don't need immediate execution, and the poller which is already being used because of future scheduled tasks is more than fast enough.

It would be great to either disable LISTEN/NOTIFY using a config setting or gracefully handle the fallback to polling when it fails.

[bensheldon]

In addition, to LISTEN, GoodJob also uses connection-based advisory locks. Foundationally, transaction-based connection-sharing isn't compatible with GoodJob.

Speculatively, I think there's a 1-2 year path for GoodJob where it moves towards being transaction-based rather than connection-based. That would require a significant rewrite of the locking strategy.

[ollym]

@bensheldon we're mainly concerned about scheduling tasks rather than running the job server itself, as we run the job server separately from the web server. We've been running PgBouncer now just for web (which does things like SomeJob.perform_later and it's working fine. My concern was that scheduling a job would have advisory locks/NOTIFY code in it but it appears not so we might be good already.

Will close this issue unless you're aware of another issue we might have?

[bensheldon]

@ollym That sounds right. Enqueuing jobs (execution_mode == :external) is itself compatible with PgBouncer's transaction-based connection sharing. It will result in an INSERT and a NOTIFY (which should be fine), but not a lock or a LISTEN (which is incompatible).

[bensheldon]

I just thought of something though. The way that GoodJob sets up its ActiveRecord model / database connection, there isn't currently a way to separate out the two roles:

• Connection-based database connection (for performing jobs)
• Transaction-based database connection (for enqueuing jobs)

That (potentially) means that when you are enqueuing a job, on your webserver, it will be using the more valuable connection-based database connection.

I'm not sure at the moment what a good strategy would be for that with how Rails Multiple DB configuration works: https://guides.rubyonrails.org/active_record_multiple_databases.html

[ollym]

@bensheldon so we avoided multiple database config anyway and just use the pgbouncer connection everywhere in the webserver and then use a direct connection on the worker servers, but we switch between the two using a different ENV['DATABASE_URL'] value instead of a different AR connection config.

I think it would be helpful to mention in the docs that enqueuing jobs is safe for a transaction-based PgBouncer connection and the workaround using multiple db config is only necessary if you're using inline/async and not external.