Re: SELECT FOR UPDATE differs inside and outside a pl/pgsql

Mark Shewmaker <mark@primefactor.com> writes:
>     If a "FOR UPDATE executes before LIMIT" rule stopped the function
>     from ever locking a row, it's still curious why didn't it stop the
>     direct command from ever locking a row as well.

I think it would.  Did you try the test the other way around (with the
direct command being blocked behind someone who deletes the first row)?

> 2.  There's now a difference between the suggested "select * into
>     myrow from mytable for update;" run within a function, with its
>     JUST GREAT behavior, and the original "select * from mytable
>     limit 1 for update;":

Yeah, because plpgsql is implicitly letting you apply a LIMIT 1 after
the FOR UPDATE, whereas there is no way to get the equivalent behavior
in plain SQL.

> 4.  As an aside, since another way to directly solve the problem would
>     be a way to only select rows that aren't locked, are there any
>     thoughts on having that sort of functionality in a future revision?

There have been previous discussions about how to do this sort of
select-the-first-available-job logic, and IIRC people have found
suitable implementations.  Search the archives.  It's not on-topic
for pgsql-bugs, in any case.

            regards, tom lane