"Lee McKeeman" <lmckee...@opushealthcare.com> writes: > Description: order by is not honored after select ... for update
The reason for this behavior is that SELECT FOR UPDATE substitutes the latest version of the row at the time the row lock is acquired, which is the very last step after the selection and ordering have been done. In your example, what the ORDER BY sees is 20/25/30/300, and it sorts on that basis, and then the 20/1 row is discovered not to be live anymore so the 40/1 row is locked and substituted. The only way to avoid this would be to lock before the sort, which could have the effect of locking more rows than are returned (if you also use LIMIT); or to repeat the sort operation after locking the rows, which I doubt anyone is going to want it to do. I suggest sorting on the client side if you really need this to work in this particular way. [ thinks for awhile... ] Actually you could make it work entirely on the server if you were willing to interpose a SQL function, along the lines of create function foo () returns setof test as $$ select * from test order by value for update $$ language sql; select * from foo() order by value; which would accomplish the desired result of having two levels of sort. (You might or might not need the ORDER BY inside the function --- does your real case use ORDER BY/LIMIT, or does it really lock every row of the table?) regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs