From: lewbloch@gmail.com
To: pgsql-sql@postgresql.org
Subject: Re: Job control in sql
Date: Thu, 31 May 2012 09:58:40 -0700 (PDT)
Message-ID: <d1dc629f-0132-47f4-95bc-8ea34a3beb73@googlegroups.com> (raw)
In-Reply-To: <4FC4E8F4.9070903@krap.dk>
References: <4FBF4293.4090902@krap.dk>
<4FC4A5B0.2070006@wp.pl>
<4FC4E8F4.9070903@krap.dk>
Sorry about the earlier unfinished post - premature click.
Svenne Krap wrote:
> Ireneusz Pluta wrote:
>> Svenne Krap pisze:
> >> 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?
Use a database to hold data. Use run-time constructs in the program or
script to handle run-time considerations.
How about using a shell script that uses "ps" to determine if a job is
already running, or using a lock file in the file system known to the
control script?
> >> I run it on Pgsql 9.1.
>>
>> 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.
Or don't use the DBMS that way at all.
--
Lew
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: lewbloch@gmail.com
Subject: Re: Job control in sql
In-Reply-To: <d1dc629f-0132-47f4-95bc-8ea34a3beb73@googlegroups.com>
* 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