Message-ID: <4176BE68.4010908@bonbon.net>
Date: Wed, 20 Oct 2004 15:37:12 -0400
From: Eric E <whalesuit@bonbon.net>
User-Agent: Mozilla Thunderbird 0.7 (Windows/20040616)
MIME-Version: 1.0
To: Tino Wildenhain <tino@wildenhain.de>
Cc: pgsql-general@postgresql.org
Subject: Re: Sequence question
References: <3sydnUgMwqQ80-ncRVn-uA@speakeasy.net>	
	<1098286552.21062.333.camel@sabrina.peacock.de>	
	<4176A5FB.5090708@bonbon.net>
	<1098300565.4042.31.camel@Andrea.peacock.de>
In-Reply-To: <1098300565.4042.31.camel@Andrea.peacock.de>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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