Обсуждение: Status of issue 4593
This may not be the appropriate place to check this, but when I filed the bug with the tracking number 4593, in relation to some sort order behavior which seemed erroneous to me. I didn't know how I would be notified if this was actually considered a bug, etc. so I thought I'd post here and see what I came up with. If there is a more appropriate means of tracking bugs by number, please let me know. =20 Thank you, Lee McKeeman
"Lee McKeeman" <lmckeeman@opushealthcare.com> writes:
> This may not be the appropriate place to check this, but when I filed
> the bug with the tracking number 4593, in relation to some sort order
> behavior which seemed erroneous to me.
That bug number never came by here --- might've gotten eaten by spam
filters? Anyway, we've seen many many complaints about strange sort
ordering, and every one of them boiled down to Postgres doing what
the LC_COLLATE locale setting told it to. Try "show lc_collate".
If it's not "C" you might wish to re-initdb in C locale.
regards, tom lane
I got a "stalled post" message because at the time of filing I was not
on this list. I don't know when moderators would look at it, and if
perhaps they deemed that it should not be posted, so it was discarded
without me being notified. I have the text that was generated by the web
form, and can send it again now that I am on the list, but I don't want
to do so if that's bad form.
To add a little detail, this wasn't that I believed a sort wasn't
correct alphabetically, lexicographically, numerically, etc., but rather
when the sort was being performed.
-Lee
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Sent: Monday, January 05, 2009 8:39 AM
To: Lee McKeeman
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] Status of issue 4593=20
"Lee McKeeman" <lmckeeman@opushealthcare.com> writes:
> This may not be the appropriate place to check this, but when I filed
> the bug with the tracking number 4593, in relation to some sort order
> behavior which seemed erroneous to me.
That bug number never came by here --- might've gotten eaten by spam
filters? Anyway, we've seen many many complaints about strange sort
ordering, and every one of them boiled down to Postgres doing what
the LC_COLLATE locale setting told it to. Try "show lc_collate".
If it's not "C" you might wish to re-initdb in C locale.
regards, tom lane
On Mon, Jan 5, 2009 at 2:47 PM, Lee McKeeman <lmckeeman@opushealthcare.com> wrote: > I got a "stalled post" message because at the time of filing I was not > on this list. I don't know when moderators would look at it, and if > perhaps they deemed that it should not be posted, so it was discarded > without me being notified. We don't moderate bug reports, except to weed out spam. My guess is that yours was dropped in error. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
In that case, I will paste what I got back when I entered the bug via
the web form:
- - - - - -
The following bug has been logged online:
Bug reference: 4593
Logged by: Lee McKeeman
Email address: lmckeeman@opushealthcare.com
PostgreSQL version: 8.3.4, 8.2.6
Operating system: Red Hat Enterprise Linux Server release 5
Description: order by is not honored after select ... for update
when
row-lock is encountered
Details:=20
Some brief background: our application depends on the ordering of
results in a number of cases, and we have been relying on the order by
clause to provide this.
Steps to reproduce:
First, steps need to be performed via two connections. I will provide
the SQL to set up the database state, then the steps, in order, to be
performed on each connections.
Setup:
create table test (value int, key int primary key); insert into test
(key,value) values (1,20); insert into test (key,value) values (2,25);
insert into test (key,value) values (3,30); insert into test (key,value)
values (4,500);
Seemingly erroneous scenario:
=46rom connection 1:
begin;
select * from test order by value for update; Return value:
value | key=20
-------+-----
20 | 1
25 | 2
30 | 3
500 | 4
(4 rows)
=46rom connection 2:
begin;
select * from test order by value for update; At this point,
connection 2 waits on connection 1.
=46rom connection 1:
update test set value =3D 40 where key =3D 1;
commit;
=46rom connection 2:
previous query now returns:
value | key=20
-------+-----
40 | 1
25 | 2
30 | 3
500 | 4
(4 rows)
At this point the transaction on connection 2 can be ended, this is all
that is necessary to demonstrate this behavior. The order by clause was
not honored insofar as the data returned does not match the order the
rows are returned in. The order is, instead, in the order the rows would
have been in before the transaction on connection 1 was completed.
I visited #postgresql on FreeNode on Friday and was told that this was
not a bug, and I needed to use:
set transaction isolation level serializable; then handle the possible:
"could not serialize access due to concurrent update"
errors. I also read:
http://www.postgresql.org/docs/8.3/interactive/transaction-iso.html
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.
I am currently working on a work-around in our software using the
serializable isolation level, but it obviously adds complexity.
Apologies if this is indeed expected behavior, but having the data by
which a result set should be ordered by failing to match the actual
order returned does seem like a bug from my perspective.
If clarification is needed, please contact me at the address provided.
Thank you,
Lee McKeeman
- - - - -
I don't know how issue numbers are assigned, and I can re-enter this via
the web form if that would be helpful.
-Lee
-----Original Message-----
From: Dave Page [mailto:dpage@pgadmin.org]=20
Sent: Monday, January 05, 2009 8:57 AM
To: Lee McKeeman
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] Status of issue 4593
On Mon, Jan 5, 2009 at 2:47 PM, Lee McKeeman
<lmckeeman@opushealthcare.com> wrote:
> I got a "stalled post" message because at the time of filing I was not
> on this list. I don't know when moderators would look at it, and if
> perhaps they deemed that it should not be posted, so it was discarded
> without me being notified.
We don't moderate bug reports, except to weed out spam. My guess is
that yours was dropped in error.
--=20
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
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
"Lee McKeeman" <lmckeeman@opushealthcare.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
Tom,
We don't actually select * without a where clause in our actual use
case, I just wrote as concise a test case as I thought I could to
demonstrate the behavior. We have a where clause that limits the rows
that are locked (otherwise we could just do a table lock rather than
using row-level locking). In our actual case, the order by uses a
function that generates ordinality based on some external values. If we
were to use your function suggestion, it could accept two arguments that
would be used in the where clause, and the select ... for update in the
function would not need to do any ordering (there is no limit involved
here), then when selecting from the function the order by could be
applied (I think). It's something we could work with.
Right now we are selecting twice to work around this, because once we
have the rows locked in our transaction, the order (as far as I can
imagine) should not change after the lock is acquired. This is somewhat
inefficient, but the where clause uses two indexed columns, so it isn't
terrible. The initial select ... for update at this point is not using
an order, so it is a bit faster, then the second does use the order and
should always be correct since the rows are locked.
Based on your explanation I understand the reason for this behavior
which was what I and my colleagues had guessed, but you didn't assert
that this is expected/correct behavior. Is it?
Ultimately, if we were to use your function suggestion, we would have
something like:
create function mytablefunction(integer,integer) returns setof mytable
as
$$ select * from mytable where col1 > $1 and col2 =3D $2 for update $$
language sql;
select * from mytablefunction(10,1000) order by sortfunc(col3);
I don't know if, in the select, a column from the resulting rowset can
be used in a function in the order by clause. I've never tried it
before, but don't see why it wouldn't work.
This seems workable, and wouldn't add a terrible amount of complexity.
Also, it is certainly valid to do the sort in our app, but we had simply
come to depend on ORDER BY, and built up the necessary infrastructure in
the database(functions, etc.) to do all the ordering there. We certainly
could pull the sorting into the app, it would just be much uglier than
using the database.
Thanks,
-Lee
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Sent: Monday, January 05, 2009 2:42 PM
To: Lee McKeeman
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] Status of issue 4593=20
"Lee McKeeman" <lmckeeman@opushealthcare.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
On Mon, 2009-01-05 at 15:42 -0500, Tom Lane wrote:
> 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);
How would that work in the case of an index scan sort?
Regards,
Jeff Davis
Jeff Davis <pgsql@j-davis.com> writes:
> On Mon, 2009-01-05 at 15:42 -0500, Tom Lane wrote:
>> 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);
> How would that work in the case of an index scan sort?
It wouldn't, which is one of the problems with doing it any other way...
I don't think there's a bug here, at least not in the sense that it
isn't Operating As Designed. But it does seem like we could do with
some more/better documentation about exactly how FOR UPDATE works.
The sequence of operations is evidently a bit more user-visible than
I'd realized.
regards, tom lane
On Tuesday 06 January 2009 02:03:14 Tom Lane wrote: > I don't think there's a bug here, at least not in the sense that it > isn't Operating As Designed. =C2=A0But it does seem like we could do with > some more/better documentation about exactly how FOR UPDATE works. > The sequence of operations is evidently a bit more user-visible than > I'd realized. Well, if the effect of ORDER BY + FOR UPDATE is "it might in fact not be=20 ordered", then it's pretty broken IMO. It would be pretty silly by analogy= =20 for example, if the effect of GROUP BY + FOR UPDATE were "depending on=20 concurrent events, it may or may not be fully grouped".