While this behavior may be very old, I would still contend that it is incorrect (or at least inconsistent with one's expectations). If it will not be changed, some additional documentation might be helpful. Perhaps a WARNING could be raised (unconditionally, as it might be a bit intensive to detect when the problem has occurred)?
It may be nice, though it would obviously be an extension, to have session-level settings that will cause 2 queries to be run independently, first the for update, second the order by when both are present, so one doesn't have to handle this every place they want to perform queries with these two clauses. Alternatively, since two queries is less efficient, perhaps if this setting is active a temporary function can be created to implement the work-around Tom mentioned earlier in the thread automatically (though perhaps creating and compiling the function would be slower than the second select in some cases). Again, this would allow for correct behavior without a great deal of schema and code modification. I guess my real point is that while no one else has complained about it, I'm confident others do and will continue to use these two clauses in the same query. In general, when one uses ORDER BY, they expect their results ordered based on the data returned. Being able to explain the reason for this inconsistency is nice, but it certainly doesn't invalidate the problem. -Lee -----Original Message----- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Monday, January 12, 2009 7:33 AM To: Peter Eisentraut Cc: PG Hackers; Jeff Davis; Lee McKeeman Subject: Re: [BUGS] Status of issue 4593 Peter Eisentraut <pete...@gmx.net> writes: > I can see two ways forward: > 1) We document bluntly that ORDER BY + FOR UPDATE can return unordered > results, or > 2) We prohibit ORDER BY + FOR UPDATE, like we do with a number of other > clauses. (There would be no loss of functionality, because you can run > the query a second time in the transaction with ORDER BY.) That code has been working like this for eight or ten years now and this is the first complaint, so taking away functionality on the grounds that someone might happen to update the ordering column doesn't seem like the answer to me. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
