From: Eric E <whalesuit@bonbon.net>
To: Tino Wildenhain <tino@wildenhain.de>
Cc: pgsql-general@postgresql.org
Subject: Re: Sequence question
Date: Wed, 20 Oct 2004 15:37:12 -0400
Message-ID: <4176BE68.4010908@bonbon.net> (raw)
In-Reply-To: <1098300565.4042.31.camel@Andrea.peacock.de>
References: <3sydnUgMwqQ80-ncRVn-uA@speakeasy.net>
<1098286552.21062.333.camel@sabrina.peacock.de>
<4176A5FB.5090708@bonbon.net>
<1098300565.4042.31.camel@Andrea.peacock.de>
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
>
>
>
>
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 using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: whalesuit@bonbon.net, tino@wildenhain.de
Subject: Re: Sequence question
In-Reply-To: <4176BE68.4010908@bonbon.net>
* 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