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
