title: Implementing a mutual exclusion system / distributed queue in Postgres
body: <p>I want to implement a mutual exclusion system in PostgreSQL where multiple worker processes will temporarily lock resources (rows) from a table (queue) while they work on them. If the worker processes crash, I want the lock to be cleanly released and not have to rely on another process to clean up the leaked locks.</p>
  
  <p>What I have come up with so far is to use a <code>SELECT ... FOR UPDATE SKIP LOCKED</code> query within a transaction, which locks the row it finds and skips any other locked row.</p>
  
  <p>It works well but one of the issues is that the worker might take a while to do its task and I need to keep the transaction open for the entire duration of its task. </p>
  
  <p>Another problem is that the workers work incrementally and persist their state to the database so that if they're stopped or crash, they can resume quickly where they were. The row being locked makes it impossible to persist their state in the same table (though I think I can get away from that by using another table to persist the state).</p>
  
  <p>I've searched on the Web on how to implement a semaphore or a resource borrowing system in SQL/PostgreSQL but I haven't found something that fits my needs. Is there a simple way of achieving this with PostgreSQL?</p>