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.
-- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
