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
