Skip site navigation (1) Skip section navigation (2)

Re: Job control in sql

From: lewbloch(at)gmail(dot)com
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Job control in sql
Date: 2012-05-31 16:56:29
Message-ID: 3e7721c2-214e-4a12-ac86-36d64b4cac41@googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-sql
Svenne Krap wrote:
> On 29-05-2012 12:32, Ireneusz Pluta wrote:
> > W dniu 2012-05-25 10:28, Svenne Krap pisze:
> >> Hi.
> >>
> >> I am building a system, where we have jobs that run at different
> >> times (and takes widely different lengths of time).
> >>
> >> Basically I have a jobs table:
> >>
> >> create table jobs(
> >>      id serial,
> >>     ready boolean,
> >>     job_begun timestamptz,
> >>     job_done timestamptz,
> >>     primary key (id)
> >> );
> >>
> >> This should run by cron, at it is my intention that the cronjob
> >> (basically) consists of
> >> /
> >> psql -c "select run_jobs()"/
> >>
> >> My problem is, that the job should ensure that it is not running
> >> already, which would be to set job_begun when the job starts". That
> >> can easily happen as jobs should be started every 15 minutes (to
> >> lower latency from ready to done) but some jobs can run for hours..
> >>
> >> The problem is that a later run of run_jobs() will not see the
> >> job_begun has been set by a prior run (that is unfinished - as all
> >> queries from the plpgsql-function runs in a single, huge transaction).
> >>
> >> My intitial idea was to set the isolation level to "read uncommitted"
> >> while doing the is-somebody-else-running-lookup, but I cannot change
> >> that in the plpgsql function (it complains that the session has to be
> >> empty - even when I have run nothing before it).
> >>
> >> Any ideas on how to solve the issue?
> >>
> >> I run it on Pgsql 9.1.
> >>
> >> Svenne
> >
> > I think you might try in your run_jobs()
> > SELECT job_begun FROM jobs WHERE id = job_to_run FOR UPDATE NOWAIT;
> > This in case of conflict would throw the exception:
> > 55P03 could not obtain lock on row in relation "jobs"
> > and you handle it (or not, which might be OK too) in EXCEPTION block.
> >
> Hehe.. good idea...
> 
> In the mean time I had thought about using advisory locks for the same
> thing, but the old-fashioned locks work fine too.


In response to

pgsql-sql by date

Next:From: lewblochDate: 2012-05-31 16:58:40
Subject: Re: Job control in sql
Previous:From: lewblochDate: 2012-05-31 16:55:27
Subject: Re: Select row cells as new columns

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group