advisory locks with connection pooling (pgbouncer) about with_advisory_lock HOT 4 OPEN

Same question here. Why no answer for a year?

from with_advisory_lock.

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/

from with_advisory_lock.

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.

from with_advisory_lock.

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.

from with_advisory_lock.