fast application locks
This week I was looking for a mechanism to build an application specific lock in postgres. I did know about pg_try_advisory_lock(long)
and pg_try_advisory_lock(int, int)
, but could not figure out a good mechanism until I found depesz blog entry about how to pick a task of a list.
He provides some very good insight into the problem and his way to finding a solution.
What depesz does there is to use a hash function to feed into the advisory lock functions. But he uses the function hashtext(text)
, which sadly is not officially supported and saw some changes in the past.
To circumvent that problem, I wrote my own small function, which works okayish.
create function basehash(input text) returns integer
as $$
select (
'x' ||
(encode(
rpad(
right(input, 4),
4,
'0'
)::bytea,
'hex')))::bit(32)::integer;
$$ language SQL immutable;
Using this function, the functionality of hashtext is not replicated but a pretty reliable hash in the space of the integer is generated.
Using this function, one can now select the next record to work on like this
select
id
from
target_table
where
pg_try_advisory_lock(target_table::regclass, basehash(id))
limit 1;
The function will cause a number of conflicts, but if a lock exists, there is probably another record around, which is not colliding. Also, please do not use that function for security hashes. There are much better options.