Re: Sequence question

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