From: Ireneusz Pluta <ipluta@wp.pl>
To: Svenne Krap <svenne.lists@krap.dk>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Job control in sql
Date: Tue, 29 May 2012 12:32:16 +0200
Message-ID: <4FC4A5B0.2070006@wp.pl> (raw)
In-Reply-To: <4FBF4293.4090902@krap.dk>
References: <4FBF4293.4090902@krap.dk>
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.
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: ipluta@wp.pl, svenne.lists@krap.dk
Subject: Re: Job control in sql
In-Reply-To: <4FC4A5B0.2070006@wp.pl>
* 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