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.