From: Tino Wildenhain <tino@wildenhain.de>
To: Eric E <whalesuit@bonbon.net>
Cc: pgsql-general@postgresql.org
Subject: Re: Sequence question
Date: Wed, 20 Oct 2004 21:29:25 +0200
Message-ID: <1098300565.4042.31.camel@Andrea.peacock.de> (raw)
In-Reply-To: <4176A5FB.5090708@bonbon.net>
References: <3sydnUgMwqQ80-ncRVn-uA@speakeasy.net>
<1098286552.21062.333.camel@sabrina.peacock.de>
<4176A5FB.5090708@bonbon.net>
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: tino@wildenhain.de, whalesuit@bonbon.net
Subject: Re: Sequence question
In-Reply-To: <1098300565.4042.31.camel@Andrea.peacock.de>
* 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