SELECT...ORDER BY...FOR UPDATE/SHARE might return results out of order

Posted by Ivan / on 01/23/2009 / 3 Comments

Bruce Momjian just updated the documentation noting that a SELECT FOR UPDATE statement might actually return the results in an arbitrary order:

Similarly, it is possible for a SELECT command using ORDER BY and FOR UPDATE/SHARE to return rows out of order. This is because ORDER BY is applied first. The command orders the result, but might then block trying to obtain a lock on one or more of the rows. Once the SELECT unblocks, one of the ordered columns might have been modified and be returned out of order. A workaround is to perform SELECT ... FOR UPDATE/SHARE and then SELECT ... ORDER BY.

I never thought about this before and I'm afraid to think of all the times I assumed ORDER BY works as expected in this case.

 

RSS Feed for this Blog    Comments Feed for this Post   

Comments

  • IVO says:

    Hmm, usefull notice. you should also take into account that LIMIT works in the same way - and you may end up with less result rows than specified by the LIMIT clause (the same reason as for ORDER BY)

    January 26, 2009 at 10:11 PM | Permalink

  • Ivan says:

    You can end up with less rows in LIMIT for a more mundane reason - the table has fewer rows :)

    January 28, 2009 at 2:08 PM | Permalink

  • IVO says:

    Yes :) I meant the case when there are enough rows (meeting the WHERE criteria)

    January 31, 2009 at 6:19 PM | Permalink

 

Join this Group Now!

Forgot Password?

Bulgarian PostgreSQL User Group
Powered by Groupsite.com

Visibility Limited Membership By Invitation or Approved Request Default Profile Professional

Your Status Not Logged-In