Re: Sequence question

Hmm....  that's a really intesting idea, Tino.  Since we're probably
talking about 1000000 numbers max, a query on this table would work
fairly fast, and operationally simple.  I'll think about that.

Thanks,

Eric


Tino Wildenhain wrote:

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