title: Implementing expiring locks (or semaphores) using postgres
body: <p>I have the following table</p>
  <pre><code>Tasks
     max_solvers int, # Maximum number of users that can solve this task (e.g. survey)
     current_solvers int[], # List of user ids that solved this task (e.g. already took the survey)
     state char[1], # If the task is solved or not, this variable can be used to show tasks to users to solve.
  </code></pre>
  <p>Question:
  Example: Let's say a task can be solved by only 3 users (e.g. max_solvers = 3)</p>
  <p>So any query that does the following will not work:</p>
  <pre><code>  select * from tasks where task.state = 'NOT_SOLVED'
  </code></pre>
  <p>Because we might show that task to (e.g. 100) users and only 3 of them can solve and other 97 users work will be wasted.</p>
  <p>One (of the possible) ways to solve this would be using semaphores. A task can be shown only to max_solvers - array_length(current_solvers).</p>
  <p>Semaphore will work for the most part. If a user is shown a task but ends up not solving (e.g. their phone died). It would be nice to have a expiring sempahores.</p>
  <p>Actual question :)</p>
  <p>Is it possible to implement expiring semaphores (or expiring ) locks using postgres. I know postgres has Advisory locks but they don't automatically expire.</p>
  <p>One way to simulate expiring locks (or serializing access) is previously discussed <a href="https://dba.stackexchange.com/a/97261">here</a></p>
  <p>Is it possible to extend that answer to implement expiring semaphores.</p>
  <p>Or is it better to rely on other system that can solve this more effectively (e.g. <a href="https://redislabs.com/ebook/part-2-core-concepts/chapter-6-application-components-in-redis/6-3-counting-semaphores/6-3-1-building-a-basic-counting-semaphore/" rel="nofollow noreferrer">redis refreshing semaphores</a>)</p>
  <p>Thanks!</p>