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 \"select get_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 UPDATE OF 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 release only when our entire
                -- transaction commits or rolls back - not when we update it.
                UPDATE test_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 process one
                -- 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.

Thu, 10 Jun 1999 11:14:33 +0800
Vadim Mikheev <vadim@krs.ru>
select order by for update 
> 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.

Ops, this will not work in all cases. Try to rewrite select:

SELECT * FROM test_attendees
WHERE print_status = 'R'
AND id_number = (select min(id_number) from test_attendees)
FOR UPDATE OF test_attendees

and run it in loop.

Vadim

Thu, 10 Jun 1999 11:33:32 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] select order by for update