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

Mark Shewmaker <mark(at)primefactor(dot)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