Обсуждение: 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