[SOLVED] PostgreSQL: get advisory lock unless more than n transactions are waiting for it

PostgreSQL: get advisory lock unless more than n transactions are waiting for it

I've a resource that gets acquired from distributed clients. This resource is protected by a transaction level lock:

SELECT pg_advisory_xact_lock(123456)

I know I can use pg_try_advisory_xact_lock if I don't want to wait for it to be free. If I got it - the call would return true, otherwise false.

What I would like to achieve is the behaviour of pg_advisory_xact_lock with queue-size. If less than 4 transactions are waiting from the lock join them in waiting, otherwise return false pg_try_advisory_xact_lock otherwise. If you managed to get the lock - return true.

Can I achieve this without writing a plv8 extension? Any ideas?

I'm using PostgreSQL 10.

Solution

Cycle through "pg_try_advisory_xact_lock(...)" on 5 different lock ids (say 123457 to 123461) (1 real lock holder and 4 more non-holding waiters = 5). Getting one of those then grants you the right to wait on the "real" lock id. Failing to get any of the 5 results in failure. This does mean you have to reserve/coordinate 6 magic constants, rather than 1.