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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html