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)

• user1822: Related: crypto.stackexchange.com/q/17645 or codeproject.com/articles/34309/convert-string-to-64bit-integer
• mark: I had thought about hashing upfront but threw the idea out of the window, having angst of collisions. I can't use the bigint version because I might need locks in categories too, which brings me to int,int. Which would mean I even have only 32 bit available because the first key would reserve the category. I need to think more, maybe this is the way to go if I want to go with pgsql. Collisions wouldn't not be fatal and out of currently 1.000.000 keys I would at most have locks ~50 for a start; worst case would be 200. Thanks for making me thing about this again!