Re: queueing via database table?

Steve Atkins wrote:
>
> Holding a lock while generating the thumbnail doesn't
> sound like a great idea, and I think that the select
> for update will end up serialising the requests.
>
> I'd add a "rendering" field, text, defaulting
> to an empty string.
>
> Then do a "select for update where ... and rendering = '' limit 1",
> update the rendering field to the hostname of the box doing the
> work and commit. Render the thumbnail. Delete the record.

Assuming each processing host keeps its connection open, I'd store the
process-id instead (get via function pg_backend_pid()). Also have a
separate status (pending|processing|done) and timestamps to track when
each status is set. Only delete rows that have been marked "done" for a
certain length of time.

This will let you spot when a host has stopped processing (e.g. crashed)
and also let you measure throughput on particular hosts.

The other thing to be aware of is that queries of the SELECT FOR UPDATE
LIMIT 1 form can return 0 rows. Open two psql connections and try it to
see what happens. You'll need to have the processing clients retry the
query in this case.

HTH
--
Richard Huxton
Archonet Ltd