From: Mark Wright <mwright@pro-ns.net>
To: pgsql-sql@postgresql.org
Subject: How do I implement FIFO?
Date: Thu, 27 May 1999 18:12:17 -0500
Message-ID: <000901bea896$60addb80$c62812ac@markw_compaq> (raw)
I want to be able to have multiple clients pulling single, mutually
exclusive records from a table in the same order that they were inserted
into the table. Imagine one group of people placing objects on a conveyor
belt, while at the other end another group of people are grabbing the first
item to come off the belt.
I think I know how to lay out the table:
create table XYZ
(
insert_seq_num serial,
is_processed boolean default FALSE,
some_data varchar(10)
);
Inserts are obvious. What I'm stumped by is how to write a SELECT that will
return the next available row in a way that two clients querying at the same
time can't grab the same one.
I think I can do it with temporary tables. I.e.
select * into temp ABC from XYZ
where is_processed = FALSE and
insert_seq_num = (select MIN(insert_seq_num)
from XYZ where is_processed = FALSE);
update XYZ set XYZ.is_processed = TRUE where exists
(select * from temp where ABC.insert_seq_num = XYZ.insert_seq_num);
select * from temp;
commit;
But even if that would work, temp tables aren't supported in 6.4. Is there
another way to do this without using temp tables?
Mark
---
Mark Wright
mwright@pro-ns.net
mark_wright@datacard.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: mwright@pro-ns.net
Subject: Re: How do I implement FIFO?
In-Reply-To: <000901bea896$60addb80$c62812ac@markw_compaq>
References: <000901bea896$60addb80$c62812ac@markw_compaq>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox