select order by for update - Mailing list pgsql-hackers

Well, I fixed one of two problems shown by Mark Wright
application (below) and found that subj doesn't work correctly:

--session1:
create table t (x int, y int);
insert into t values (1, 1);
insert into t values (2, 2);
begin;
update t set x = 3 where x = 1;

--session2:
select * from t order by x for update; -- waits

--session1:
commit;

session2 returns:
x|y
-+-
3|1
2|2

: FOR UPDATE in subj must be handled before sorting.
No ability to fix this in 6.5.X. Is  there the "known-bug"
doc? 

There is another problem in subj - sometimes application gets
ERROR: EvalPlanQual: t_xmin is uncommitted ?!
I'll try to find why. Mark (Wright), could you avoid
order by in PL function? If you really need in ordered
updates then try to create index on id_number and add
id_number >= 0 to WHERE in select for update.

Vadim
======================================================
Test Case:
----------
The following SQL script will create the tables, indices and function necessary
to reproduce the error.  If you then execute these commands, it should re-create
the problem:       perl -e 'for ($i=0;$i<200;++$i){system \               "psql -c \"select get_next_test_attendee();\"
>>$$.txt";}'&       perl -e 'for ($i=0;$i<200;++$i){system \               "psql -c \"select
get_next_test_attendee();\">> $$.txt";}'&       perl -e 'for ($i=0;$i<200;++$i){system \               "psql -c
\"selectget_next_test_attendee();\" >> $$.txt";}'&       perl -e 'for ($i=0;$i<200;++$i){system \               "psql
-c\"select get_next_test_attendee();\" >> $$.txt";}'&
 

(The same error occurs if I use DBI+DBD::Pg in a Perl script instead of psql.)

===============================================================================
                       Begin Script
 
===============================================================================
drop table test_attendees;
drop sequence test_attendees_id_number_seq;
create table test_attendees
(       id_number               serial,       print_status    char            default 'R',       name
varchar(20)
 
);
create index idx_test_attendees_name on test_attendees(name);


DROP FUNCTION get_next_test_attendee ();
CREATE FUNCTION get_next_test_attendee() returns int4 AS '
DECLARE       test_attendee_rec       RECORD;
BEGIN       FOR test_attendee_rec IN SELECT * FROM test_attendees                                        WHERE
print_status= ''R''                                       ORDER BY id_number                                        FOR
UPDATEOF test_attendees       LOOP               -- If more changes in test_attendee are to be made than just setting
           -- status to P, do them all in one UPDATE. The record is               -- locked now and the lock will
releaseonly when our entire               -- transaction commits or rolls back - not when we update it.
UPDATEtest_attendees SET print_status = ''Y''                        WHERE id_number = test_attendee_rec.id_number;
 
               -- Now we return from inside the loop at the first               -- row processed. This ensures we will
processone               -- row at max per call.               RETURN test_attendee_rec.id_number;       END LOOP;
 
       -- If we reach here, we did not find any row (left) with       -- print_status = R       return -1;

END;' LANGUAGE 'plpgsql';

insert into test_attendees (name) values ('name1');
insert into test_attendees (name) values ('name2');
...
I used 500 rows.