Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks for the flock command. Very handy. I guess it should be built into the function, though, to cover all vectors. –  Erwin Brandstetter Jul 24 '14 at 2:00
    
Used flock following this example: stackoverflow.com/questions/169964/… and worked fine! –  Ivan De Sousa Paz Jul 24 '14 at 20:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.