On Mon, 2009-01-05 at 09:03 -0600, Lee McKeeman wrote:
> I did not see anything that indicated to me that order by may not be
> handled properly at the read committed isolation level, so I do believe
> this to be erroneous behavior, and therefore a bug. I have attempted
> this in 8.3.4 and
> 8.2.6 as I have ready access to installations of these versions. I can
> likely get access to an 8.3.5 installation if necessary for this bug to
> be investigated, but don't have one available to me at this time.
This looks like a bug to me, as well. Transaction isolation affects
visibility of tuples, but ORDER BY should still work. Your example also
works if using FOR SHARE in connection 2.
The manual does have this to say about FOR UPDATE/SHARE:
"It is possible for a SELECT command using both LIMIT and FOR
UPDATE/SHARE clauses to return fewer rows than specified by LIMIT. This
is because LIMIT is applied first. The command selects the specified
number of rows, but might then block trying to obtain lock on one or
more of them. Once the SELECT unblocks, the row might have been deleted
or updated so that it does not meet the query WHERE condition anymore,
in which case it will not be returned."
-- http://www.postgresql.org/docs/8.3/static/sql-select.html
I'm sure something very similar is happening with ORDER BY, so it should
be documented at a minimum.
However, I think we should consider your issue more serious, because I
think this it a violation of the SQL standard. I've been wrong about the
SQL standard plenty of times though, so don't take my word for it ;)
Regards,
Jeff Davis