 | |
Re: [BUGS] SELECT FOR UPDATE differs inside and outside a pl/pgsql
mailing.database.pgsql-bugs mailing.database.pgsql-bugs
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: [BUGS] SELECT FOR UPDATE differs inside and outside a pl/pgsql -
12-17-2003
, 07:00 PM
|
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. |
direct command being blocked behind someone who deletes the first row)?
Quote:
|
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;": |
the FOR UPDATE, whereas there is no way to get the equivalent behavior
in plain SQL.
Quote:
|
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? |
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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org
#2
| ||||
| ||||
|
|
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)? |
(See Session_A-with-early-delete occurring with Session B_1 below.)
To be as clear as possible as to the actual tests run:
+-----------------+---------------------------------------------------+
Quote:
|
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.)
Quote:
|
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. |
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.)
Quote:
|
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. |
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
#3
| |||
| |||
|
|
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. |
behavior I expected: the first invocation of SELECT FOR UPDATE
fails to lock any row. You manually did the equivalent of looping
as in myfunction(). So it looks the same to me.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.