Re: Skipping numbers in a sequence.

On Fri, Nov 24, 2000 at 09:59:17PM +1300, some SMTP stream spewed forth:
> GH wrote:
> > Hello all.
> > It's like this. ;-)
> > I have a table with a column that has a default nextval('sequence').
> > The sequence is a standard increment 1 cycle sequence.
> > What happens when
> > the sequence wraps after inserting the
> > 2-million-and-whatever-th row
> > some of the earlier
> > rows (say, 1-100) are still in the table
> > but other rows or sections (say, 101-110 and 120-125) have
> > been deleted
> > and I need to begin with the first un-used sequence number?
>
> This sort of shit is to be avoided at all costs! Its going to all be
> horribly inefficient, really. :-)
>
> I usually leave the sequence to error at 4 billion, and if that hits me
> before the heat-death of the universe I will sort it out then :-)

I was thinking about something like that.
This table is used in such a way that each row corresponds to one item
in an order. So, I suppose I need to guesstimate the likely-hood that
a company would sell (or have-shopped) n items.

I just have that icky feeling that some day the sequence will roll over
and hell with come after my ass. ;-))

>
> You could add a trigger onto the table that implements the sequence,
> perhaps, to always set the nextval to something which is available, but
> this is icky.

---
>It would be easier to bite the bullet and write your own
> mynextval() function which found the first free value after the current
> "lastval", set "lastval" to that and handed it back for your new key.

I was hoping someone could guide me in the right direction as to going about
creating such a monster. How could I find the next free value? Would said
monster have to be written in something other than SQL, such as C?
---

At the moment, this sequence number serves no real purpose other
than providing something unique for each row. Perhaps I could
re-sequence the rows every once in a while? (That sounds like it
would be interesting...*I* am not even sure what I am thinking.)

The rows in this table will be relatively temporary, but must have some
column that is guaranteed unique for each row. That is the only reason
that I even have the sequence column.

Table in discussion:
key | order_num | item_id | quantity
1 | 1234 | abc123 | 12
2 | 1234 | 12blah | 6
etc.,etc.

Thanks

gh

>
> Cheers,
> Andrew.
> --
> _____________________________________________________________________
> Andrew McMillan, e-mail: Andrew(at)cat-it(dot)co(dot)nz
> Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
> Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267