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

On Wed, 2003-12-17 at 19:57, Tom Lane wrote:
> 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)?

Yes, or at least I've done the test that I think you're asking about.
(See Session_A-with-early-delete occurring with Session B_1 below.)

To be as clear as possible as to the actual tests run:

+-----------------+---------------------------------------------------+
|Run this in psql:| Along with one of these in a second psql session: |
+-----------------+-----------------+-----------------+---------------+
| Session A | Session B_1 | Session B_2 | Session B_3 |
+-----------------+-----------------+-----------------+---------------+
| | | | |
| # begin | | | |
| transaction | | | |
| | | | |
| | # begin | # begin | # begin |
| | transaction; | transaction | transaction |
| | | | |
| # select * from | | | |
| mytable limit | | | |
| 1 for update; | | | |
| (returns a=1) | | | |
| | | | |
|(NOTE: if the | | | |
|following delete | | | |
|statement is | | | |
|moved here,before| | | |
|session B_?'s | | | |
|selects, it has | | | |
|no effect on | | | |
|session B_?'s | | | |
|hangs & results) | | | |
| | | | |
| | # select * from | # select | # select |
| | mytable limit | myfunction(); | myfunction2();
| | 1 for update; | (hangs) | (hangs) |
| | (hangs) | (hangs) | (hangs) |
| # delete from | (hangs) | (hangs) | (hangs) |
| mytable where | (hangs) | (hangs) | (hangs) |
| a=1; | (hangs) | (hangs) | (hangs) |
| (succeeds with | (hangs) | (hangs) | (hangs) |
| "DELETE 1") | (hangs) | (hangs) | (hangs) |
| | (hangs) | (hangs) | (hangs) |
| # commit; | (hangs) | (hangs) | (hangs) |
| (succeeds with | (hangs) | (hangs) | (hangs) |
| "COMMIT") | (hangs) | (hangs) | (hangs) |
| | (returns with | (hangs) | (returns with |
| | no rows) | (hangs) | a=2) |
| | | (hangs) | |
| | # select * from | (hangs) | # delete from |
| | mytable limit | (hangs) | mytable |
| | 1 for update; | (hangs) | where a=2; |
| | (returns with | (hangs) | (succeeds with|
| | a=2) | (hangs) | "DELETE 1") |
| | | (hangs) | |
| | # delete from | (hangs) | # commit; |
| | mytable where | (hangs) | (succeeds with|
| | a=2; | (hangs) | "COMMIT") |
| | (succeeds with | (hangs) | |
| | "DELETE 1") | (hangs) | |
| | | (hangs) | |
| | # commit; | (hangs) | |
| | (succeeds with | (hangs) | |
| | "COMMIT") | (hangs) | |
| | | (hangs) | |
+-----------------+-----------------+-----------------+---------------+
| Before each test: # delete from mytable; |
| # insert into mytable (a) values (1); |
| # insert into mytable (a) values (2); |
| # insert into mytable (a) values (3); |
+---------------------------------------------------------------------+
| myfunction() mostly consists of: |
| |
| LOOP |
| select * into myrow from mytable limit 1 for update; |
| if found then exit; |
| end if; |
| END LOOP; |
| return myrow.a; |
+---------------------------------------------------------------------+
| myfunction2() mostly consists of: |
| |
| select * into myrow from mytable for update; |
| return myrow.a; |
+---------------------------------------------------------------------+

Does this contain the test case you were asking about?

("select * into myrow from mytable for update;" as you suggested, with
it's implicit limit done within the function is still a great solution
for me.)

> > 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.

Ahhh. Okay.

Now that you've explained that one a second time, it's finally begun to
sink in. :-)

(That doesn't explain the original difference discussed above, of
course.)

> > 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.

Thanks.

I'll search the archives further, and bring up it up in a more suitable
list if need be and where it will be more on topic.

--
Mark Shewmaker
mark(at)primefactor(dot)com