[NOVICE] 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

On Fri, Nov 24, 2000 at 04:07:25AM -0600, some SMTP stream spewed forth:

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

*snippity snip snip*

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 :-)

Hell, do you suppose I could just set the column to float8 and let the
sequence run for the next several years? I would be happy if the sequence
buys me 3 or more years. ;-)
(Just get me to Morrocco, baby. ;-))

There would not be any problems with a sequence as high as max(float8), would
there?

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

Thanks.

gh

On Fri, Nov 24, 2000 at 04:16:52AM -0600, some SMTP stream spewed forth:

On Fri, Nov 24, 2000 at 04:07:25AM -0600, some SMTP stream spewed forth:

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

*snippity snip snip*

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 :-)

Hell, do you suppose I could just set the column to float8 and let the
sequence run for the next several years? I would be happy if the sequence
buys me 3 or more years. ;-)
(Just get me to Morrocco, baby. ;-))

There would not be any problems with a sequence as high as max(float8), would
there?

Er, to answer my own question, yes.
I seem to have discovered that the maximum maxvalue for a sequence is
9,999,999,999 (i.e. 10 digits) -- which leads one to believe that 10 is
the max num of digits for an integer-type column.

gh

Er, to answer my own question, yes.
I seem to have discovered that the maximum maxvalue for a sequence is
9,999,999,999 (i.e. 10 digits) -- which leads one to believe that 10 is
the max num of digits for an integer-type column.

[Must...stop...coding...while...asleep...and....braindead.]

The max for maxvalue is the max(integer) which I believe is 2147483647.

gh

Try a dump of your database using pg_dump. 4294967296 is the maxval if I
remember correctly. 32 bit integers are a good thing. Your question
still holds, what is the condition of overflow after maxval has been
reached.

Typically, you want to use a sequence like this for a unique opaque
indentifier (read: primary key) field in your db table schema. It's
probably not a good idea to reuse these, even if it is possible. One
thing you can do to ensure your data is not overwritten is to build a
unique constraint into the field (I think PostgreSQL expands this as a
trigger).

As far as trying to pick the first "hole" out of a set of numbers, that's
an interesting question. Or maybe it isn't. It makes me question your
schema design if you have to do off-the-wall things like that. In any
case, remember that you can't select something that's not there. Again,
question why it is that you're trying to do what you are :)

Hope this is of some help.

- Rob

On Thu, 23 Nov 2000, GH wrote:

On Fri, 24 Nov 2000, GH wrote:

On Fri, Nov 24, 2000 at 04:07:25AM -0600, some SMTP stream spewed forth:

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

*snippity snip snip*

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 :-)

Hell, do you suppose I could just set the column to float8 and let the
sequence run for the next several years? I would be happy if the sequence
buys me 3 or more years. ;-)
(Just get me to Morrocco, baby. ;-))

There would not be any problems with a sequence as high as max(float8), would
there?

What happens when you try to increment by 1, a number stored in
a float 8 which is (much) bigger than MAXVAL, but still smaller than
what a float 8 can hold? Absolutely nothing. A numerical test
of equality shows the 2 numbers are equal. You have to stick with
some kind of integer, but it will have to be spread across multiple
words. If int4 isn't big enough, int6 (your home-made int 6 that
is) would probably be big enough. If heat death really bothers
you, try int 8. There should be stuff in the GNU multi-precision
library or perl (bit vectors I think) which would let you write
this sort of stuff.

Gord

Matter Realisations http://www.materialisations.com/
Gordon Haverland, B.Sc. M.Eng. President
101 9504 182 St. NW Edmonton, AB, CA T5T 3A7
780/481-8019 ghaverla @ freenet.edmonton.ab.ca
780/993-1274 (cell)

GH <grasshacker@over-yonder.net> writes:

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

At some point someone will doubtless get around to supporting sequences
based on int8 rather than int4 values. Relying on that to happen before
you need it seems like a better answer than inventing slow,
hard-to-understand schemes for recycling sequence values.

regards, tom lane

GH wrote:

Er, to answer my own question, yes.
I seem to have discovered that the maximum maxvalue for a sequence is
9,999,999,999 (i.e. 10 digits) -- which leads one to believe that 10 is
the max num of digits for an integer-type column.

[Must...stop...coding...while...asleep...and....braindead.]

The max for maxvalue is the max(integer) which I believe is 2147483647.

So if you stick with the current standard, you can create ten per second
for the next 6.8 years before you have to worry.

I think that by then PostgreSQL will have INT8 sequences, or you will
have learned enough to implement them yourself :-)

Regards,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew@cat-it.co.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

On Sun, Nov 26, 2000 at 11:33:39PM +1300, some SMTP stream spewed forth:

GH wrote:

Er, to answer my own question, yes.
I seem to have discovered that the maximum maxvalue for a sequence is
9,999,999,999 (i.e. 10 digits) -- which leads one to believe that 10 is
the max num of digits for an integer-type column.

[Must...stop...coding...while...asleep...and....braindead.]

The max for maxvalue is the max(integer) which I believe is 2147483647.

So if you stick with the current standard, you can create ten per second
for the next 6.8 years before you have to worry.

Bu...bu...but, *that's not the /point/*! ;-))

heh.
I hate it when my noble efforts are shot down by realism and sensible
thinking. ;-)

The truly funny thing about it all is that I have since dropped the whole
bloody column from the table. Who needs a single unique column, anyway?
The sad truth is that I need to re-do a large part of the schema using
foreign keys and some other things.

Thanks for all your efforts.

gh