Thread: organizing cron jobs in one function
Hi, I'm planning to centralize all db maintenance jobs from a single pl/pgsql function called by cron every 15 minutes (highest frequency required by a list of jobs). In pseudo code: CREATE or replace FUNCTION cron_jobs() RETURNS void LANGUAGE plpgsql AS $$ DECLARErec record; BEGIN /* update tbl1 every 15 minutes*/select name, modified from job_last_update where name='tbl1' into rec;if not found orrec.modified + interval '15 minutes' < now() then perform tbl1_job(); update job_last_update set modified=now() wherename='tbl1';end if; /* update tbl2 every 2 hours */select name, modified from job_last_update where name='tbl2' into rec;if not found or rec.modified+ interval '2 hours' < now() then perform tbl2_job(); update job_last_update set modified=now() where name='tbl2';endif; /* etc, etc.*/ END; $$; The 'job_last_update' table holds the last time a job was completed. Is this a good way to do it? Thanks,
On 11/18/2012 12:19 AM, Louis-David Mitterrand wrote: > Hi, > > I'm planning to centralize all db maintenance jobs from a single > pl/pgsql function called by cron every 15 minutes (highest frequency > required by a list of jobs). It sounds like you're effectively duplicating PgAgent. Why not use PgAgent instead? -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Sun, Nov 18, 2012 at 07:27:54PM +0800, Craig Ringer wrote: > On 11/18/2012 12:19 AM, Louis-David Mitterrand wrote: > > Hi, > > > > I'm planning to centralize all db maintenance jobs from a single > > pl/pgsql function called by cron every 15 minutes (highest frequency > > required by a list of jobs). > It sounds like you're effectively duplicating PgAgent. > > Why not use PgAgent instead? Sure, I didn't know about PgAgent. Is it still a good solution if I'm not running PgAdmin and have no plan doing so?
On 11/19/2012 01:11 AM, Louis-David Mitterrand wrote: > On Sun, Nov 18, 2012 at 07:27:54PM +0800, Craig Ringer wrote: >> On 11/18/2012 12:19 AM, Louis-David Mitterrand wrote: >>> Hi, >>> >>> I'm planning to centralize all db maintenance jobs from a single >>> pl/pgsql function called by cron every 15 minutes (highest frequency >>> required by a list of jobs). >> It sounds like you're effectively duplicating PgAgent. >> >> Why not use PgAgent instead? > Sure, I didn't know about PgAgent. > > Is it still a good solution if I'm not running PgAdmin and have no plan > doing so? > It looks like it'll work. The main issue is that if your jobs run over-time, you don't really have any way to cope with that. Consider using SELECT ... FOR UPDATE, or just doing an UPDATE ... RETURNING instead of the SELECT. I'd also use one procedure per job in separate transactions. That way if your 4-hourly job runs overtime, it doesn't block your 5-minutely one. Then again, I'd also just use PgAgent. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Mon, Nov 19, 2012 at 08:31:10AM +0800, Craig Ringer wrote: > On 11/19/2012 01:11 AM, Louis-David Mitterrand wrote: > > On Sun, Nov 18, 2012 at 07:27:54PM +0800, Craig Ringer wrote: > >> On 11/18/2012 12:19 AM, Louis-David Mitterrand wrote: > >>> Hi, > >>> > >>> I'm planning to centralize all db maintenance jobs from a single > >>> pl/pgsql function called by cron every 15 minutes (highest frequency > >>> required by a list of jobs). > >> It sounds like you're effectively duplicating PgAgent. > >> > >> Why not use PgAgent instead? > > Sure, I didn't know about PgAgent. > > > > Is it still a good solution if I'm not running PgAdmin and have no plan > > doing so? > > > It looks like it'll work. The main issue is that if your jobs run > over-time, you don't really have any way to cope with that. Consider > using SELECT ... FOR UPDATE, or just doing an UPDATE ... RETURNING > instead of the SELECT. > > I'd also use one procedure per job in separate transactions. That way if > your 4-hourly job runs overtime, it doesn't block your 5-minutely one. > > Then again, I'd also just use PgAgent. In my last question I was asking about the ability to run PgAgent _without_ PgAdmin. Is that possible? From the docs it seems PgAgent requires a GUI to configure jobs, and I'd rather avoid that if possbile. Otherwise thanks for the advice, especially the 'overtime' issue (which when a new job starts when the old one is not over yet, right?)
On 2012-11-17, Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> wrote: > Hi, > > I'm planning to centralize all db maintenance jobs from a single > pl/pgsql function called by cron every 15 minutes (highest frequency > required by a list of jobs). In pseudo code: centralising execution of cron jobs into a single function is a recipe for locking problems, if your jobs are not of the type that acquire exclusive locks on tables, it might work for you. . -- ⚂⚃ 100% natural