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