SELECT FOR UPDATE differs inside and outside a pl/pgsql function (7.4)

Should there be a difference between the behavior of a "select for
update" typed into psql directly versus "select into variable_name for
update" done within a function?

In other words: Is this a bug or a user misunderstanding:

1. Run the following commands to set up a table called
mytable and a function called myfunction:

------------------------------

create table mytable (a int);
insert into mytable values (1);
insert into mytable values (2);
insert into mytable values (3);

create or replace function myfunction() returns integer as '
DECLARE
myrow mytable%ROWTYPE;
BEGIN
LOOP
select * into myrow from mytable limit 1 for update;
if found then exit;
end if;
END LOOP;
return myrow.a;
end; ' language 'plpgsql';
------------------------------

2. Then open up two psql sessions and run the following commands:

+-----------------------------------------+----------------------+
| psql Session A | psql Session B |
+-----------------------------------------+----------------------+
|begin transaction; | |
| | begin transaction; |
|select * from mytable limit 1 for update;| |
| | select myfunction(); |
|delete from mytable where a=1; | |
|commit; | |
+-----------------------------------------+----------------------+

Session B's "select myfunction();" will hang, and it will continue to
hang even after session A commits. Is this expected behavior?

There are two ways to have session B not hang after the commit:

1. Don't do the "delete from mytable where a=1;".

Session B's "select myfunction();" will then return after
Session A commits, and with a value of 1.

Or,

2. Instead of running "select myfunction();" in Session B,
run two manual "select * from mytable limit 1 for update;"s.

The first manual select-for-update will hang until Session A's
transaction commits, after which the second manual select-for-update
in session A will succeeds.

This one really confuses me--should a function not be able
to find a row when a manual command can?

So I guess I'm curious as to:

1. Whether this is a bug or not. I'm guessing yes.

(I expected the multiple select-for-update attempts in the
function to the same behavior as multiple select-for-update's
done manually. That is, I expected both types of B's
select-for-updates selecting locked rows to hang until session A's
commit, immediately fail to find any row, and then succeed on the
next try. It would be nice if session B's first select were to
have transparently succeeded on the a=2 row, something which I think
would be a legal thing to happen, but as it's not what the
documentation implies would happen I didn't expect that. In any
event I did not expect the select-for-update within pgsql to
continually fail to find a row.)

2. If there's a better way to have multiple transactions lock rows
with select-for-update without any chance of the transaction
erroring out, (such as would occur with serializable--and that
would mean I'd have to have to move more of the logic to the
application.)

(I don't know of a way to avoid even the busy-looping--though I
could call sleep functions from plperl or something to lower the
cpu load. BTW, it would be nice to have some plpgsql-native sleep
function just to more easily test for problems like this.)

3. If there's some really elegant solution out there, such as a
way to do a "select for update where not locked" to search for
rows no one has a conflicting lock on. (To me this would seem
to be the best of all possible solutions.)

I'm running version() "PostgreSQL 7.4 on i686-pc-linux-gnu, compiled
by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7)".

--
Mark Shewmaker
mark at primefactor dot com