Re: Status of issue 4593

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(at)opushealthcare(dot)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:

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:
From connection 1:
begin;
select * from test order by value for update; Return value:
value | key
-------+-----
20 | 1
25 | 2
30 | 3
500 | 4
(4 rows)

From connection 2:
begin;
select * from test order by value for update; At this point,
connection 2 waits on connection 1.

From connection 1:
update test set value = 40 where key = 1;
commit;

From connection 2:
previous query now returns:
value | key
-------+-----
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(at)pgadmin(dot)org]
Sent: Monday, January 05, 2009 8:57 AM
To: Lee McKeeman
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] Status of issue 4593

On Mon, Jan 5, 2009 at 2:47 PM, Lee McKeeman
<lmckeeman(at)opushealthcare(dot)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