Re: RFC: pgAgent Scheduler Design

-----Original Message-----
From: Andreas Pflug [mailto:pgadmin(at)pse-consulting(dot)de]
Sent: Sun 3/6/2005 1:41 PM
To: Dave Page
Cc: pgadmin-hackers(at)postgresql(dot)org
Subject: Re: [pgadmin-hackers] RFC: pgAgent Scheduler Design

> >Yes, I'm aware of that issue - and even the current design will have
> >problems in that jobs may stil run late.
>
> Better late than never.

Yup - but, an exception... say I have a daily job that sends me a simple report via email. If the system is down for a few days, then I don't want it to run all the old instances of the job on restart (cron won't, the MS Task Scheduler won't - in fact, I can't think of any I've used that do would). Anyway, I was thinking that when the agent first starts, it should do something like 'update pga_schedule set nextrun = nextrun where jscactive = true and jscrunning = false' to nudge the update trigger to recalculate the next run dates from that point. What I'm not so sure about is how to log the failed jobs in that instance. This should be multi-agent safe.

> Threading is nice, but not a guarantee to be exactly on-time for job starts.

Nope, but except on the most overloaded of systems each job should start within a minute of it's schedule. With the current design, one 6 hour job could completely screw things up for other jobs.

> -a job that is due to run will be running as soon as possible

Yup.

> - any instance of pgAgent might be configured to run a job threaded
> - multiple instance may share the pool of due tasks

Eh? No, I'm advocating 1 thread per job. The main thread queries the db, find 5 jobs due and spawns 5 threads to run them. 1 minute later, regardless of the state of the 5 threads, the main thread checks for new tasks to run and spawns more threads if required. As jobs are finished, their threads simply die.

The use of multiple agents by the vast majority of people seems unlikely to me - especially given the lack of control over what runs on what agent. In particular, the majority of jobs are likely to be SQL jobs, the distribution of which is pretty much irrelevant anyway as all the hard work is done by the server. I'm not convinced that many people will want to run resource hungry batch jobs that may run on random agent machines.

Can we get some third party opinions on what usage models will be useful please?

Regards, Dave