Re: Duplicate serial in Informix 7.2.3
>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
On Jul 25, 11:25 pm, "Ian Michael Gumby" <im_gu...@hotmail.com> wrote:
> >From: "mark.scran...@gmail.com" <mark.scran...@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_m...
Gumby,
Mark is ABSOLUTELY correct. If you create a table with a serial
column using SQL (not the dbaccess menus) it will not have a UNIQUE
index not a UNIQUE or PRIMARY KEY constraint on it be default and
while inserting '0' to that column will always, as you say, insert the
next sequential number after the last, there is nothing stopping a
poorly written application or an interactive user from inserting '10'
to that column when there is already a row with that value. Try it.
The only attributes of a SERIAL column that differentiate it from an
INTEGER column is the behavior if you insert zero and the fact that
you cannot UPDATE the value of a SERIAL column. Period. It doesn't
even neccessarily prevent NULL values unless you explicitely add the
NOT NULL clause/constraint.
Someone noted that this questioner may be using the SELECT
MAX(serialcol) paradigm even in the WEB app. No. He states that he
inserts a zero. If he inserts using zero and fetches the serial
value using a SELECT from the table he might have his child table rows
associated with the wrong master record, but he's still always have
unique serial numbers. It has to be another poorly written app or
interactive user interfering with the one correctly written one by
inserting MAX(serialcol)+1 directly.
Art S. Kagel
Art,
Not to quibble over semantics, but Mark's argument is wrong.
Mark's argument is based on the premise that if you can "roll over" your
counter, then you're not guranteeing uniqueness. Its a specious argument.
First, this is way off topic from the OP.
(I was the one who said it was not a bug in Informix ...)
But since this came up...
You have to consider the forensics aspects to the argument.
The serial datatype was designed by Informix over 20 years ago.
(AFAIK its unique to Informix) In terms of today's designs, newer/younger
databases offer an identity column identifyer which is similar to a serial
data type.
An example: cloudscape/derby/javaDB ... see:
http://wiki.apache.org/db-derby/UniqueIdentityAndInserts?highlight=%28identity%29
The argument made by others is that you can't guarantee uniqueness by using
a serial column alone, and that you need to create a unique index that
doesn't allow nulls. (And to split hairs, its a combination of an index and
a constraint on the index... ;-)
My argument is that if you look back at the design, there are things in the
design to suggest that the intent was to guarantee uniqueness.
(The skipping of numbers in the sequence, the fact that DBACCESS forces you
to create the identity index which you can't change....) Then there's the
fact that the Alter table doesn't let you reset the serial column counter's
current value.
Informix did address the "rollover" issue that Mark points to by creating a
serial8 datatype.
2^64-1 is a very large number.
Now the reason Mark's argument is a specious argument is that he points to
the "rollover" issue. This issue is the same for databases which have
identity columns which can limit you to autogenerate only and not allow for
rows with values in the identity column to be inserted. They too suffer from
this "rollover".
Again this is way off topic and the point of my original post is that this
is *not* a defect in Informix.
-G
>From: "Art S. Kagel" <art.kagel@gmail.com>
>
>On Jul 25, 11:25 pm, "Ian Michael Gumby" <im_gu...@hotmail.com> wrote:
> > >From: "mark.scran...@gmail.com" <mark.scran...@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_m...
>
>Gumby,
>
>Mark is ABSOLUTELY correct. If you create a table with a serial
>column using SQL (not the dbaccess menus) it will not have a UNIQUE
>index not a UNIQUE or PRIMARY KEY constraint on it be default and
>while inserting '0' to that column will always, as you say, insert the
>next sequential number after the last, there is nothing stopping a
>poorly written application or an interactive user from inserting '10'
>to that column when there is already a row with that value. Try it.
>
>The only attributes of a SERIAL column that differentiate it from an@@NL@
On Jul 26, 11:44 am, "Ian Michael Gumby" <im_gu...@hotmail.com> wrote:
> Art,
>
> Not to quibble over semantics, but Mark's argument is wrong.
>
> Mark's argument is based on the premise that if you can "roll over" your
> counter, then you're not guranteeing uniqueness. Its a specious argument.
>
> First, this is way off topic from the OP.
> (I was the one who said it was not a bug in Informix ...)
>
> But since this came up...
>
> You have to consider the forensics aspects to the argument.
>
> The serial datatype was designed by Informix over 20 years ago.
> (AFAIK its unique to Informix) In terms of today's designs, newer/younger
> databases offer an identity column identifyer which is similar to a serial
> data type.
> An example: cloudscape/derby/javaDB ... see:http://wiki.apache.org/db-derby/UniqueIdentityAndInserts?highlight=%2...
>
> The argument made by others is that you can't guarantee uniqueness by using
> a serial column alone, and that you need to create a unique index that
> doesn't allow nulls. (And to split hairs, its a combination of an index and
> a constraint on the index... ;-)
>
> My argument is that if you look back at the design, there are things in the
> design to suggest that the intent was to guarantee uniqueness.
> (The skipping of numbers in the sequence, the fact that DBACCESS forces you
> to create the identity index which you can't change....) Then there's the
> fact that the Alter table doesn't let you reset the serial column counter's
> current value.
>
> Informix did address the "rollover" issue that Mark points to by creating a
> serial8 datatype.
> 2^64-1 is a very large number.
>
> Now the reason Mark's argument is a specious argument is that he points to
> the "rollover" issue. This issue is the same for databases which have
> identity columns which can limit you to autogenerate only and not allow for
> rows with values in the identity column to be inserted. They too suffer from
> this "rollover".
>
> Again this is way off topic and the point of my original post is that this
> is *not* a defect in Informix.
>
> -G
>
>
>
> >From: "Art S. Kagel" <art.ka...@gmail.com>
>
> >On Jul 25, 11:25 pm, "Ian Michael Gumby" <im_gu...@hotmail.com> wrote:
> > > >From: "mark.scran...@gmail.com" <mark.scran...@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_m...
>
> >Gumby,
>
> >Mark is ABSOLUTELY correct. If you create a table with a serial
> >column using SQL (not the dbaccess menus) it will not have a UNIQUE
> >index not a UNIQUE or PRIMARY KEY constraint on it be default and
> >while inserting '0' to that column will always, as you say, insert the
> >next sequ