How do I use string as a key to PostgreSQL advisory lock?

Question

There is a locking mechanism in PostgreSQL called advisory locking. It provides the following API functions.

The function that allows us to obtain such a lock accepts an big integer argument: pg_advisory_lock(key bigint) or two integer keys: pg_advisory_lock(key1 int, key2 int) (second form).

What abstraction mechanism can I use to be able to use string keys instead of integer ones? Maybe some hashing functions will be able to do the job?

Is it possible to implement this solely in the PostgreSQL without the need to cast string to integer on the application level?

If the desired goal is hard to achieve maybe I can use two integer to identify the row in the table. The second integer could be a primary key of the row, but what integer can I use as a table identifier?

Answer 1

You've already hit upon the most likely candidate: using a synthetic primary key of a table plus a table identifier as a key.

You can use the table's oid (object identifier) from pg_class to specify the table. The convenience cast to the pseudo-type regclass looks this up for you, or you can select c.oid from pg_class c inner join pg_namespace n where n.nspname = 'public' and c.relname = 'mytable' to get it by-schema.

There's a small problem, because oid is internally an unsigned 32-bit integer, but the two-arg form of pg_advisory_lock takes a signed integer. This isn't likely to be a problem in practice as you need to go through a lot of OIDs before that's an issue.

e.g.

SELECT pg_advisory_lock('mytable'::regclass::integer, 42); 

However, if you're going to do that, you're basically emulating row locking using advisory locks. So why not just use row locking?

SELECT 1 FROM mytable WHERE id = 42 FOR UPDATE OF mytable; 

---

Now, if you really had to use a string key, you're going to have to accept that there'll be collisions because you'll be using a fairly small hash.

PostgreSQL has built-in hash functions which are used for hash joins. They are not cryptographic hashes - they're designed to be fast and they produce a fairly small result. That's what you need for this purpose.

They actually hash to int4, and you'd really prefer int8, so you run an even higher risk of collisions. The alternative is to take a slow cryptographic hash like md5 and truncate it, though, and that's just ugly.

So if you really, really feel you must, you could do something like:

select pg_advisory_lock( hashtext('fredfred') ); 

... but only if your app can cope with the fact that it is inevitable that other strings can produce the same hash, so you might see a row as "locked" that is not truly locked.