471,092 Members | 1,567 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #1
25 2595
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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #3
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 de*****@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?"

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #4
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 4: Don't 'kill -9' the postmaster

Nov 23 '05 #5
......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

Nov 23 '05 #6
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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #7
> 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
sc********@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #8

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 <ja****@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

Nov 23 '05 #9
Clinging to sanity, ja****@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
Nov 23 '05 #10
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 <ja****@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 de*****@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?"

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

Nov 23 '05 #11
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #12
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

Nov 23 '05 #13
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...5/msg00342.php
http://archives.postgresql.org/pgsql...2/msg00977.php
http://archives.postgresql.org/pgsql...1/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...1/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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #14

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #15
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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #16
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: sc*****@hub.org Yahoo!: yscrappy ICQ: 7615664

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #17
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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #18

Tom Lane <tg*@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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #19
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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #20
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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly


--
Jim C. Nasby, Database Consultant de*****@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?"

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #21
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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #22
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #23
"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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #24
Jeff Amiel <ja****@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...READ-COMMITTED

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

Nov 23 '05 #25
"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 <ja****@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...READ-COMMITTED

regards, tom lane

--
Jeff Amiel
Systems/Development Manager
iStream Imaging, an iTeam Company
ja****@iStreamImaging.com
(262) 796-0925 x1011

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #26

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Lothar Armbr?ster | last post: by
13 posts views Thread by m0002a | last post: by
4 posts views Thread by Just D. | last post: by
1 post views Thread by tiger | last post: by
1 post views Thread by Dave Pink | last post: by
1 post views Thread by Jason Richmeier | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.