How to ensure that the same connection is always used from a given thread

[HoneyryderChuck]

For a given "stats collection" functionality I have, where a thread is spawned from a process which runs in all servers where an application is deployed, I'm building a leader election process, to ensure that only one of them does work. For that, I'm using the pg advisory lock functionality. The process is very simple: on each 30s thread loop, I try acquiring the lock, and if successful, I collect the stats and send them to statsd, otherwise I sleep again.

For this to work the way I intended, I have to ensure that the worker thread always uses the same connection. However, by default, sequel distributes connections to all threads doing database work and using the same database object. Is there a way to "pin" connections to a given thread? Or do I have to acquire a connection for that thread for the duration of the process?

[janko]

Or do I have to acquire a connection for that thread for the duration of the process?

That sounds like the best way to me, calling DB.synchronize around the work done by that thread. If I remember correctly how connection pooling is implemented, this will pin the connection to that thread for the duration of the block.

[jeremyevans]

There isn't a supported non-block approach to checking out a connection from the pool, by design. You could use Database#new_connection to return a new connection object not added to the pool, but then you need to use that connection object directly, it would not be accessed through the Database object.

Maybe what you want is a separate Database object with the single_threaded option, and use that for your thread?

[HoneyryderChuck]

By using the new_connection approach, I guess I'd still have to find a way to attach it to a new database object, right? So I guess your suggestion of having a separate database object makes sense.

Instead of creating it myself, is it possible for me to clone from the existing object, and enabling single threaded mode? Smth like:

Thread.new do
  dedicated = DB.clone
  dedicated.single_threaded = true
  # and only use this one from here on out

[jeremyevans]

I don't think clone will get what you want, and single threaded mode needs to be set when the Database instance is created so it uses the correct pool class. Maybe: dedicated = Sequel.connect(DB.opts.merge(single_threaded: true))

[HoneyryderChuck]

that works as well for me 👍 thanks a lot!

[HoneyryderChuck]

I've changed the approach to using max_connections: 1 instead of switching to single threaded mode(see here). The reason being, I'm using it in production with the :connection_validator extension enabled, which is incompatible with the single threaded mode.