Re: Duplicate serial in Informix 7.2.3

On 26/07/07, Ian Michael Gumby <im_gumby@hotmail.com> wrote: > > > > >From: "mark.scranton@gmail.com" <mark.scranton@gmail.com> > > > >Uh guys....yes, Roy HAS nailed it. For G to reinforce that it will > >"*NEVER* ... (no duh!)" happen is absolutely untrue. Serial datatypes > >have NEVER been unique by default. Many, many clients (and apparently > >an IBMer or two) feel that they are...when traveling the US and world, > >I'd always ask this question openly to the crowd. The majority always > >felt that they were unique. I believe most of the time they didn't > >believe me, but....and, if you're not sure that I'm sure, just try it! > >It's easy enough to test. The serial value for a tablespace is stored > >in the partition page for that tablespace as the "Current value", > >which really means, the "next value to be used." If you wanna see some > >interesting stuff, play with seeding a negative value, then pass 0, > >etc...you might be surprised by what happens. Or maybe not. Try it! > > > >HTH - > >Mark Scranton > >Livin' the Farm Life (ok Clown, have at it...I'm sure this will > >generate some fodder from someone!) > > Gee Mark, > I'll admit I've never set up a serial column outside of dbaccess, so I've > always had a backing index. > (What can I say, I'm lazy... ;-) > > However... > > I think you need to stop drinking the well water and get it tested. > I believe the quote that you wanted to say was that while a serial datatype > guarantees uniqueness, it does not gurantee that you'll get your numbers in > order. > > Meaning you can see gaps in the pattern even if every row is entered using > the serial number generated by IDS. (Rollback!) > > If you look at a serial value, when you manually insert a row that has a > value greater than the current last serial value, the last serial value is > set to that number. This means that the next time you request a serial > number, you will get last serial value +1. > > If you read my post, you'll see the example of 1,2,3,4,5 manual insert > value =10 so the next generated serial value is 11. > > This means that you will get a unique value each time. > > If you are using a serial column, try inserting a row with the value of > MAXINT -1 which is the maxium size of an integer -1 or 2^n-1 where I think > n=32? Then continue to add rows. > > Since the serial value is an unsigned int (always positive), you'll see the > value wrap around. > After that occurs, all bets are off. > > There was a discussion about this in Cloudscape, when someone pulled out the > spec. This is true of any sequence generator. > > At IDUG, a certain IBMer suggested using a serial8 which is an 8 byte width > or 2^64-1 so you have a long time before that number rolls around. > > Now you said that serial values were not intended to be unique. Not true > Mark. If you think about it, they were intended to be unqiue because of the > way that they will skip to be the next largest value. That shows intent. > Also the fact that there can only be one serial datatype in a table, along > with the auto inclusion of a backing index when you use the tool to build > your database table, all kind of suggest intent. > > Now when you wrap a sequence number around, all bets of unqiueness are off > on *all* databases. > > If you happen to wrap your sequence around its max value, in theory, with a > b-tree index that doesn't rebalance, you should be able to determine the > next available open value and insert a record in that position. > > If the b-tree index does rebalance, I think its still possible to determine > the next open value, but it wouldn't be an easy algorithm. Not to mention > that you have a serial8 data type so why waste your time trying to determine > how to find an empty cell in a tree? Its a form of mental masturbation. > Meaning that a solution may exist, but its not worth the effort to try and > figure it out. Note: While I think it may be possible, others don't, and > they could be right. > > > But hey! what do I know? I never had access to the source code ... ;-) > > -G > > _________________________________________________________________ > http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migration_HM_mini_2G_0507 > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > However, it you then do a manual insert of 5 the serial will take it with no complaints, unless you have an unique index backing the serial column. This will still happens at 9.4 and I wouldn't have thought there would be any change to this action even at 11. Keith