Date: Sat, 17 Nov 2012 17:19:17 +0100
From: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
To: pgsql-sql@postgresql.org
Subject: organizing cron jobs in one function
Message-ID: <20121117161916.GA9492@apartia.fr>
Mail-Followup-To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.21 (2010-09-15)

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,