Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 11E03B53E84
	for <pgsql-sql@postgresql.org>; Fri, 25 May 2012 05:28:21 -0300 (ADT)
Received: from mail.kracon.dk ([81.7.185.66])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <svenne.lists@krap.dk>) id 1SXpsi-0005J1-4U
	for pgsql-sql@postgresql.org; Fri, 25 May 2012 08:28:21 +0000
Received: (qmail 13792 invoked from network); 25 May 2012 08:28:02 -0000
Received: from unknown (HELO ?192.168.0.145?) (sk@62.66.238.82)
	by mail.kracon.dk with ESMTPA; 25 May 2012 08:28:02 -0000
Message-ID: <4FBF4293.4090902@krap.dk>
Date: Fri, 25 May 2012 10:28:03 +0200
From: Svenne Krap <svenne.lists@krap.dk>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:12.0) Gecko/20120524 Thunderbird/12.0.1
MIME-Version: 1.0
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Job control in sql
Content-Type: multipart/alternative;
	boundary="------------070700010409080109070001"
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201205/86
X-Sequence-Number: 36634

This is a multi-part message in MIME format.
--------------070700010409080109070001
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

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

--------------070700010409080109070001
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    Hi. <br>
    <br>
    I am building a system, where we have jobs that run at different
    times (and takes widely different lengths of time). <br>
    <br>
    Basically I have a jobs table: <br>
    <br>
    create table jobs(<br>
         id serial, <br>
        ready boolean, <br>
        job_begun timestamptz, <br>
        job_done timestamptz, <br>
        primary key (id)<br>
    );<br>
    <br>
    This should run by cron, at it is my intention that the cronjob
    (basically) consists of<br>
    <i><br>
      psql -c "select run_jobs()"</i><br>
    <br>
    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..
    <br>
    <br>
    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). <br>
    <br>
    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). <br>
    <br>
    Any ideas on how to solve the issue?<br>
    <br>
    I run it on Pgsql 9.1.<br>
    <br>
    Svenne<br>
  </body>
</html>

--------------070700010409080109070001--