Re: SELECT FOR UPDATE differs inside and outside a pl/pgsql - Mailing list pgsql-bugs
| From | Mark Shewmaker |
|---|---|
| Subject | Re: SELECT FOR UPDATE differs inside and outside a pl/pgsql |
| Date | |
| Msg-id | 1071739715.2083.481.camel@k9 Whole thread Raw |
| In response to | Re: SELECT FOR UPDATE differs inside and outside a pl/pgsql (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: SELECT FOR UPDATE differs inside and outside a pl/pgsql
|
| List | pgsql-bugs |
On Wed, 2003-12-17 at 19:57, Tom Lane wrote:
> 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)?
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@primefactor.com
pgsql-bugs by date: