Re: Job control in sql
От | Ireneusz Pluta |
---|---|
Тема | Re: Job control in sql |
Дата | |
Msg-id | 4FC4A5B0.2070006@wp.pl обсуждение исходный текст |
Ответ на | Job control in sql (Svenne Krap <svenne.lists@krap.dk>) |
Ответы |
Re: Job control in sql
|
Список | pgsql-sql |
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.
В списке pgsql-sql по дате отправления: