Que doesn't work with PgBouncer

[sudhirj]

When I try to use Que with a tool like pgbouncer (that disables prepared statements) I keep getting a duplicate prepared statement error. Is there any dependency on prepared statements? Because a pgbouncer type tools is common in high scale environments, especially in resource constrained deployments like Heorku, it becomes really necessary there.

[chanks]

It would be pretty trivial to tweak the gem to allow for disabling prepared statements, I can take care of that. I don't think it's that common to use both an in-app connection pool and something like pgbouncer, though.

[sudhirj]

On something like Heroku, very common - the base Postgres instance supports 120 connections, which will usually work out to 50 Puma threads and 50 Que workers, with 20 connections left over.

With something like PgBouncer, we can get about 10x the throughput depending on the nature of the jobs - long running jobs (the ones I have pack stuff on to S3, send SMS / email) are usually network bound, which means that while the network request (~ 1 sec) is happening the connection can be used in another job. This becomes essential when there about a few thousand jobs per minute to run.

[chanks]

Actually, now that I think about it, I'm not sure that current Que master will work with pgbouncer at all, since the workers rely on having the same connection between taking a lock and releasing it. If you SELECT pg_advisory_lock(1) and then later SELECT pg_advisory_unlock(1), all outside the context of a transaction, is pgbouncer smart enough to make sure those statements are executed by the same backend? If not, you're going to have a lot of dangling locks.

The 1.0 branch has support for holding all locks through a single dedicated connection (along with LISTEN/NOTIFY support), which would be better for your use case. I wouldn't recommend using it in production quite yet, though, since I plan on changing it somewhat before the final release.

[sudhirj]

You're right... any bouncer will recycle connections, so advisory locks will not be maintained inside a job. The way you're describing it we're talking about one job - one transaction / connection type semantics. Don't think it's even possible to do this any other way.

What might be still be helpful is to remove the dependency on prepared statements for enqueuing jobs - this way installations that are working with something like PgBouncer + Puma can continue to work fine, and the rake que:work process can be run without PgBouncer.

Re. the 1.0 branch, that is an interesting way to do it... there'll need to be some kind of separate connection factory for the supervisor that does this - that way it can be supplied with a special long lived connection even if a bouncer is being used.

[chanks]

Yes, in the 1.0 branch all locks for a process are held by a single
connection, which by default is taken from the connection pool, though you
can give it a custom connection to use if you like.

I can add a configuration option to bypass prepared statements, it's not
hard. They're already bypassed when inside a transaction, by the way, to
make sure that attempting to execute a nonexistent one (which is sometimes
unavoidable when a connection is lost and then reestablished) doesn't ruin
the transaction.

And I've already removed them entirely from the 1.0 branch, since one of
its goals is to support multiple job tables, which prepared statements are
incompatible with.

On Wed, May 13, 2015 at 3:33 PM, Sudhir Jonathan notifications@github.com
wrote:

You're right... any bouncer will recycle connections, so advisory locks
will not be maintained inside a job. The way you're describing it we're
talking about one job - one transaction / connection type semantics. Don't
think it's even possible to do this any other way.

What might be still be helpful is to remove the dependency on prepared
statements for enqueuing jobs - this way installations that are working
with something like PgBouncer + Puma can continue to work fine, and the rake
que:work process can be run without PgBouncer.

Re. the 1.0 branch, that is an interesting way to do it... there'll need
to be some kind of separate connection factory for the supervisor that does
this - that way it can be supplied with a special long lived connection
even if a bouncer is being used.

—
Reply to this email directly or view it on GitHub
#110 (comment).

[sudhirj]

Awesome... will wait for 1.0. And great job, BTW. This is by far the best queue processor I've used :)

Please feel free to close the issue or hold it till 1.0 is out. It may help people who face errors using Que with a bouncer.

[chanks]

I'll leave this open until I add the option for disabling prepared statements, I'll try to get to it today. And thanks!

I'm hoping to get 1.0 out in the next couple months, I think the foundation for LISTEN/NOTIFY and batch polling is pretty solid but I'm not 100% sure how support for multiple job tables is going to be handled. That's the sticking point for now.

[chanks]

Added option to set Que.disable_prepared_statements = true. Thanks for the suggestion!

[sudhirj]

👍