How can I run a pgAgent job every 10 second?

Question

I have a PostgreSQL 11.3 Database. I installed pgAgent for creating jobs. Now I want to run my job every 10 second, but in GUI there is possibility to write with precision of minutes, not with precision of second.

Is it possible to run job created by pgAgent faster than every minute?

Answer 1

As it is using a cron-style schedule definition it is unlikely to support sub-minute granularity as most (all?) cron implementations don't.

A hack I've used to run a task more often than every minute with cron is a script that cron calls once per minute, which performs its task, sleeps a bit, then repeats. For example to run for times per minute: dotask; sleep 15; dotask; sleep 15; dotask; sleep 15; dotask;. You could do something similar with pg_sleep. There are a few caveats to watch out for including:

1. Your task may run a few more times after you stop it via your schedule configuration.
2. If the task takes an appreciable amount of time you need to adjust for this in your sleep durations. If the time it takes is variable the you need to be brighter than a simple static sleep call: for the 4/minute example your first sleep would be for "15-minus-current-seconds-on-wall-clock seconds", the second for "30-minus-current-seconds-on-wall-clock seconds".
3. If the task ever takes longer than your target timing granularity to complete, you have the potential problem of two instances trying to run at the same time which might cause locking issues and/or bog your database down.

Another option is to define multiple tasks all set to go off on the minute. The first runs immediately, the second sleeps for seconds before running the actual work, the second sleeps for *2, etc. So for running the task four times per minute via cron you'd have:

* *    * * *    dotask.sh
* *    * * *    sleep 15 && dotask.sh
* *    * * *    sleep 30 && dotask.sh
* *    * * *    sleep 45 && dotask.sh

^{_{(where 5 *s means "run every minute" in cron's config files)}}
This removes the exactly-how-long-to-sleep-for problem (the second issue above), but potentially makes the multiple-instaces-running-at-once matter (issue three above) more of a concern. Both options are somewhat hacky...