Do the exclusion in the cron
job definition. The handy flock
shell command is intended for exactly this.
To do it inside PostgreSQL you would have the function take a lock that it holds for the duration of its run. Lots of options - LOCK TABLE ... IN EXCLUSIVE MODE
, do a SELECT ... FOR UPDATE
of a particular row, or use pg_advisory_xact_lock
. See the explicit locking chapter. The main advantage of using an advisory lock is that you don't need to create some table/row just to lock it in the case where there's no "natural" candidate for a lock target.
You can use the try
forms of the advisory lock functions, or NOWAIT
locks, if you want to abort a run instead of queueing it up. If you're using psql
remember that by default it continues on error instead of aborting, so use psql -v ON_ERROR_STOP=1
in combination with NOWAIT
.