From: Svenne Krap <svenne.lists@krap.dk>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Job control in sql
Date: Fri, 25 May 2012 10:28:03 +0200
Message-ID: <4FBF4293.4090902@krap.dk> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: svenne.lists@krap.dk
Subject: Re: Job control in sql
In-Reply-To: <4FBF4293.4090902@krap.dk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox