alter Table modify serial did not work
Andre found that ALTER TABLE ... MODIFY (snr SERIAL(1)) on IDS 11.5 silently failed to reset the counter — after deleting all rows, the next insert still produced ~280091. Art Kagel explained this is by design: a serial value can only be raised, never lowered. The workaround is to set it to MAXINT (2147483647), insert one row and delete it, so numbering wraps back to 1 (provided no conflicting unique key). Others suggested drop/recreate, a TRUNCATE ... WITH SERIAL RESET feature request, and John Miller gave a sysptnhdr query to view current serial values.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all,
I have a strange problem. Using IDS 11.5 the following command did not
work as expected:
alter table prj modify (snr serial(1));The command was processed without any problem, but the next insert did
not generate 1 (or even 2) for the next "snr", but it generates
280091. I have completely deleted all the entries (rows) from the table,
so there should be no problem for informix starting with 1
for the next serial "snr". There is nothing special with snr (its a
primary key and a serial, we often use a variable named snr)
Whats going wrong, I have not found any information, the modify ...
serial is simply ignored in every case we hav tested.
Kind regards
Andre
--
* Andre Koppel Software GmbH *
* Prinz-Handjery-Str. 38 *
* 14167 Berlin *
* Tel.: (+4930) 810 09 190 *
* Fax: (+4930) 326 01 046 *
* www.invep.de *
* www.akso.de *
Eingetragen beim Amtsgericht
Berlin Charlottenburg HRB92600
Geschäftsführer Andre Koppel
On Tue, Sep 21, 2010 at 2:17 PM, Andre Koppel <akoppel@akso.de> wrote:
> Hi all,
> I have a strange problem. Using IDS 11.5 the following command did not
> work as expected:
> alter table prj modify (snr serial(1));> The command was processed without any problem, but the next insert did
> not generate 1 (or even 2) for the next "snr", but it generates
> 280091. I have completely deleted all the entries (rows) from the table,
> so there should be no problem for informix starting with 1
> for the next serial "snr". There is nothing special with snr (its a
> primary key and a serial, we often use a variable named snr)
> Whats going wrong, I have not found any information, the modify ...
> serial is simply ignored in every case we hav tested.
> Kind regards
> Andre
>
Can you provide a test case? Something that includes the table schema, the
ALTER TABLE command, the INSERT(s) and the result verification?
And the exact versions also please...
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0016e64bb7905590260490c4fae2
You cannot set the serial value down only up. To restart it from 1 you
have to set it to MAXINT (2147483647) then insert one row and delete it.
The following row will start over at 1 assuming nothing else prevents that
(like a unique index/constraint on the serial column and a '1' already
exists).
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Tue, Sep 21, 2010 at 9:17 AM, Andre Koppel <akoppel@akso.de> wrote:
> Hi all,
> I have a strange problem. Using IDS 11.5 the following command did not
> work as expected:
> alter table prj modify (snr serial(1));> The command was processed without any problem, but the next insert did
> not generate 1 (or even 2) for the next "snr", but it generates
> 280091. I have completely deleted all the entries (rows) from the table,
> so there should be no problem for informix starting with 1
> for the next serial "snr". There is nothing special with snr (its a
> primary key and a serial, we often use a variable named snr)
> Whats going wrong, I have not found any information, the modify ...
> serial is simply ignored in every case we hav tested.
> Kind regards
> Andre
>
> --
> * Andre Koppel Software GmbH *
> * Prinz-Handjery-Str. 38 *
> * 14167 Berlin *
> * Tel.: (+4930) 810 09 190 *
> * Fax: (+4930) 326 01 046 *
> * www.invep.de *
> * www.akso.de *
>
> Eingetragen beim Amtsgericht
> Berlin Charlottenburg HRB92600
> Geschäftsführer Andre Koppel
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016e6509b5279a96c0490c53496
Fernando, this a Works As Designed. It's always been this way.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Tue, Sep 21, 2010 at 9:26 AM, Fernando Nunes <domusonline@gmail.com>wrote:
> On Tue, Sep 21, 2010 at 2:17 PM, Andre Koppel <akoppel@akso.de> wrote:
>
> > Hi all,
> > I have a strange problem. Using IDS 11.5 the following command did not
> > work as expected:
> > alter table prj modify (snr serial(1));> > The command was processed without any problem, but the next insert did
> > not generate 1 (or even 2) for the next "snr", but it generates
> > 280091. I have completely deleted all the entries (rows) from the table,
> > so there should be no problem for informix starting with 1
> > for the next serial "snr". There is nothing special with snr (its a
> > primary key and a serial, we often use a variable named snr)
> > Whats going wrong, I have not found any information, the modify ...
> > serial is simply ignored in every case we hav tested.
> > Kind regards
> > Andre
> >
>
> Can you provide a test case? Something that includes the table schema, the
> ALTER TABLE command, the INSERT(s) and the result verification?
> And the exact versions also please...>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --0016e64bb7905590260490c4fae2
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00163630f45502418b0490c5396f
True!. I forgot about that and how it annoys me :)
Regards and thanks!
On Tue, Sep 21, 2010 at 2:44 PM, Art Kagel <art.kagel@gmail.com> wrote:
> Fernando, this a Works As Designed. It's always been this way.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Tue, Sep 21, 2010 at 9:26 AM, Fernando Nunes <domusonline@gmail.com
> >wrote:
>
> > On Tue, Sep 21, 2010 at 2:17 PM, Andre Koppel <akoppel@akso.de> wrote:
> >
> > > Hi all,
> > > I have a strange problem. Using IDS 11.5 the following command did not
> > > work as expected:
> > > alter table prj modify (snr serial(1));> > > The command was processed without any problem, but the next insert did
> > > not generate 1 (or even 2) for the next "snr", but it generates
> > > 280091. I have completely deleted all the entries (rows) from the
> table,
> > > so there should be no problem for informix starting with 1
> > > for the next serial "snr". There is nothing special with snr (its a
> > > primary key and a serial, we often use a variable named snr)
> > > Whats going wrong, I have not found any information, the modify ...
> > > serial is simply ignored in every case we hav tested.
> > > Kind regards
> > > Andre
> > >
> >
> > Can you provide a test case? Something that includes the table schema,
> the
> > ALTER TABLE command, the INSERT(s) and the result verification?
> > And the exact versions also please...> >
> > --
> > Fernando Nunes
> > Portugal
> >
> > http://informix-technology.blogspot.com
> > My email works... but I don't check it frequently...
> >
> > --0016e64bb7905590260490c4fae2
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --00163630f45502418b0490c5396f
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0015175ce07a9a00040490c566ab
Hi Art,
you are right, it works this way. But I have have some old code (2003 or
so) that has reset the serial value
after all of the rows in a table are deleted. I am absolutely sure that
the code has worked long time ago.
So something has changed within the past. For me it did not make sense
that it is not alowed to reset a serial
even if the table is empty.
--
Am 21.09.2010 15:42, schrieb Art Kagel:
> You cannot set the serial value down only up. To restart it from 1 you
> have to set it to MAXINT (2147483647) then insert one row and delete it.
> The following row will start over at 1 assuming nothing else prevents that
> (like a unique index/constraint on the serial column and a '1' already
> exists).
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Tue, Sep 21, 2010 at 9:17 AM, Andre Koppel<akoppel@akso.de> wrote:
>
>> Hi all,
>> I have a strange problem. Using IDS 11.5 the following command did not
>> work as expected:
>> alter table prj modify (snr serial(1));>> The command was processed without any problem, but the next insert did
>> not generate 1 (or even 2) for the next "snr", but it generates
>> 280091. I have completely deleted all the entries (rows) from the table,
>> so there should be no problem for informix starting with 1
>> for the next serial "snr". There is nothing special with snr (its a
>> primary key and a serial, we often use a variable named snr)
>> Whats going wrong, I have not found any information, the modify ...
>> serial is simply ignored in every case we hav tested.
>> Kind regards
>> Andre
>>
>> --
>> * Andre Koppel Software GmbH *
>> * Prinz-Handjery-Str. 38 *
>> * 14167 Berlin *
>> * Tel.: (+4930) 810 09 190 *
>> * Fax: (+4930) 326 01 046 *
>> * www.invep.de *
>> * www.akso.de *
>>
>> Eingetragen beim Amtsgericht
>> Berlin Charlottenburg HRB92600
>> Geschäftsführer Andre Koppel
>>
>>
>>
>>
>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
> --0016e6509b5279a96c0490c53496
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Andre
Perhaps your code does a drop/create, that would delete all the data and
reset the serial.
Keith
On 21/09/2010, Andre Koppel <akoppel@akso.de> wrote:
> Hi Art,
> you are right, it works this way. But I have have some old code (2003 or
> so) that has reset the serial value
> after all of the rows in a table are deleted. I am absolutely sure that
> the code has worked long time ago.
> So something has changed within the past. For me it did not make sense
> that it is not alowed to reset a serial
> even if the table is empty.
> --
>
> Am 21.09.2010 15:42, schrieb Art Kagel:
> > You cannot set the serial value down only up. To restart it from 1 you
> > have to set it to MAXINT (2147483647) then insert one row and delete it.
> > The following row will start over at 1 assuming nothing else prevents that
> > (like a unique index/constraint on the serial column and a '1' already
> > exists).
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
and
> > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> > organization with which I am associated either explicitly, implicitly, or
by
> > inference. Neither do those opinions reflect those of other individuals
> > affiliated with any entity with which I am affiliated nor those of the
> > entities themselves.
> >
> > On Tue, Sep 21, 2010 at 9:17 AM, Andre Koppel<akoppel@akso.de> wrote:
> >
> >> Hi all,
> >> I have a strange problem. Using IDS 11.5 the following command did not
> >> work as expected:
> >> alter table prj modify (snr serial(1));> >> The command was processed without any problem, but the next insert did
> >> not generate 1 (or even 2) for the next "snr", but it generates
> >> 280091. I have completely deleted all the entries (rows) from the table,
> >> so there should be no problem for informix starting with 1
> >> for the next serial "snr". There is nothing special with snr (its a
> >> primary key and a serial, we often use a variable named snr)
> >> Whats going wrong, I have not found any information, the modify ...
> >> serial is simply ignored in every case we hav tested.
> >> Kind regards
> >> Andre
> >>
> >> --
> >> * Andre Koppel Software GmbH *
> >> * Prinz-Handjery-Str. 38 *
> >> * 14167 Berlin *
> >> * Tel.: (+4930) 810 09 190 *
> >> * Fax: (+4930) 326 01 046 *
> >> * www.invep.de *
> >> * www.akso.de *
> >>
> >> Eingetragen beim Amtsgericht
> >> Berlin Charlottenburg HRB92600
> >> Geschäftsführer Andre Koppel
> >>
> >>
> >>
> >>
> >
>
*******************************************************************************
> >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>
> >>
> > --0016e6509b5279a96c0490c53496
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
It's been that way since I started using serials about 10-12 years ago. Maybe
you could log a feature request for something like this:
TRUNCATE TABLE my_table WITH SERIAL RESET
So that when a table is truncated the next serial values are reset to 1. It
should be pretty easy to implement, if there's enough call for it. It would
also be a way to provide compatibility with other databases, Squeal for one,
that always reset serials when tables are truncated.
--EEM
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Andre Koppel
> Sent: Tuesday, September 21, 2010 9:00 AM
> To: ids@iiug.org
> Subject: Re: alter Table modify serial did not work [21391]
>
> Hi Art,
> you are right, it works this way. But I have have some old code (2003
> or
> so) that has reset the serial value
> after all of the rows in a table are deleted. I am absolutely sure that
> the code has worked long time ago.
> So something has changed within the past. For me it did not make sense
> that it is not alowed to reset a serial
> even if the table is empty.
> --
>
> Am 21.09.2010 15:42, schrieb Art Kagel:
> > You cannot set the serial value down only up. To restart it from 1
> you
> > have to set it to MAXINT (2147483647) then insert one row and delete
> it.
> > The following row will start over at 1 assuming nothing else prevents
> that
> > (like a unique index/constraint on the serial column and a '1'
> already
> > exists).
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
> opinions and
> > do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other
> > organization with which I am associated either explicitly,
> implicitly, or by
> > inference. Neither do those opinions reflect those of other
> individuals
> > affiliated with any entity with which I am affiliated nor those of
> the
> > entities themselves.
> >
> > On Tue, Sep 21, 2010 at 9:17 AM, Andre Koppel<akoppel@akso.de> wrote:
> >
> >> Hi all,
> >> I have a strange problem. Using IDS 11.5 the following command did
> not
> >> work as expected:
> >> alter table prj modify (snr serial(1));> >> The command was processed without any problem, but the next insert
> did
> >> not generate 1 (or even 2) for the next "snr", but it generates
> >> 280091. I have completely deleted all the entries (rows) from the
> table,
> >> so there should be no problem for informix starting with 1
> >> for the next serial "snr". There is nothing special with snr (its a
> >> primary key and a serial, we often use a variable named snr)
> >> Whats going wrong, I have not found any information, the modify ...
> >> serial is simply ignored in every case we hav tested.
> >> Kind regards
> >> Andre
> >>
> >> --
> >> * Andre Koppel Software GmbH *
> >> * Prinz-Handjery-Str. 38 *
> >> * 14167 Berlin *
> >> * Tel.: (+4930) 810 09 190 *
> >> * Fax: (+4930) 326 01 046 *
> >> * www.invep.de *
> >> * www.akso.de *
> >>
> >> Eingetragen beim Amtsgericht
> >> Berlin Charlottenburg HRB92600
> >> Geschäftsführer Andre Koppel
> >>
> >>
> >>
> >>
> >
> ***********************************************************************
> ********
> >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>
> >>
> > --0016e6509b5279a96c0490c53496
> >
> >
> >
> ***********************************************************************
> ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
Here is a way of peaking at the serials columns current value.
select trim(dbsname)||'.'||trim(tabname) as tabname,
serialv as serial, cur_serial8 as serial8, cur_bigserial as big_serial
from sysptnhdr P, systabnames T
where P.partnum =3D T.partnum
and P.partnum =3D P.lockid
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 09/21/2010 07:20:36 AM:
> [image removed]
>
> RE: alter Table modify serial did not work [21393]
>
> Everett Mills
>
> to:
>
> ids
>
> 09/21/2010 07:21 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> It's been that way since I started using serials about 10-12 years ag=
o.
Maybe
> you could log a feature request for something like this:
>
> TRUNCATE TABLE my_table WITH SERIAL RESET
>
> So that when a table is truncated the next serial values are reset to=
1.
It
> should be pretty easy to implement, if there's enough call for it. It=
would
> also be a way to provide compatibility with other databases, Squeal f=
or
one,
> that always reset serials when tables are truncated.
>
> --EEM
>
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf =
Of
> > Andre Koppel
> > Sent: Tuesday, September 21, 2010 9:00 AM
> > To: ids@iiug.org
> > Subject: Re: alter Table modify serial did not work [21391]
> >
> > Hi Art,
> > you are right, it works this way. But I have have some old code (20=
03
> > or
> > so) that has reset the serial value
> > after all of the rows in a table are deleted. I am absolutely sure =
that
> > the code has worked long time ago.
> > So something has changed within the past. For me it did not make se=
nse
> > that it is not alowed to reset a serial
> > even if the table is empty.
> > --
> >
> > Am 21.09.2010 15:42, schrieb Art Kagel:
> > > You cannot set the serial value down only up. To restart it from =
1
> > you
> > > have to set it to MAXINT (2147483647) then insert one row and del=
ete
> > it.
> > > The following row will start over at 1 assuming nothing else prev=
ents
> > that
> > > (like a unique index/constraint on the serial column and a '1'
> > already
> > > exists).
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > IIUG Board of Directors (art@iiug.org)
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
> > opinions and
> > > do not reflect on my employer, Advanced DataTools, the IIUG, nor =
any
> > other
> > > organization with which I am associated either explicitly,
> > implicitly, or by
> > > inference. Neither do those opinions reflect those of other
> > individuals
> > > affiliated with any entity with which I am affiliated nor those o=
f
> > the
> > > entities themselves.
> > >
> > > On Tue, Sep 21, 2010 at 9:17 AM, Andre Koppel<akoppel@akso.de> wr=
ote:
> > >
> > >> Hi all,
> > >> I have a strange problem. Using IDS 11.5 the following command d=
id
> > not
> > >> work as expected:
> > >> alter table prj modify (snr serial(1));> > >> The command was processed without any problem, but the next inse=
rt
> > did
> > >> not generate 1 (or even 2) for the next "snr", but it generates
> > >> 280091. I have completely deleted all the entries (rows) from th=
e
> > table,
> > >> so there should be no problem for informix starting with 1
> > >> for the next serial "snr". There is nothing special with snr (it=
s a
> > >> primary key and a serial, we often use a variable named snr)
> > >> Whats going wrong, I have not found any information, the modify =
...
> > >> serial is simply ignored in every case we hav tested.
> > >> Kind regards
> > >> Andre
> > >>
> > >> --
> > >> * Andre Koppel Software GmbH *
> > >> * Prinz-Handjery-Str. 38 *
> > >> * 14167 Berlin *
> > >> * Tel.: (+4930) 810 09 190 *
> > >> * Fax: (+4930) 326 01 046 *
> > >> * www.invep.de *
> > >> * www.akso.de *
> > >>
> > >> Eingetragen beim Amtsgericht
> > >> Berlin Charlottenburg HRB92600
> > >> Gesch=E4ftsf=FChrer Andre Koppel
> > >>
> > >>
> > >>
> > >>
> > >
> > *******************************************************************=
****
> > ********
> > >> Forum Note: Use "Reply" to post a response in the discussion for=
um.
> > >>
> > >>
> > > --0016e6509b5279a96c0490c53496
> > >
> > >
> > >
> > *******************************************************************=
****
> > ********
> > > Forum Note: Use "Reply" to post a response in the discussion foru=
m.
> > >
> >
> >
> > *******************************************************************=
****
> > ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.=
>
>
>
***********************************************************************=
********
> Forum Note: Use "Reply" to post a response in the discussion forum.=
>=