Thread: using database for queuing operations?

using database for queuing operations?

From
Mark Harrison
Date:
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

Re: using database for queuing operations?

From
Jeff Amiel
Date:
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.
>


Re: using database for queuing operations?

From
Mark Harrison
Date:
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

Re: using database for queuing operations?

From
"Jim C. Nasby"
Date:
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?"

Re: using database for queuing operations?

From
Jeff Amiel
Date:
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
>


Re: using database for queuing operations?

From
Jeff Amiel
Date:
.....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
>>
>
>


Re: using database for queuing operations?

From
Ron St-Pierre
Date:
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


Re: using database for queuing operations?

From
Scott Ribe
Date:
> 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


Re: using database for queuing operations?

From
Chris Gamache
Date:
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

Re: using database for queuing operations?

From
Christopher Browne
Date:
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

Re: using database for queuing operations?

From
"Jim C. Nasby"
Date:
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?"

Re: using database for queuing operations?

From
Tom Lane
Date:
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

Re: using database for queuing operations?

From
Mark Harrison
Date:
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!!!

Re: using database for queuing operations?

From
Tom Lane
Date:
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

Re: using database for queuing operations?

From
"Chris Ochs"
Date:

> 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



Re: using database for queuing operations?

From
Kevin Barnard
Date:
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
>

Re: using database for queuing operations?

From
"Marc G. Fournier"
Date:
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

Re: using database for queuing operations?

From
John Sidney-Woollett
Date:
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.

Re: using database for queuing operations?

From
Greg Stark
Date:
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

Re: using database for queuing operations?

From
dom@happygiraffe.net (Dominic Mitchell)
Date:
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

Re: using database for queuing operations?

From
"Jim C. Nasby"
Date:
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?"

Re: using database for queuing operations?

From
John Sidney-Woollett
Date:
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
>>
>
>

Re: using database for queuing operations?

From
Andre Maasikas
Date:
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


Re: using database for queuing operations?

From
Jeff Amiel
Date:
"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


Re: using database for queuing operations?

From
Tom Lane
Date:
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

Re: using database for queuing operations?

From
Jeff Amiel
Date:
"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