|

»

»

»

PostgreSQL - general

Feature: FOR UPDATE SKIP LOCKED

View: New views

6 Messages — Rating Filter: Alert me

	Feature: FOR UPDATE SKIP LOCKED by Jonathan Bond-Caron :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message Some parts of this message have been removed. Learn more about Nabble's security policy. I’m been reading up on FOR UPDATE NOWAIT and it looks like it was added in 8.1. How difficult is it to add FOR UPDATE SKIP LOCKED or something similar? (basically skip locked rows / oracle syntax) More background here: http://forge.mysql.com/worklog/task.php?id=3597 It would be quite useful to implement a database queue. Although FOR UPDATE NOWAIT and trying again can work as well as other techniques, just skipping over the locks has its advantages (simplicity and zero wait)
	Re: Feature: FOR UPDATE SKIP LOCKED by Tom Lane-2 :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message "Jonathan Bond-Caron" <jbondc@...> writes: > It would be quite useful to implement a database queue. Although FOR UPDATE > NOWAIT and trying again can work as well as other techniques, > just skipping over the locks has its advantages (simplicity and zero wait) And disadvantages, such as complete lack of predictability or failure detection. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@...) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
	Re: Feature: FOR UPDATE SKIP LOCKED by Csaba Nagy :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message On Wed, 2008-07-09 at 00:48 -0400, Tom Lane wrote: > "Jonathan Bond-Caron" <jbondc@...> writes: > > It would be quite useful to implement a database queue. Although FOR UPDATE > > NOWAIT and trying again can work as well as other techniques, > > > just skipping over the locks has its advantages (simplicity and zero wait) > > And disadvantages, such as complete lack of predictability or failure > detection. Well, it's not like SQL is completely predictable in general... think about ordering of results. Such a feature would definitely help queue like table processing, and the fact that it is predictably unpredictable should not be a surprise for anybody using such a feature... Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@...) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
	Re: Feature: FOR UPDATE SKIP LOCKED by Craig Ringer :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message Csaba Nagy wrote: > On Wed, 2008-07-09 at 00:48 -0400, Tom Lane wrote: >> "Jonathan Bond-Caron" <jbondc@...> writes: >>> It would be quite useful to implement a database queue. Although FOR UPDATE >>> NOWAIT and trying again can work as well as other techniques, >>> just skipping over the locks has its advantages (simplicity and zero wait) >> And disadvantages, such as complete lack of predictability or failure >> detection. > > Well, it's not like SQL is completely predictable in general... think > about ordering of results. Such a feature would definitely help queue > like table processing, and the fact that it is predictably unpredictable > should not be a surprise for anybody using such a feature... Especially if it returned an updated row count or supported the RETURNING clause, so you could find out after the fact what was or wasn't done. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@...) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
	Re: Feature: FOR UPDATE SKIP LOCKED by Csaba Nagy :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message On Wed, 2008-07-09 at 16:23 +0800, Craig Ringer wrote: > Especially if it returned an updated row count or supported the > RETURNING clause, so you could find out after the fact what was or > wasn't done. Well, it is supposed to be used as "SELECT ... FOR UPDATE SKIP LOCKED", so you can in fact put the locked row ids in the target list. With a "LIMIT 1" appended would be the perfect way to check out the next queue item to process... Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@...) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
	Re: Feature: FOR UPDATE SKIP LOCKED by Craig Ringer :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message Csaba Nagy wrote: > On Wed, 2008-07-09 at 16:23 +0800, Craig Ringer wrote: >> Especially if it returned an updated row count or supported the >> RETURNING clause, so you could find out after the fact what was or >> wasn't done. > > Well, it is supposed to be used as "SELECT ... FOR UPDATE SKIP LOCKED", > so you can in fact put the locked row ids in the target list. With a > "LIMIT 1" appended would be the perfect way to check out the next queue > item to process... That makes sense. I was thinking of UPDATE ... SKIP LOCKED RETURNING instead, which could be handy in similar situations. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@...) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general