Subject: Re: Sequence question
From: Tino Wildenhain <tino@wildenhain.de>
To: Eric E <whalesuit@bonbon.net>
Cc: pgsql-general@postgresql.org
In-Reply-To: <3sydnUgMwqQ80-ncRVn-uA@speakeasy.net>
References: <3sydnUgMwqQ80-ncRVn-uA@speakeasy.net>
Content-Type: text/plain
Message-Id: <1098286552.21062.333.camel@sabrina.peacock.de>
Mime-Version: 1.0
Date: Wed, 20 Oct 2004 17:35:53 +0200
Content-Transfer-Encoding: 7bit

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