Re: [BUGS] Status of issue 4593 - Mailing list pgsql-hackers

"Robert Haas" <robertmhaas@gmail.com> writes:
> On Mon, Jan 12, 2009 at 8:32 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.

> If the only case where ORDER BY + FOR UPDATE are not strictly
> compatible is when the columns being updated are the same as the
> columns of the sort, a blanket prohibition against using the two
> together seems like it prohibits an awful lot of useful things someone
> might want to do.

Exactly.

> Saying that you can run the query a second time as
> a workaround so there's no loss of functionality is true only if you
> accept the proposition that performance is not a requirement.

Right, and also I'm unconvinced that it is really equivalent.  If you've
got something like an ORDER BY LIMIT and the ordering columns are
changing, you may very well get a different set of rows from the second
query ... not all of which got locked the first time, so there's going
to be some tail-chasing involved there.

A re-sort after locking doesn't really make things all nice and
intuitive either.  Suppose that the values of X are 10,20,30,40,50
and we do SELECT ... ORDER BY x LIMIT 3 FOR UPDATE.  Concurrently
someone updates the 20 to 100.  The existing code locks the 10,20,30
rows, then notices the 20 got updated to 100, and returns you
10,100,30.  If it re-sorted you would get 10,30,100, but on what
grounds is that the correct answer and not 10,20,40?  If you want
to argue that 10,20,40 is the correct answer, how are you going to
arrive at it without locking more rows than are returned?

And just to bend your brain a bit more, what if the same update command
that changed 20 to 100 also changed 50 to 1?  Surely if we take the one
row change into account in determining the sort order, we ought to
notice that one too.  However, we aren't even going to *see* that row
unless we traverse the entire table.

I think the behavior Lee is expecting is only implementable with a
full-table write lock, which is exactly what FOR UPDATE is designed
to avoid.  There are certain properties you don't get with a partial
lock, and in the end I think we can't do much except document them.
We have LOCK TABLE for those who need the other behavior.
        regards, tom lane