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

Mark Shewmaker <mark@primefactor.com> writes:
> In other words:  Is this a bug or a user misunderstanding:

You've got the function doing

>     LOOP
>       select * into myrow from mytable limit 1 for update;
>       if found then exit;
>       end if;
>     END LOOP;

which means it will loop infinitely if the SELECT finds no row.

Because "LIMIT 1" executes before "FOR UPDATE" does, it is possible that
the first row returned by the query is rejected by FOR UPDATE (in this
case because it was deleted by an already-committed transaction), and
then the query will not examine any more rows because of the LIMIT.

I think you could get the behavior you want with

      select * into myrow from mytable for update;

that is, just rely on the implicit LIMIT 1 associated with SELECT INTO
rather than writing one in the query.  Then you will get the first row
that passes the FOR UPDATE restriction, which I think is what you're
after.

There has been some argument about whether FOR UPDATE shouldn't execute
before LIMIT, but IIRC there are nasty corner cases in that case too.
So it's hard to say if this should be considered a bug or not.

            regards, tom lane