Lock concurrent crons with postgres database

Question

What I am trying to achieve is to have multiple instances of the same application running at the same time, but only one of those instances running a cron, by locking it in a Postgres database.

My solution so far is :

• Running a cron on all the instances.
• Inserting a row in a table cron_lock with a unique identifier for the cron.
• If I have an error while running the insert query, it is most likely because the row already exists (the cron identifier is the primary key of the table). If that is the case, I do nothing, and I exit.
• If I don't have an error while running the insert query, then the application instance will run the cron process.
• At the end of my process, I delete the row with the unique identifier.

This solution is working, but I am not sure if another locking mechanism would exist with Postgres, in particular one that would not have me execute queries that are creating errors.

Answer 1

Thanks to @Belayer I found a nice way to do it with advisory locks.

Here is my solution :

Each of my crons have an associated and unique ID (integer format). All of the crons start on all the different servers. But before running the main function of the cron, I try to get an advisory lock with the unique ID in the database. If the cron can get the lock, then it will run the main function and free the lock, otherwise, it just stops.

And here is some pseudo code if you want to implement it in a language of your choice :

enum Cron {
  Echo = 1,
  Test = 2
}

function uniqueCron(id, mainFunction) {
  result = POSTGRES ('SELECT pg_try_advisory_lock($id) AS "should_run"')
  if(result == FALSE){ return }

  mainFunction()

  POSTGRES ('SELECT pg_advisory_unlock($id)')
}

cron(* * * * *) do {
  uniqueCron(Cron.Echo, (echo "Unique cron"))
}

cron(*/5 * * * *) do {
  uniqueCron(Cron.Test, (echo "Test"))
}

Running this process many times, or on many different servers, all using the same database, will result in only one mainFunction being executed at once, given that all crons are launched at the same time (same time/timezone on the different servers). A main function too short to execute might cause problems if one server try to get the lock and another already released it. In that case, wait a little before releasing the lock.