advisory locks with connection pooling (pgbouncer)

[Azdaroth]

It's not a bug although it might be a good place to ask, at least the answer might be an interesting section in Readme ;).

We've been using advisory locks extensively in multiple apps via with_advisory_lock gem and now, we are looking into introducing a connection pooler due to the huge number of DB connections. We are considering pgbouncer with transaction pool_mode and based on some research, some things are not really clear, so I was wondering if you have any experience with that.

So advisory locks are session-based features, which won't work with a transaction pool mode. This is also a common source of the issues with running migrations in Rails and one of the reasons why in Rails 6 it's possible to disable advisory locks to solve the issue with migrations. On the other hand, there are transaction-level locks, which are also supported by this gem, which might work just fine with the transaction pool mode. Yet, this doesn't seem to go along with the general recommendations that you shouldn't use advisory locks with pgbouncer or even with the fact that in Rails, it's either a session-based advisory lock or not at all (although this decision could be made due to the fact that it's possible to disable transaction per migration).

So I was wondering if transaction-level locks will just work with pgbouncer or should we find some alternative?

Thanks in advance for the answer.

[selivandex]

Same question here. Why no answer for a year?

[mceachen]

Why no answer for a year?

Because this is a volunteer effort?

The ClosureTree packages could benefit from more help: a couple people have stepped up, but more would be better to shoulder the load: there are still lots of users. See
ClosureTree/closure_tree#277 for more context.

[JF-Lalonde]

So I was wondering if transaction-level locks will just work with pgbouncer or should we find some alternative?

My understanding is that using the default session-level lock only works if pgbouncer is configured to session-pooling mode.

But if pgbouncer is in transaction-pooling mode, then using the transaction-level advisory lock (setting the transaction option to true) should work fine.

Here are some blog posts that go into more details:

• https://samu.space/distributed-locking-with-postgres-advisory-locks/
• https://prog.world/synchronizing-applications-using-advisory-locks-postgresql-what-is-it-why-and-the-nuances-of-working-with-pgbouncer/

[quidproquo]

I think when using the option transaction: true, the call already needs to be within a transaction block. Looking at the code, it does not seem like this will automatically start a new transaction.