Thread: Skipping numbers in a sequence.
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?
I suppose that I would need to find the first un-used sequence number,
use setval() to update the sequence, and then insert the row that needs
to be insert-ed. Well, how can I find the first un-used sequence number?
I thought about doing something using a function like
select sequence_column from table
NOT [the set of numbers that make up the sequence]
but, how do I select the set of numbers that make up the sequence?
Is there a better/cleaner/easier way of getting the end result?
Postgres 7.0.2 (should be running .3, *sigh*)
FreeBSD 4.1-RELEASE
PHP 4.0.3pl1
Thanks fellas (and ladies, if applicable).
gh
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@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 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
>
>
> >
> > Cheers,
> > 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 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
>
> > 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
>
> >
> >
> > >
> > > Cheers,
> > > 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
> > 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 > > gh > > > > > > 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 > > > > > > > > > > > > > > > > Cheers, > > > > 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
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:
> 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?
>
> I suppose that I would need to find the first un-used sequence number,
> use setval() to update the sequence, and then insert the row that needs
> to be insert-ed. Well, how can I find the first un-used sequence number?
> I thought about doing something using a function like
> select sequence_column from table
> NOT [the set of numbers that make up the sequence]
> but, how do I select the set of numbers that make up the sequence?
>
> Is there a better/cleaner/easier way of getting the end result?
>
> Postgres 7.0.2 (should be running .3, *sigh*)
> FreeBSD 4.1-RELEASE
> PHP 4.0.3pl1
>
> Thanks fellas (and ladies, if applicable).
>
> gh
>
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 > 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