Re: pg_try_advisory_lock is waiting?

On Fri, Jan 28, 2022 at 5:34 PM Mladen Gogala <gogala(dot)mladen(at)gmail(dot)com>
wrote:

> pg_try_advisory_lock returned TRUE even without "FOR UPDATE" clause in the
> subquery. Shouldn't it return false because it can't lock the row until the
> uncommitted update finishes?
>
> <https://dbwhisperer.wordpress.com>
>
> The rows being updated or not is unrelated to advisory locks. Your example
shows you locking on key 0 and then on key 1. That should be allowed. If
you tried key 0 twice, then that is when you would get back "false" from
the function call.

You could establish a pattern of using the table OID as the first lock key
and the primary key value (if a single column) as the second advisory lock
key with the two parameter version of the function. But it is totally up to
your code to honor that advisory lock, or not.

Again, why use advisory locks and not select for update? Perhaps just
because you don't want to deal with the failed transaction? What should
happen when some other process cannot get a lock on that row? Do you want
to wait and retry? Have you looked into the "skip locked" option? If you
use "returning id" with that, you'd be able to check if you got the lock or
not.