Re: Sequence question

Hi,

Am Mi, den 20.10.2004 schrieb Eric E um 19:52:
> Hi Tino,
> Many thanks for helping me.
>
> I know that the sequence issue is a troubling one for many on the list.
> Perhaps if I explain the need for a continuous sequence I can circumvent
> some of that:
>
> This database is for a laboratory, and the numbers in sequence
> determine storage locations for a sample. Having a physical space in
> our storage boxes tells us something has happened - the sample was used
> up, broken, in use, etc - and account for that missing sample. If the
> generated sequence has holes in it, we cannot tell if a sample is
> properly not in the rack, or if that hole was simply generated by the
> database. Allowing empties would also fill up limited box space with
> spaces generated by the database.
> If anyone has a brilliant idea for how a non-continuous sequence could
> address the needs, I'd be delighted to hear it, but short of that I
> think I have to keep this requirement.

Maybe you skip the sequence thingy alltogether in this case and
use an approach like this:

initialize a table with all possible locations and mark them
as empty.

CREATE TABLE locations (location_id int2,taken bool);

(you might want to have a timestamp for changes too)

Whenever you change state of a location, do it like this
(perhaps in a function)

SELECT INTO loc_id location_id FROM locations WHERE taken
FOR UPDATE;
IF FOUND THEN
UPDATE location SET taken=true WHERE location_id=loc_id;
ELSE
RAISE EXCEPTION 'no free location anymore';

...

AND the other way round for freeing a location.
The SELECT ... FOR UPDATE should lock the candidate
position in the table so concurrent
transactions have to wait then then find another
free cell when they wake up.

Advantage: not a full table scan. Only the first
matching row should be used and locked.

Not this is only a rough sketch and you should
look for the actual syntax and more flesh for
the function.

Regards
Tino