Re: queueing via database table?

On 1/3/07, Mark Harrison <mh(at)pixar(dot)com> wrote:
> Does the following sound like a reasonable approach?
> If not, what's a good way to go?
>
> The processes generating the requests will insert into
> a queue table. They may add a priority and timestamp.
>
> The several processes servicing the requests will do a
> SELECT FOR UPDATE where ... limit 1, generate thumbnail,
> delete the record and commit.

Well, this will block. So it will mean that only one thumbnail
will be processed while running the transaction.

You may want to rather use SELECT FOR UPDATE NOWAIT,
probably "wrapped" into a PL/PgSQL function. I did that and
I'm quite satisfied with this approach.

A simple implementation would be something like this:

CREATE OR REPLACE FUNCTION get_next() RETURNS int AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT id FROM foo_table LIMIT 100 LOOP
BEGIN
PERFORM id FROM foo_table WHERE id=r.id FOR UPDATE NOWAIT;
RETURN r.id;
EXCEPTION
WHEN lock_not_available THEN -- do nothing
END;
END LOOP;
RETURN NULL;
END;
$$ LANGUAGE PLpgSQL;

Of course you should customize the query, and use better tuned limit.
I think good rule of the thumb size of LIMIT is twice the number of
simultaneous processing nodes working. An ORDER BY might be
worh it or not, etc, etc.

Other approach might be using something like
LOOP
BEGIN
SELECT id INTO i FROM foo_table LIMIT 1 OFFSET n FOR UPDATE NOWAIT;
RETURN i;
EXCEPTION
WHEN lock_not_avaibale THEN -- do nothing;
END;
n := n + 1;
END LOOP;

But I feel it will be slower most of the time.