Thread: using database for queuing operations?
I would like to try and build a queuing mechanism on top of Postgresql.
Imagine an application where a large number of processes generate images
and queue up thumbnail requests. A smaller number of processes (running
on a dedicated set of machines) generate thumbnails for those images.
Adding entries to the queue from multiple processes is easy, by executing
statements such as:
insert into nameq(action,name) values('add','foo');
Now comes the part I'm not sure about. I can easily write a front
end program that selects the lowest sequence number
select * from nameq where serial = (select min(serial) from nameq);
and then parcels that out to a subprocess for thumbnail generation.
It would be really great if I could handle this without the front end
program, so that multiple programs could do something like the following:
select next image to be processed (with above select logic)
process the image
delete the row for that image
I think that I can use "select for update" to obtain a write lock (so that
I can safely delete the row when finished), but I'm unsure if it's possible
to avoid the race condition where two processes would get the same row.
Any advice, comments, etc, appreciated!
Mark
---------------------------------------------------------
mh=# \d nameq
Table "public.nameq"
Column | Type | Modifiers
---------+-----------------------------+----------------------------------------------------
action | text | not null
name | text | not null
serial | bigint | default nextval('nameq_seq'::text)
addtime | timestamp without time zone | default ('now'::text)::timestamp(6) with time zone
Indexes:
"nameq_addtime" btree (addtime)
"nameq_ser" btree (serial)
mh=# select * from nameq;
action | name | serial | addtime
--------+------+--------+----------------------------
add | bar | 11 | 2004-09-20 11:50:19.756182
del | bar | 13 | 2004-09-20 11:50:25.080124
add | foo | 14 | 2004-09-20 11:50:28.536398
--
Mark Harrison
Pixar Animation Studios
Add a column to the nameq table designating the 'state' of the image.
Then your logic changes to "select * from nameq where serial = (select
min(serial) from nameq) and state="UNPROCESSED" (or whatever)
So you select for update, change the state, then process the
image....then delete.
Viola!
Mark Harrison wrote:
> I would like to try and build a queuing mechanism on top of Postgresql.
>
> Imagine an application where a large number of processes generate images
> and queue up thumbnail requests. A smaller number of processes (running
> on a dedicated set of machines) generate thumbnails for those images.
>
> Adding entries to the queue from multiple processes is easy, by executing
> statements such as:
>
> insert into nameq(action,name) values('add','foo');
>
> Now comes the part I'm not sure about. I can easily write a front
> end program that selects the lowest sequence number
>
> select * from nameq where serial = (select min(serial) from nameq);
>
> and then parcels that out to a subprocess for thumbnail generation.
> It would be really great if I could handle this without the front end
> program, so that multiple programs could do something like the following:
>
>
> select next image to be processed (with above select logic)
> process the image
> delete the row for that image
>
> I think that I can use "select for update" to obtain a write lock (so
> that
> I can safely delete the row when finished), but I'm unsure if it's
> possible
> to avoid the race condition where two processes would get the same row.
>
Jeff Amiel wrote: > Add a column to the nameq table designating the 'state' of the image. > Then your logic changes to "select * from nameq where serial = (select > min(serial) from nameq) and state="UNPROCESSED" (or whatever) > So you select for update, change the state, then process the > image....then delete. Thanks Jeff, I think that will work perfectly for me! Cheers, Mark -- Mark Harrison Pixar Animation Studios
On Mon, Sep 20, 2004 at 03:08:29PM -0500, Jeff Amiel wrote: > Add a column to the nameq table designating the 'state' of the image. > Then your logic changes to "select * from nameq where serial = (select > min(serial) from nameq) and state="UNPROCESSED" (or whatever) > So you select for update, change the state, then process the > image....then delete. > Viola! You should also consider what happens if the conversion program can't update the state to processed for some reason. For example, pgsql might get shutdown unexpectedly, or the conversion process could. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
Although....it wont really solve the race condition issue... you can still have a point where 2 processes select the same record...one gets the 'for update' lock on it and the other one just waits for it... Regardless of the 'state', once that lock releases, the second process will grab it. In my world I have a 'main' process that selects all the records that currently meet the criteria I am interested and them 'parse' them out to the sub-processes via unique id. Dont know if this helps.... Jeff Mark Harrison wrote: > Jeff Amiel wrote: > >> Add a column to the nameq table designating the 'state' of the image. >> Then your logic changes to "select * from nameq where serial = >> (select min(serial) from nameq) and state="UNPROCESSED" (or whatever) >> So you select for update, change the state, then process the >> image....then delete. > > > Thanks Jeff, I think that will work perfectly for me! > > Cheers, > Mark >
.....or instead change the logic to: So you: 1. select for update, with the criteria outlined 2. Check the state (again) to see of we had that particular race condition. 3. If already processed or in processing, somebody else must already be working on it....go back to step 1 4, change the state 5. process the image 6. delete. 7 go to step 1. change the state, then process the image....then delete. Jeff Amiel wrote: > Although....it wont really solve the race condition issue... > you can still have a point where 2 processes select the same > record...one gets the 'for update' lock on it and the other one just > waits for it... > Regardless of the 'state', once that lock releases, the second process > will grab it. > In my world I have a 'main' process that selects all the records that > currently meet the criteria I am interested and them 'parse' them out > to the sub-processes via unique id. > > Dont know if this helps.... > Jeff > > > > Mark Harrison wrote: > >> Jeff Amiel wrote: >> >>> Add a column to the nameq table designating the 'state' of the image. >>> Then your logic changes to "select * from nameq where serial = >>> (select min(serial) from nameq) and state="UNPROCESSED" (or whatever) >>> So you select for update, change the state, then process the >>> image....then delete. >> >> >> >> Thanks Jeff, I think that will work perfectly for me! >> >> Cheers, >> Mark >> > >
Mark Harrison wrote:
> select * from nameq where serial = (select min(serial) from nameq);
>
You might also want to try this as:
select * from nameq where serial = (select serial from nameq order
by serial asc limit 1);
and see if runs faster.
Ron
> So you:
>
> 1. select for update, with the criteria outlined
> 2. Check the state (again) to see of we had that particular race condition.
> 3. If already processed or in processing, somebody else must already be
> working on it....go back to step 1
> 4, change the state
> 5. process the image
> 6. delete.
> 7 go to step 1.
You can also rely on the old trick that, having selected min(serial) you
know that:
update nameq set state = 'processing'
where serial = xxx and state = 'unprocessed';
Will execute atomically and will set a row count of 0 or 1. You still have
some racing going on with the selects, but only 1 process ever gets hold of
a row to process. I've done similar things where tests showed that
collisions would be relatively rare--the following could really be bad if
processing didn't take "much time" and you had "a lot" of processes
extracting queue items. Excuse the atrocious mix of pseudo-sql and pseudo-C
and commentary:
select serial from nameq
where state = 'unprocessed' order by serial limit 10;
for( i = 0; i < 10 && i < actual num rows selected; ++i )
{
curserial = currow.seral;
update nameq set state = 'processing'
where serial = curserial and state = 'unprocessed';
if( rowcount == 1 )
{
process row;
update nameq set state = 'processed' where serial = curserial;
break;
}
else
{
pause some brief random time to prevent lock-step race
fetch next row
}
}
--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice
SELECT ... FOR UPDATE can and will produce a race condition if multiple back-ends attempt to access the same row at the exact same time. If you don't believe me, ask my gray hairs! :) Instead use LOCK TABLE your_table IN EXCLUSIVE MODE; Here's what I do: BEGIN; LOCK TABLE your_table IN EXCLUSIVE MODE; UPDATE your_table SET claimed_by = 'unique_processor_id', status = 'IN PROCESS' WHERE serial_pkey = (SELECT min(serial_pkey) FROM your_table WHERE status = 'UNPROCESSED') COMMIT; Then I can SELECT * FROM your_table WHERE claimed_by = 'unique_processor_id' AND status = 'IN PROCESS'; and I can be sure my multiple processors get one and only one row, marked for processing by one processor. The statements in the LOCKed transaction are completely serialized, but the subsequent selects are unencumbered by a lock. Many thanks to Tom Lane for this solution. It has worked like a charm for two years and counting. CG --- Jeff Amiel <jamiel@istreamimaging.com> wrote: > .....or instead change the logic to: > > So you: > > 1. select for update, with the criteria outlined > 2. Check the state (again) to see of we had that particular race condition. > 3. If already processed or in processing, somebody else must already be > working on it....go back to step 1 > 4, change the state > 5. process the image > 6. delete. > 7 go to step 1. > > > > change the state, then process the image....then delete. > > > > Jeff Amiel wrote: > > > Although....it wont really solve the race condition issue... > > you can still have a point where 2 processes select the same > > record...one gets the 'for update' lock on it and the other one just > > waits for it... > > Regardless of the 'state', once that lock releases, the second process > > will grab it. > > In my world I have a 'main' process that selects all the records that > > currently meet the criteria I am interested and them 'parse' them out > > to the sub-processes via unique id. > > > > Dont know if this helps.... > > Jeff > > > > > > > > Mark Harrison wrote: > > > >> Jeff Amiel wrote: > >> > >>> Add a column to the nameq table designating the 'state' of the image. > >>> Then your logic changes to "select * from nameq where serial = > >>> (select min(serial) from nameq) and state="UNPROCESSED" (or whatever) > >>> So you select for update, change the state, then process the > >>> image....then delete. > >> > >> > >> > >> Thanks Jeff, I think that will work perfectly for me! > >> > >> Cheers, > >> Mark > >> > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > _______________________________ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com
Clinging to sanity, jamiel@istreamimaging.com (Jeff Amiel) mumbled into her beard:
> .....or instead change the logic to:
>
> So you:
>
> 1. select for update, with the criteria outlined
> 2. Check the state (again) to see of we had that particular race condition.
> 3. If already processed or in processing, somebody else must already
> be working on it....go back to step 1
> 4, change the state
> 5. process the image
> 6. delete.
> 7 go to step 1.
>
> change the state, then process the image....then delete.
If you can identify some form of "process ID" for each of the
processors running concurrently, you might do something like:
1. Update for selection (converse of 'select for update' :-)
update nameq set action = 'in process', pid = 45676
where action <> 'in process' and (other criteria for grabbing the
record)
2. select * from nameq where pid = 45676 and action = 'in progress'
3. do your work, processing the image
4. update nameq set action= 'done', -- Or whatever is the appropriate
-- state
pid = NULL
where [criterion for the processed image...]
This way only one of the PIDs will get ownership of any given row for
step #2...
At the Unix level, this would be like making a "work" directory for
each work process, and having Step #1 try to do "mv file
$pid_work_dir".
The file can only get placed in one spot; if one "mv" wins, the others
necessarily lose. If one "set pid = my_pid" wins, no other one can do
so later.
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://www3.sympatico.ca/cbbrowne/linux.html
"It's like a house of cards that Godzilla has been blundering
through." -- Moon, describing how system messages work on ITS
What's the race in the SELECT FOR UPDATE? BTW, this is one nice thing about Oracle... it comes with a built-in queuing mechanism. It would probably be worth trying to write a generic queuing system and stick it in Gborg. Incidentally, Oracle also supports user-named locks, which would probably make this easier to do. LOCK TABLE works, but it's more brute force than is needed. Unfortunately, I don't see a way to simply add such a thing onto PostgreSQL without adding it to the core. On Mon, Sep 20, 2004 at 02:17:38PM -0700, Chris Gamache wrote: > > SELECT ... FOR UPDATE can and will produce a race condition if multiple > back-ends attempt to access the same row at the exact same time. If you don't > believe me, ask my gray hairs! :) Instead use > > LOCK TABLE your_table IN EXCLUSIVE MODE; > > Here's what I do: > > BEGIN; > LOCK TABLE your_table IN EXCLUSIVE MODE; > UPDATE your_table SET claimed_by = 'unique_processor_id', status = 'IN PROCESS' > WHERE serial_pkey = (SELECT min(serial_pkey) FROM your_table WHERE status = > 'UNPROCESSED') > COMMIT; > > Then I can > > SELECT * FROM your_table WHERE claimed_by = 'unique_processor_id' AND status = > 'IN PROCESS'; > > and I can be sure my multiple processors get one and only one row, marked for > processing by one processor. The statements in the LOCKed transaction are > completely serialized, but the subsequent selects are unencumbered by a lock. > > Many thanks to Tom Lane for this solution. It has worked like a charm for two > years and counting. > > CG > > --- Jeff Amiel <jamiel@istreamimaging.com> wrote: > > > .....or instead change the logic to: > > > > So you: > > > > 1. select for update, with the criteria outlined > > 2. Check the state (again) to see of we had that particular race condition. > > 3. If already processed or in processing, somebody else must already be > > working on it....go back to step 1 > > 4, change the state > > 5. process the image > > 6. delete. > > 7 go to step 1. > > > > > > > > change the state, then process the image....then delete. > > > > > > > > Jeff Amiel wrote: > > > > > Although....it wont really solve the race condition issue... > > > you can still have a point where 2 processes select the same > > > record...one gets the 'for update' lock on it and the other one just > > > waits for it... > > > Regardless of the 'state', once that lock releases, the second process > > > will grab it. > > > In my world I have a 'main' process that selects all the records that > > > currently meet the criteria I am interested and them 'parse' them out > > > to the sub-processes via unique id. > > > > > > Dont know if this helps.... > > > Jeff > > > > > > > > > > > > Mark Harrison wrote: > > > > > >> Jeff Amiel wrote: > > >> > > >>> Add a column to the nameq table designating the 'state' of the image. > > >>> Then your logic changes to "select * from nameq where serial = > > >>> (select min(serial) from nameq) and state="UNPROCESSED" (or whatever) > > >>> So you select for update, change the state, then process the > > >>> image....then delete. > > >> > > >> > > >> > > >> Thanks Jeff, I think that will work perfectly for me! > > >> > > >> Cheers, > > >> Mark > > >> > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > > _______________________________ > Do you Yahoo!? > Declare Yourself - Register online to vote today! > http://vote.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
Mark Harrison <mh@pixar.com> writes:
> It would be really great if I could handle this without the front end
> program, so that multiple programs could do something like the following:
> select next image to be processed (with above select logic)
> process the image
> delete the row for that image
> I think that I can use "select for update" to obtain a write lock (so that
> I can safely delete the row when finished), but I'm unsure if it's possible
> to avoid the race condition where two processes would get the same row.
See the archives; this has been discussed in great detail before
(several times before, if memory serves).
regards, tom lane
Tom Lane wrote: > See the archives; this has been discussed in great detail before > (several times before, if memory serves). > > regards, tom lane Sorry for the cluelessness, but searching on queuing, scheduling, and their spelling variants isn't turning up anything useful. Got something else I can search on? TIA! Mark PS, so far the comments received have been very useful... thanks so much!!!
Mark Harrison <mh@pixar.com> writes: > Tom Lane wrote: >> See the archives; this has been discussed in great detail before >> (several times before, if memory serves). > Sorry for the cluelessness, but searching on queuing, scheduling, > and their spelling variants isn't turning up anything useful. I got a bunch of hits on "select for update queue" from http://www.pgsql.ru/db/pgsearch/ , for instance http://archives.postgresql.org/pgsql-general/2003-05/msg00342.php http://archives.postgresql.org/pgsql-general/2001-02/msg00977.php http://archives.postgresql.org/pgsql-sql/2001-11/msg00378.php There seems to be some disconnect between that search engine and the archives though. For instance it also pointed me to http://archives.postgresql.org/pgsql-sql/2002-11/msg00001.php which does not exist; in fact archives.postgresql.org has hardly anything for that whole month of pgsql-sql. Marc, any idea what's wrong there? The data was obviously there last time Oleg trolled for it. regards, tom lane
> Tom Lane wrote: > > See the archives; this has been discussed in great detail before > > (several times before, if memory serves). > > > > regards, tom lane > > Sorry for the cluelessness, but searching on queuing, scheduling, > and their spelling variants isn't turning up anything useful. Got > something else I can search on? > > TIA! > Mark > > PS, so far the comments received have been very useful... thanks so much!!! As a side note on searching the archives, the search is broken at the moment, and has been for at least several days. I couldn't get any search results on just common terms like freebsd or linux... Chris
Well everybody else has thrown in there suggestions. I have several processes that do something similar to this. Granted I'm moving data around instead of processing images but the queue principles are the same. I use a nullable process_time to keep track of state because I maintain history file. First I spawn the task from a cron job periodically although this could also be done by a daemon. The process has a limit of how many units it will perform. Since my data units are small I have about 1000 max per process. SELECT serial FROM queue WHERE process_time IS NULL ORDER BY serial LIMIT 1000. This gives me 1000 units. I then loop through each unit as $this_serial. And try SELECT serial FROM queue WHERE serial = $this_serial AND process_date is NULL FOR UPDATE. I check if a row is returned. If it is not then I know another process took the job and continue on through the list. Next I move data, or in your case process the image. Finally if my process succeeds I UPDATE queue SET process_time = CURRENT_TIMESTAMP WHERE serial = $this_serial and commit the transaction otherwise I rollback and clear my lock. This works great for me because if the process fails then I never update and the next run will pick it up and retry. In my world I have to mainly deal with the possibility of runs colliding with each other. It seems to me that since you have several machines you might want to use a start time and a finish time. You could then have a garbage collection clear out finished jobs if you need. This also gives you the benefit of keeping track of zombied processes. On Mon, 20 Sep 2004 17:13:30 -0700, Mark Harrison <mh@pixar.com> wrote: > Tom Lane wrote: > > See the archives; this has been discussed in great detail before > > (several times before, if memory serves). > > > > regards, tom lane > > Sorry for the cluelessness, but searching on queuing, scheduling, > and their spelling variants isn't turning up anything useful. Got > something else I can search on? > > TIA! > Mark > > PS, so far the comments received have been very useful... thanks so much!!! > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
On Mon, 20 Sep 2004, Tom Lane wrote: > which does not exist; in fact archives.postgresql.org has hardly > anything for that whole month of pgsql-sql. Marc, any idea what's wrong > there? The data was obviously there last time Oleg trolled for it. fixed ... let me know if you notice any others like this ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
Oracle also has the NOWAIT option for use with the SELECT ... FOR UPDATE query. If the record is locked, the NOWAIT causes the query to generate an exception (rather than blocking) which can then be caught and handled - it helps prevent race conditions. A very nice feature! John Sidney-Woollett Jim C. Nasby wrote: > What's the race in the SELECT FOR UPDATE? > > BTW, this is one nice thing about Oracle... it comes with a built-in > queuing mechanism. It would probably be worth trying to write a generic > queuing system and stick it in Gborg. > > Incidentally, Oracle also supports user-named locks, which would > probably make this easier to do. LOCK TABLE works, but it's more brute > force than is needed. Unfortunately, I don't see a way to simply add > such a thing onto PostgreSQL without adding it to the core.
Tom Lane <tgl@sss.pgh.pa.us> writes: > which does not exist; in fact archives.postgresql.org has hardly > anything for that whole month of pgsql-sql. Marc, any idea what's wrong > there? The data was obviously there last time Oleg trolled for it. Oh, and btw, archives.postgresql.org search doesn't work at all if you specify a time period. You just get no results even though results from the past 2 years comes up fine when you don't specify a time period. -- greg
On Mon, Sep 20, 2004 at 11:57:20PM -0300, Marc G. Fournier wrote: > On Mon, 20 Sep 2004, Tom Lane wrote: > >which does not exist; in fact archives.postgresql.org has hardly > >anything for that whole month of pgsql-sql. Marc, any idea what's wrong > >there? The data was obviously there last time Oleg trolled for it. > > fixed ... let me know if you notice any others like this ... It looks like http://archives.postgresql.org/pgsql-hackers/2002-09/ is also pretty empty. It claims 1271 messages, but it doesn't have anything like that on the page. -Dom
Ahh, yes, forgot about that. Very handy to have. But even without that you wouldn't have a race condition, just a blocked process, right? On Tue, Sep 21, 2004 at 07:15:08AM +0100, John Sidney-Woollett wrote: > Oracle also has the NOWAIT option for use with the SELECT ... FOR UPDATE > query. If the record is locked, the NOWAIT causes the query to generate > an exception (rather than blocking) which can then be caught and handled > - it helps prevent race conditions. A very nice feature! > > John Sidney-Woollett > > Jim C. Nasby wrote: > >What's the race in the SELECT FOR UPDATE? > > > >BTW, this is one nice thing about Oracle... it comes with a built-in > >queuing mechanism. It would probably be worth trying to write a generic > >queuing system and stick it in Gborg. > > > >Incidentally, Oracle also supports user-named locks, which would > >probably make this easier to do. LOCK TABLE works, but it's more brute > >force than is needed. Unfortunately, I don't see a way to simply add > >such a thing onto PostgreSQL without adding it to the core. > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
I'd have to sit down and think about the answer to that and I'm too tired right now... ;) We found the NOWAIT option very useful for helping to get our stored procedures to behave in a more deterministic way, especially in a multi-threaded environment. John Sidney-Woollett Jim C. Nasby wrote: > Ahh, yes, forgot about that. Very handy to have. But even without that > you wouldn't have a race condition, just a blocked process, right? > > On Tue, Sep 21, 2004 at 07:15:08AM +0100, John Sidney-Woollett wrote: > >>Oracle also has the NOWAIT option for use with the SELECT ... FOR UPDATE >>query. If the record is locked, the NOWAIT causes the query to generate >>an exception (rather than blocking) which can then be caught and handled >>- it helps prevent race conditions. A very nice feature! >> >>John Sidney-Woollett >> >>Jim C. Nasby wrote: >> >>>What's the race in the SELECT FOR UPDATE? >>> >>>BTW, this is one nice thing about Oracle... it comes with a built-in >>>queuing mechanism. It would probably be worth trying to write a generic >>>queuing system and stick it in Gborg. >>> >>>Incidentally, Oracle also supports user-named locks, which would >>>probably make this easier to do. LOCK TABLE works, but it's more brute >>>force than is needed. Unfortunately, I don't see a way to simply add >>>such a thing onto PostgreSQL without adding it to the core. >> >>---------------------------(end of broadcast)--------------------------- >>TIP 3: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly >> > >
John Sidney-Woollett wrote: > I'd have to sit down and think about the answer to that and I'm too > tired right now... ;) > > We found the NOWAIT option very useful for helping to get our stored > procedures to behave in a more deterministic way, especially in a > multi-threaded environment. > > John Sidney-Woollett > > Jim C. Nasby wrote: > >> Ahh, yes, forgot about that. Very handy to have. But even without that >> you wouldn't have a race condition, just a blocked process, right? >> Not sure if this should be that way - the docs say that in case of locked rows the where clause is reevaluated: 1 session: lock row, update status. 2 session: test=# select * from test2 where nr=(select max(nr) from test2 where status='NEW') for update; <waits here> 1. session commit; 2.session results: nr | status ----+------------ 55 | PROCESSING (1 row) test=# Andre
"the docs say that in case of locked rows the where clause is reevaluated: " Is this true? Which docs are you refering to (I could find no mention in the postgres docs)..... Jeff
Jeff Amiel <jamiel@istreamimaging.com> writes: >> "the docs say that in case of locked rows the where clause is reevaluated: " > Is this true? > Which docs are you refering to (I could find no mention in the postgres > docs)..... Second paragraph in http://developer.postgresql.org/docs/postgres/transaction-iso.html#XACT-READ-COMMITTED regards, tom lane
"The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation, starting from the updated version of the row." Hey....that's neat. All this time, I've done an unncessary extra select to see if the value has changed because of competing processes trying to work on same row.... Once again...kudos to lists like this one and of course, RTFM. Jeff Tom Lane wrote: >Jeff Amiel <jamiel@istreamimaging.com> writes: > > >>>"the docs say that in case of locked rows the where clause is reevaluated: " >>> >>> > > > >>Is this true? >>Which docs are you refering to (I could find no mention in the postgres >>docs)..... >> >> > >Second paragraph in >http://developer.postgresql.org/docs/postgres/transaction-iso.html#XACT-READ-COMMITTED > > regards, tom lane > > > > -- Jeff Amiel Systems/Development Manager iStream Imaging, an iTeam Company jamiel@iStreamImaging.com (262) 796-0925 x1011