pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedorganizing cron jobs in one function
6+ messages / 3 participants
[nested] [flat]
* organizing cron jobs in one function
@ 2012-11-17 16:19 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2012-11-18 00:50 ` Re: organizing cron jobs in one function Jasen Betts <jasen@xnet.co.nz>
2012-11-18 11:27 ` Re: organizing cron jobs in one function Craig Ringer <craig@2ndQuadrant.com>
0 siblings, 2 replies; 6+ messages in thread
From: Louis-David Mitterrand @ 2012-11-17 16:19 UTC (permalink / raw)
To: 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,
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: organizing cron jobs in one function
2012-11-17 16:19 organizing cron jobs in one function Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2012-11-18 00:50 ` Jasen Betts <jasen@xnet.co.nz>
1 sibling, 0 replies; 6+ messages in thread
From: Jasen Betts @ 2012-11-18 00:50 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: organizing cron jobs in one function
2012-11-17 16:19 organizing cron jobs in one function Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2012-11-18 11:27 ` Craig Ringer <craig@2ndQuadrant.com>
2012-11-18 17:11 ` Re: organizing cron jobs in one function Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
1 sibling, 1 reply; 6+ messages in thread
From: Craig Ringer @ 2012-11-18 11:27 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: organizing cron jobs in one function
2012-11-17 16:19 organizing cron jobs in one function Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2012-11-18 11:27 ` Re: organizing cron jobs in one function Craig Ringer <craig@2ndQuadrant.com>
@ 2012-11-18 17:11 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2012-11-19 00:31 ` Re: organizing cron jobs in one function Craig Ringer <craig@2ndQuadrant.com>
0 siblings, 1 reply; 6+ messages in thread
From: Louis-David Mitterrand @ 2012-11-18 17:11 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: organizing cron jobs in one function
2012-11-17 16:19 organizing cron jobs in one function Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2012-11-18 11:27 ` Re: organizing cron jobs in one function Craig Ringer <craig@2ndQuadrant.com>
2012-11-18 17:11 ` Re: organizing cron jobs in one function Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2012-11-19 00:31 ` Craig Ringer <craig@2ndQuadrant.com>
2012-11-19 10:09 ` Re: organizing cron jobs in one function Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 1 reply; 6+ messages in thread
From: Craig Ringer @ 2012-11-19 00:31 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: organizing cron jobs in one function
2012-11-17 16:19 organizing cron jobs in one function Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2012-11-18 11:27 ` Re: organizing cron jobs in one function Craig Ringer <craig@2ndQuadrant.com>
2012-11-18 17:11 ` Re: organizing cron jobs in one function Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2012-11-19 00:31 ` Re: organizing cron jobs in one function Craig Ringer <craig@2ndQuadrant.com>
@ 2012-11-19 10:09 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 0 replies; 6+ messages in thread
From: Louis-David Mitterrand @ 2012-11-19 10:09 UTC (permalink / raw)
To: pgsql-sql
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?)
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-11-19 10:09 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-11-17 16:19 organizing cron jobs in one function Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2012-11-18 00:50 ` Jasen Betts <jasen@xnet.co.nz>
2012-11-18 11:27 ` Craig Ringer <craig@2ndQuadrant.com>
2012-11-18 17:11 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2012-11-19 00:31 ` Craig Ringer <craig@2ndQuadrant.com>
2012-11-19 10:09 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox