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 17:35:53 +0200
Message-ID: <1098286552.21062.333.camel@sabrina.peacock.de> (raw)
In-Reply-To: <3sydnUgMwqQ80-ncRVn-uA@speakeasy.net>
References: <3sydnUgMwqQ80-ncRVn-uA@speakeasy.net>
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: tino@wildenhain.de, whalesuit@bonbon.net
Subject: Re: Sequence question
In-Reply-To: <1098286552.21062.333.camel@sabrina.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