Postgres advisory lock when key doesn't fit

Question

I've identified that the Postgres advisory lock would be a good match for my use-case (being bound to the current session/transaction) and thus I don't want to use e.g. Redis.

The advisory lock functions are documented at https://www.postgresql.org/docs/9.6/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS having two variants: (bigint) or (int, int).

I've a resource which is 512 bit (a VARHCHAR(64) column, actually). The column is part of a table where I cannot just directly lock the row. The resource being part of that row is just one aspect.

One of the goals here is that I can detect the lock from without other parts of the application: i.e. the application must not hang/wait for the lock but give me back whether the resource is currently locked or not.

Is there a way to express an advisory lock on a resource requiring more storage? This is a problem because I don't have any unique key that I could use for this purpose.

I'm using Postgres 9.6.3.

Answer 1

The usual way of solving such issues is to use a hash function that would produce a (nearly-)unique value that fits your purposes - in this case a pair of integers or a single bigint.

Unfortunately, to my knowledge there is no such built-in hash function in PostgreSQL. You can adopt different solutions though:

• use the built-in md5() and truncate it to bigint
• find a 3rd party solution, like hash64 (warning: I haven't tried this yet, so no idea if it is a good hash function or not)