Let's assume you have a task
FOO that can be queued once every minute, and a pool of 50 workers that can be paused. The queue is paused for 10 minutes, and 10
FOO tasks are queued. When the queue is resumed, the 10
FOO tasks will be executed almost concurrently (because there are more workers than tasks).
In this case, I need to ensure that no more than 1
FOO task per minute (time can vary) is performed.
One solution, using Redis, is to take advantage of Redis atomic and the
TTL of a key. When a
FOO task starts, it checks if the key
worker:FOO exists. If does, then it exists, if it does not it sets the value and a
TTL to the maximum frequency. This is easy to achieve using
SETNX worker:FOO whatever and then using
TTL worker:FOO if the previous command returned 1.
SETNX is atomic, I won't fall into the case where two
FOO tasks are executed because of the race condition between the GET and the SET.
Now the question is: what is the correct way to achieve the same result using PostgreSQL? I can have a table with a
key and a
executed_on timestamp value, but how can I ensure that there is no case where two
FOO tasks are both executed because of the delay between
FOO 1 checks the record and writes a lock?