Message-ID: <50A97DCE.8020808@2ndQuadrant.com>
Date: Mon, 19 Nov 2012 08:31:10 +0800
From: Craig Ringer <craig@2ndQuadrant.com>
Organization: 2nd Quadrant
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:16.0) Gecko/20121029 Thunderbird/16.0.2
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: organizing cron jobs in one function
References: <20121117161916.GA9492@apartia.fr>
	<50A8C63A.5070801@2ndQuadrant.com>
	<20121118171111.GA2945@apartia.fr>
In-Reply-To: <20121118171111.GA2945@apartia.fr>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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