Rollover in Identity/Serial data types?
A theoretical discussion rather than a support question: the poster argued that SERIAL columns eventually wrap around and collide with existing index entries, and suggested the engine could scan the B-tree to find the next free value after rollover. Replies pointed out that SERIAL8/BIGSERIAL already allows values up to 9,223,372,036,854,775,807 — one poster calculated that even at 10 trillion rows a day it would take 250+ years to exhaust — and that reusing gaps would break the implied ordering of serials. The thread degenerated into personal jibes with no resolution or change recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
One of the issues with a serial data type is that if you roll past the end, you have an issue of collisions and errors in the backing index. The current solution is to just increase the size and hope that its large enough that you won't run out of numbers. This is true of any and all of the databases. But in theory, when a roll over occurs, you should be able to do some predictive analysis and determine the next open slot within the B-Tree index. While this is a very esoteric issue, as disk becomes cheap and more data is captured, this may become an issue faster than people think.
Ian Michael Gumby wrote: > One of the issues with a serial data type is that if you roll past the > end, you have an issue of collisions and errors in the backing index. > > The current solution is to just increase the size and hope that its > large enough that you won't run out of numbers. > This is true of any and all of the databases. > > But in theory, when a roll over occurs, you should be able to do some > predictive analysis and determine the next open slot within the B-Tree > index. > > While this is a very esoteric issue, as disk becomes cheap and more > data is captured, this may become an issue faster than people think. > > For most people there is an implied time ordering with serials which your suggestion would break That's what serial8 is for It ranges to 9,223,372,036,854,775,807 which is probably enough for most applications
On Feb 6, 10:07 am, Clive Eisen <cl...@serendipita.com> wrote: > Ian Michael Gumby wrote: > > One of the issues with a serial data type is that if you roll past the > > end, you have an issue of collisions and errors in the backing index. > > > The current solution is to just increase the size and hope that its > > large enough that you won't run out of numbers. > > This is true of any and all of the databases. > > > But in theory, when a roll over occurs, you should be able to do some > > predictive analysis and determine the next open slot within the B-Tree > > index. > > > While this is a very esoteric issue, as disk becomes cheap and more > > data is captured, this may become an issue faster than people think. > > For most people there is an implied time ordering with serials which > your suggestion would break > No, it doesn't. With multiple OLTP transactions occurring simultaneously, with a serial you only guarantee uniqueness. You don't guarantee order. And with the current implementation, when you go past the end of the largest number of a serial, you start over. So today, you still wrap around. > That's what serial8 is for > > It ranges to 9,223,372,036,854,775,807 which is probably enough for most > applications I know. Its a large number. But I'm starting to see some applications where its possible to surpass that number. Remember this doesn't mean that the table has to hold that many rows since they can be purged, but the serial counter will continue to grow. This is more of a theoretical issue. The idea is if you can look at a b-tree index and find the next open node.
Ian Michael Gumby wrote: > On Feb 6, 10:07 am, Clive Eisen <cl...@serendipita.com> wrote: > >> Ian Michael Gumby wrote: >> >>> One of the issues with a serial data type is that if you roll past the >>> end, you have an issue of collisions and errors in the backing index. >>> >>> The current solution is to just increase the size and hope that its >>> large enough that you won't run out of numbers. >>> This is true of any and all of the databases. >>> >>> But in theory, when a roll over occurs, you should be able to do some >>> predictive analysis and determine the next open slot within the B-Tree >>> index. >>> >>> While this is a very esoteric issue, as disk becomes cheap and more >>> data is captured, this may become an issue faster than people think. >>> >> For most people there is an implied time ordering with serials which >> your suggestion would break >> >> > No, it doesn't. > I didn't say that it was strictly ascending. I do know the odd thing or three about this stuff. > With multiple OLTP transactions occurring simultaneously, with a > serial you only guarantee uniqueness. You don't guarantee order. > And with the current implementation, when you go past the end of the > largest number of a serial, you start over. So today, you still wrap > around. > > >> That's what serial8 is for >> >> It ranges to 9,223,372,036,854,775,807 which is probably enough for most >> applications >> > > I know. Its a large number. But I'm starting to see some applications > where its possible to surpass that number. > Remember this doesn't mean that the table has to hold that many rows > since they can be purged, but the serial counter will continue to > grow. > > This is more of a theoretical issue. The idea is if you can look at a > b-tree index and find the next open node. > > I'm sorry I forgot that you are always correct in every respect
On Feb 6, 10:24 am, Ian Michael Gumby <im_gu...@hotmail.com> wrote: > On Feb 6, 10:07 am, Clive Eisen <cl...@serendipita.com> wrote: > > That's what serial8 is for > > > It ranges to 9,223,372,036,854,775,807 which is probably enough for most > > applications > > I know. Its a large number. But I'm starting to see some applications > where its possible to surpass that number. > Remember this doesn't mean that the table has to hold that many rows > since they can be purged, but the serial counter will continue to > grow. > I am curious what you define as where it is possible to surpass this number. By my math even at a 10 trillion transactions a day (10,000,000,000,000). it still takes 92,230 days(over 250 years) to get past this number. As a note, my math says 10 trillion a day comes out to 115 million a second. Am I missing something? Will
ricew wrote: > On Feb 6, 10:24 am, Ian Michael Gumby <im_gu...@hotmail.com> wrote: >> On Feb 6, 10:07 am, Clive Eisen <cl...@serendipita.com> wrote: >>> That's what serial8 is for >>> It ranges to 9,223,372,036,854,775,807 which is probably enough for most >>> applications >> I know. Its a large number. But I'm starting to see some applications >> where its possible to surpass that number. >> Remember this doesn't mean that the table has to hold that many rows >> since they can be purged, but the serial counter will continue to >> grow. >> > > I am curious what you define as where it is possible to surpass this > number. > > By my math even at a 10 trillion transactions a day > (10,000,000,000,000). it still takes 92,230 days(over 250 years) to > get past this number. > > As a note, my math says 10 trillion a day comes out to 115 million a > second. > > Am I missing something? Yes, Gumby is a no-brainer. -- }(:
On Feb 7, 11:18 am, ricew <willr...@cahoba.net> wrote: > On Feb 6, 10:24 am, Ian Michael Gumby <im_gu...@hotmail.com> wrote: > > > On Feb 6, 10:07 am, Clive Eisen <cl...@serendipita.com> wrote: > > > That's what serial8 is for > > > > It ranges to 9,223,372,036,854,775,807 which is probably enough for most > > > applications > > > I know. Its a large number. But I'm starting to see some applications > > where its possible to surpass that number. > > Remember this doesn't mean that the table has to hold that many rows > > since they can be purged, but the serial counter will continue to > > grow. > > I am curious what you define as where it is possible to surpass this > number. > > By my math even at a 10 trillion transactions a day > (10,000,000,000,000). it still takes 92,230 days(over 250 years) to > get past this number. > > As a note, my math says 10 trillion a day comes out to 115 million a > second. > > Am I missing something? > > Will As a note, please change transactions to records, as I am sure there will be some pendantic poster out there (though I know there are none on this board) who is going to bring up how it is quite possible to insert multiple records in one transaction. Will
>> >> Am I missing something? > > Yes, Gumby is a no-brainer. > With a small set of bells
Cute. I just walk silently and carry a small knotted rope. ;-) > From: markbtownsend@sbcglobal.net > Subject: Re: Rollover in Identity/Serial data types? > Date: Sat, 7 Feb 2009 13:34:44 -0800 > To: informix-list@iiug.org > > > >> > >> Am I missing something? > > > > Yes, Gumby is a no-brainer. > > > > With a small set of bells > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Windows Live™: E-mail. Chat. Share. Get more ways to connect. http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_HM_AE_Faster_022009