On Fri, Jan 28, 2022 at 5:34 PM Mladen Gogala <gogala.mladen@xxxxxxxxx> wrote:
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.
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.
