Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TaOId-0004L8-Uw for pgsql-sql@postgresql.org; Mon, 19 Nov 2012 10:09:56 +0000 Received: from marius.cruisefish.net ([178.63.80.84]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TaOIb-000611-Ad for pgsql-sql@postgresql.org; Mon, 19 Nov 2012 10:09:55 +0000 Received: by marius.cruisefish.net (Postfix, from userid 1000) id 6C7ED80DD78F1; Mon, 19 Nov 2012 11:09:52 +0100 (CET) Date: Mon, 19 Nov 2012 11:09:46 +0100 From: Louis-David Mitterrand To: pgsql-sql@postgresql.org Subject: Re: organizing cron jobs in one function Message-ID: <20121119100945.GA23295@apartia.fr> Mail-Followup-To: pgsql-sql@postgresql.org References: <20121117161916.GA9492@apartia.fr> <50A8C63A.5070801@2ndQuadrant.com> <20121118171111.GA2945@apartia.fr> <50A97DCE.8020808@2ndQuadrant.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <50A97DCE.8020808@2ndQuadrant.com> User-Agent: Mutt/1.5.21 (2010-09-15) X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201211/18 X-Sequence-Number: 36951 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?)