Subject: Re: Sequence question
From: Tino Wildenhain <tino@wildenhain.de>
To: Eric E <whalesuit@bonbon.net>
Cc: pgsql-general@postgresql.org
In-Reply-To: <4176A5FB.5090708@bonbon.net>
References: <3sydnUgMwqQ80-ncRVn-uA@speakeasy.net>
	<1098286552.21062.333.camel@sabrina.peacock.de>
	<4176A5FB.5090708@bonbon.net>
Content-Type: text/plain
Message-Id: <1098300565.4042.31.camel@Andrea.peacock.de>
Mime-Version: 1.0
Date: Wed, 20 Oct 2004 21:29:25 +0200
Content-Transfer-Encoding: 7bit

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