Re: Processing a work queue

On 4/27/07, Merlin Moncure <mmoncure@gmail.com> wrote:
> how about this:
> create table job(job_id int, [...])
> create sequence worker;
>

couple typos: here is an example that works:
create table job(job_id serial);
create sequence worker;

-- get next available job
create function next_job() returns job as
$$
select job from job join
(
 select nextval('worker') as requested_job
) q on job_id = requested_job
and
(
 (select (w.last_value, w.is_called) < (j.last_value, j.is_called)
from worker w, job_job_id_seq j)
);
$$ language sql;

select next_job();

again, remembering that sequences are not rolled back on transaction
failure, you have to think really carefully about failure conditions
before going with something like this.

merlin