pgAgent unwanted delay between jobs start time

Question

I've been trying to make jobs running continuosly, so after previous run is done, the next one starts immediately. I made some initial success with following trigger:

CREATE or replace FUNCTION continous_jobs()
RETURNS trigger AS $$
BEGIN
  NEW.jobnextrun = clock_timestamp();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER continous_jobs_trg
BEFORE UPDATE OF jobnextrun ON pgagent.pga_job 
FOR EACH ROW when (NEW.jobdesc = 'CONTINOUS_JOB' )
EXECUTE PROCEDURE continous_jobs();

I set some test job, with pg_sleep(5) inside, and it worked great

But after that I put there some real procedure inside, and it started to have delays (3-5 seconds). Why is that happening?

I tried setting different jobnextrun values, also clock_timestamp() - '5 second'::interval, but it's still the same, delay is about couple of seconds

Answer 1

There are several reasons why your pgAgent job may be experiencing a delay between runs:

1. The first possibility is that the job itself is taking longer to complete than expected. If the job is running a complex query or performing a lot of calculations, it may take longer to finish, which would result in a delay between runs.

2. Another possibility is that the trigger you created is not being executed as quickly as you expect. When the trigger is executed, it updates the value of jobnextrun, which determines when the job will be run again. If the trigger is not being executed quickly enough, this could cause a delay between runs.

3. It's also possible that there are other factors at play, such as system resource constraints or other tasks that are running concurrently and competing for resources.

To troubleshoot this issue, you may want to try the following:

1. Monitor the performance of the job itself to see if it is taking longer than expected to run. You can use tools like EXPLAIN or EXPLAIN ANALYZE to understand the performance of the query or procedure that the job is running.

2. Check the system resource utilization while the job is running to see if there are any bottlenecks that could be causing a delay. This could include CPU, memory, or disk usage.

3. Try running the job without the trigger to see if the delay is still present. This will help you determine if the trigger itself is causing the delay.

4. Consider using a different scheduling mechanism, such as cron or the built-in PostgreSQL pg_cron extension, to run the job instead of pgAgent. This will allow you to bypass the trigger entirely and see if the delay persists.