Clojurians Log v2

hi, morning i am trying to do lookup by primary key (uuid) with clojure.jdbc (database - postgresql) but it fails cause uuid is being treated as string

(jdbc/get-by-id db <table> #uuid "....")

(java.util.UUID/fromString uuid-string)

oh yeah guilty hahaha. I suck at naming things. Autoconn has a property of looking up and decrypting the required DB credentials, I didn't even think about the autocommit attribute

It's just a credentials lookup "automagically". Was orthogonal to the autocommit... terrible naming choice, in retrospect

So I'm in a dumb, non-ideal position where I need to hack together the concept of 2 phase commits without actually being able to enable prepare transaction, commit prepared , and rollback prepared. Basically it would go something like this:

(jdbc/with-transaction [conn (get-conn!)]
  (let [data     (get-postgres-data! conn)
        update   (whatever! data)]
    (sql/update! conn update [<where>...])))

It is already uuid

Oops, sorry, misunderstood.

Possibly misunderstanding, but would SELECT FOR UPDATE be a possibility? You can accidentally wind up locking more than you mean, but perhaps is what you are looking for?

(do 
  (future
    (time 
     (do 
       (with-open [conn (get-connection!)]
         (jdbc/with-transaction [conn conn]
           (jdbc/execute! conn ["lock table table_name in share mode"])
           (Thread/sleep (* 10 1000))))
       (println "lock released!"))))
  (time 
   (do
     (with-open [conn (get-connection!)]
       (jdbc/with-transaction [conn conn]
         (sql/insert! conn :table_name {:identifier "ha123"})))
     (println "inserted!"))))

Although in this case I'm making a relatively low throughput queue and data integrity is more important than throughput

Yeah locking entire tables in my experience can make all kinds of things sad very fast, but as always “it depends.”

any suggestions on better locking practices? Here's the scenario: I want multiple lockers workers to be able to pull jobs from a queue-table. Each row represents a task. The only thing they are required to do is pickup a row where status is NULL , update status = 'pending', do some work, and then set status = 'done' or delete the row. I don't want two workers to pickup the same row/task.

I really only need one worker but I need to guard against multiple folks running the app simultaneously, a job running twice is not acceptable in this scenario

I do think SELECT FOR UPDATE is what you want, provided you can make the status = 'pending' update and commit immediately… but there are some much deeper SQL gurus in here. If you have a larger number of workers and having them pick jobs serially is bad, I have seen a technique against MySQL where you get a list of “potentially eligible” rows using the WHERE status is NULL criterion in a non-locking way, then “pick” one somehow (random, time-sorted, whatever) and SELECT FOR UPDATE on the primary key to lock only the job you are about to adopt, with retries in the case of lock expiration to just try to grab another. I’m sure this too has contention issues with some number of thundering workers if you are trying to order the jobs, but hey. I also don’t know enough about the locking semantics of Postgres to give high-performance advice, but it is a fun subject 🙂