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.


1 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:

  • 1
  • 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!
    – mark
    Feb 20, 2018 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.