Re: pg_try_advisory_lock is waiting?

On Fri, Jan 28, 2022 at 6:34 PM Mladen Gogala wrote: > > On 1/28/22 19:08, Tom Lane wrote: > > I doubt it. I think the FOR UPDATE in the sub-select is blocked > because the other session has an uncommitted update on the row > it wants to lock. This command won't reach the pg_try_advisory_lock >

Re: pg_try_advisory_lock is waiting?

On 1/28/22 20:54, Michael Lewis wrote: On Fri, Jan 28, 2022 at 5:34 PM Mladen Gogala 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? The rows

Re: pg_try_advisory_lock is waiting?

On Fri, Jan 28, 2022 at 5:34 PM Mladen Gogala 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? > > > > The rows

Re: pg_try_advisory_lock is waiting?

On 1/28/22 19:08, Tom Lane wrote: I doubt it. I think the FOR UPDATE in the sub-select is blocked because the other session has an uncommitted update on the row it wants to lock. This command won't reach the pg_try_advisory_lock call until that row lock comes free. Yes, I figured it out, but

Re: pg_try_advisory_lock is waiting?

Mladen Gogala writes: > I tried the following: > _*1st Session:*_ > mgogala=# begin transaction; > BEGIN > mgogala=*# update emp set sal=sal*1 where empno=7934; > UPDATE 1 > mgogala=*# > _*2nd Session:*_ > mgogala=# begin transaction; > BEGIN > mgogala=*# select pg_try_advisory_lock(0) from

Re: pg_try_advisory_lock is waiting?

pg_try_advisory_lock and other advisory lock functions take a lock on a key. The keys you have tried to lock are 0 and 1. Why? What problem are you trying to solve by changing from "for update" lock to advisory lock anyway?

pg_try_advisory_lock is waiting?

I am trying to replace SELECT FROM WHERE FOR UPDATE with pg_try_advisory_lock. The documentation says the following: https://www.postgresql.org/docs/13/functions-admin.html |pg_try_advisory_lock| ( /|key|/ |bigint| ) → |boolean| |pg_try_advisory_lock| ( /|key1|/ |integer|, /|key2|/