Re: Status of issue 4593

"Lee McKeeman" <lmckeeman(at)opushealthcare(dot)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