organizing cron jobs in one function

From: Louis-David Mitterrand <vindex+lists-pgsql-sql(at)apartia(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: organizing cron jobs in one function
Date: 2012-11-17 16:19:17
Message-ID: 20121117161916.GA9492@apartia.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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 $$
DECLARE
rec record;
BEGIN
/* update tbl1 every 15 minutes*/
select name, modified from job_last_update where name='tbl1' into rec;
if not found or rec.modified + interval '15 minutes' < now() then
perform tbl1_job();
update job_last_update set modified=now() where name='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';
end if;

/* 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,

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jasen Betts 2012-11-18 00:50:34 Re: organizing cron jobs in one function
Previous Message Igor Neyman 2012-11-13 15:19:19 Re: How to compare two tables in PostgreSQL