4 ms·

Your wish was granted https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS https://www.postgresql.org/docs/current/explicit-locking.htm

• rand_r 2y ago
  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?
  • throwaway30713 2y ago
    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.
    • rand_r 2y ago
      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.
  • mslot 2y ago
    hashtext() works well
• murkt 2y ago
  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.
  • c0brac0bra 2y ago
    That's what exactly what I did a fairly basic distributed cron and it worked fine.
    • murkt 2y ago
      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.