Message-ID: <4FC4E8F4.9070903@krap.dk>
Date: Tue, 29 May 2012 17:19:16 +0200
From: Svenne Krap <svenne.lists@krap.dk>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:12.0) Gecko/20120529 Thunderbird/12.0.1
MIME-Version: 1.0
To: Ireneusz Pluta <ipluta@wp.pl>
CC: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: Job control in sql
References: <4FBF4293.4090902@krap.dk> <4FC4A5B0.2070006@wp.pl>
In-Reply-To: <4FC4A5B0.2070006@wp.pl>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

On 29-05-2012 12:32, Ireneusz Pluta wrote:
> 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.
>
Hehe.. good idea...

In the mean time I had thought about using advisory locks for the same
thing, but the old-fashioned locks work fine too.

Svenne