Forum OpenACS Q&A: Re: PostgreSQL MVCC transaction model

Posted by Andrew Piskorski on
Mark, I would always do a "select for update" in code like that.

Your code also seems a little wonky, in that you apparently have a queue of rows, but when an item is complete rather than doing the normal thing and updating a "done_p" flag column, you're deleting it from the table. Hard to say withou knowing more about your application, but that's also probably a problem. Unless I had a very good reason to do otherwise, I would probably update the row, not delete it; then if necessary run a sweeper job at some later point to archive or delete out old obsolete completed rows.

Posted by russ m on
Adding a status column also lets you set the task to 'in progress' straight after it's been selected, reducing the window during which another thread could try and run the task only to be rolled back when the final delete/update fails.
Posted by Mark Aufflick on
That code (actually, a perl version of it) is being used for a telco internal provisioning system. There is never any user feedback, and it could potentially take hundreds of tasks a minute - maybe more. Still, I like the idea of keeping some history - thanks for the input!