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 13:52:59 -0400
Message-ID: <4176A5FB.5090708@bonbon.net> (raw)
In-Reply-To: <1098286552.21062.333.camel@sabrina.peacock.de>
References: <3sydnUgMwqQ80-ncRVn-uA@speakeasy.net>
<1098286552.21062.333.camel@sabrina.peacock.de>
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.
One thought I had, and I'd love to hear what people think of this, is to
build a table of storage location numbers that are available for use.
That way the search for new numbers could be pushed off until some
convenient moment well after the user requests them.
Thanks again for any ideas.
Cheers,
Eric
Tino Wildenhain wrote:
>Hi,
>
>On Tue, 2004-10-19 at 01:16, Eric E wrote:
>
>
>>Hi,
>> I have a question about sequences. I need a field to have values with
>>no holes in the sequence. However, the values do not need to be in order.
>>
>>My users will draw a number or numbers from the sequence and write to
>>the field. Sometimes, however, these sequence numbers will be discarded
>>(after a transaction is complete), and thus available for use. During
>>the transaction, however, any drawn numbers need to be unavailable.
>>I would like the next user who draws a number to draw the lowest number
>>she can, starting with the holes in the sequence.
>>
>>This continuous sequence is absolutely required by our company, as the
>>fact that the sequence has no holes is used to check for much more
>>serious problems.
>>
>>
>
>I would recheck this requirement. What should actually be achieved
>with the check for no holes in the numbering?
>Remember you can always enumerate using a set returning function
>or by means of a temporary sequence for a query.
>
>
>
>>So my question is:
>>what's the most effective way to get the next available number?
>>
>>
>
>There is none.
>
>
>
>>My present method is to do a query that finds the first and last number
>>in each of the holes, step through those holes, and then start
>>generating new numbers. Unfortunately, this involves doing a table scan
>>each time - before I generate the number, and does not produce the
>>transaction-safety I want.
>>
>>
>
>You cannot eat the cake and keep it - either you have holes
>or you have transaction security or you have bad performance
>by locking the whole table on insert.
>
>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: <4176A5FB.5090708@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