From: David Ecker <david@familie-ecker.net>
Subject: Re: Sequence question
References: <fo6dnX3_Oc9Ur-jcRVn-rw@speakeasy.net>
Lines: 46
User-Agent: KNode/0.7.7
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
Message-ID: <1098285405.OfvaWaCsriLMf7T5iDLfwA@ultrafeed>
Date: Wed, 20 Oct 2004 17:16:44 +0200
To: pgsql-general@postgresql.org

Far from being a perfect idea but a faster solution than stepping through
all holes:

1) Create a second table containing only one field of type of your key.
2) When you delete an entry place the delete key value in your second table
3) If you insert a new entry into your old table and your new table contains
a value, take the minimum value in the new table as your new key and delete
that entry from the new table. If the new table is empty just use the
sequence to get the new key value.

Hope that helps
David Ecker

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.
> 
> 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