Hacker News Reader

Comment by redditor98654 | Hacker News Reader

I wish it had logical locks where you didn’t need to lock a particular row. Instead you’d just specify a string and lock on it and then execute your application code. Would be useful for distributed locks, or for transactions that may conflict with another so you take the logical lock in the application code before proceeding.

View on HN

Your wish was granted

https://www.postgresql.org/docs/current/explicit-locking.htm...

That is the way, but the UX is pretty ass because the lock ID is a 64 bit number instead of a string. How the heck are you supposed to keep track of what lock ID you should be checking in a given situation across multiple client apps?

You get the same problem for strings. How do you know that you should lock "user_update" and not "update_user" for example? And how do you avoid name collision when client A wants to check for a lock that is used by client B for other purposes?

The solution to both cases is to define them as either static constants or use an Enum. Then you would not care if the end result is a string or a number.

At my work place we simply have a static class with lock names that we use.

Good point, that’s a better approach. I guess for a multi-repo situation at work, you would need to create a base project like “postgres-lock-ids” so you can synchronize the lock across everything.

hashtext() works well

It's not exactly string-based as it accepts bigint key, but I guess it's possible to hash a string when you pass it to the function.

That's what exactly what I did a fairly basic distributed cron and it worked fine.

Have you passed a string into Postgres and then hashed it into bigint with another function? If yes, what function did you use?

I assume that if you do it this way, then you see a string key in logs, views of current/locked queries, etc. Which should immensely help when debugging any kind of problems.

Reply on news.ycombinator.com