Message-ID: <41753069.3000406@bonbon.net>
Date: Tue, 19 Oct 2004 11:19:05 -0400
From: Eric E <whalesuit@bonbon.net>
User-Agent: Mozilla Thunderbird 0.7 (Windows/20040616)
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: Sequence question
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.

So my question is:
what's the most effective way to get the next available number?

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.

Does anyone have any better ideas?  Places I should look?

Thanks,

Eric