3 ms·

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

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.

klinch 3y ago
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 3y 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 3y 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 3y 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 3y ago
    hashtext() works well
- murkt 3y 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 3y ago
    That's what exactly what I did a fairly basic distributed cron and it worked fine.
    - murkt 3y 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.
deleted 3y ago
[deleted]

klinch 3y ago

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

deleted 3y ago

[deleted]