Re: Small PosgreSQL locking function request - with bounty

David Noel <david(dot)i(dot)noel(at)gmail(dot)com> wrote:

> I've read through the documentation, found a few examples using
> "for update" syntax, and it looks like you're right. Would
> transaction level SERIALIZABLE also work? It does seem messier
> though, having to handle errors thrown due to concurrent
> transactions.

The SERIALIZABLE transaction isolation level, as implemented in
PostgreSQL, would provide correct behavior; but is not a very
efficient way to handling access to the ends of a queue.
Regardless of the isolation level you use for other things, you
probably want blocking around access to the ends of the queue -- or
at least to the part where you pull an item from the queue for
processing.  The tricky part is likely to be making sure that if
something is pulled from the queue it gets processed or gets put
back onto the queue.  Without careful planning you might risk
either losing items or blocking not just for pulling off the queue,
but all the way through processing.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company