Re: [BUGS] Status of issue 4593

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:tgl(at)sss(dot)pgh(dot)pa(dot)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 <peter_e(at)gmx(dot)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