DBMS: Postgres 9.3.4 OS: Debian 7
I have a plpgsql function that will be put in crontab to run every 15 minutes. The function should finish in about 8 minutes, but just in case it takes more than 15 minutes, it CAN'T run until the last execution has finished.
I could think in more than 1 workaround, but I was wondering if there is some best known solution for this. Maybe something native on plpgsql syntax.
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.