AW: Sync serial value after manually inserted records

Hi,

There were older versions with some problems, for example some alter tables 
causing much internal work incl. re-creating the table internally, which caused 
forgetting the newest value for serial. Therefore please use the newest version 
available.
Or the reason could have been a loading with some option causing the 
fast-loading (with pre-build records/pages). Then, in older versions the 
highest inserted serial-value was forgotten.
Or the case may be that after dropping the default serial and re-creating it 
with a higher value the cache did not catch this newer, higher value and used 
those values cached before the default-drop. Then some inserts (eating up the 
cache) and some deletes by hand will help.

With versions > 7.4.3.26 (or the newest 7.5-version) no such problem should 
occur any more. Which version do you use?

Elke
SAP Labs berlin

> -----Ursprüngliche Nachricht-----
> Von: Erik Rehrmann [mailto:rehrmann@xxxxxx]
> Gesendet: Montag, 11. April 2005 16:41
> An: Zabach, Elke
> Betreff: RE: Sync serial value after manually inserted records
> 
> Hi Elke,
> 
> you're right! It worked the way you described:
> 
> INSERT (serial,...) values (1000,...)
> INSERT (...) values (...)
> CURRVAL=1001
> 
> But: All records in this table are initially inserted this way (manually),
> highest ID = 862 with some gaps.
> Why did the number generator produce a new serial value of (9) ???
> 
> I do not understand the system behind and that's why I'm doubtfully think
> it can happen again!
> 
> Is it necessary to ask the CURRVAL afer auto-inserting?
> 
> --
> Erik Rehrmann
> IOn AG
> Vorstand, Forschung & Entwicklung
> Board Member, R&D
> http://www.ion.ag
> http://ionic.ion.ag
> GnuPG Fingerprint: FA40 4DA8 F27B B4F6 FBA2 3455 0452 0C54 D111 546A
> 
> 
> > -----Original Message-----
> > From: Zabach, Elke [mailto:elke.zabach@xxxxxxx]
> > Sent: Monday, April 11, 2005 4:02 PM
> > To: Erik Rehrmann; maxdb@xxxxxxxxxxxxxxx
> > Subject: AW: Sync serial value after manually inserted records
> >
> > Erik Rehrmann [mailto:rehrmann@xxxxxx]
> > > Gesendet: Freitag, 8. April 2005 12:47
> > > An: maxdb@xxxxxxxxxxxxxxx
> > > Betreff: Sync serial value after manually inserted records
> > >
> > > Hi all,
> > >
> > > I've encountered a very serious problem using serials.
> > >
> > > A MaxDB is first filled via an ETL-process as a copy of a different
> > DBMS,
> > > i.e. all tables containing serial columns are filled not by
> > the MaxDB
> > > number generator, but by a manual insert.
> > >
> > > The source database is already used and, because of some normal
> > deletions,
> > > the serial ids are not subsequent.
> > >
> > > Using the new filled MaxDB our application gets lots of
> > "Duplicate key
> > > error", because the next generated serial id is already in
> > use within
> > the
> > > table.
> > >
> > > So here are the questions:
> > >
> > > +) Is it possible to synchronize the max value of a serial with the
> > > highest value of the serial id within the table?
> > >
> > > +) Is there any way, maybe as dba, to change the current
> > serial value
> > > within the cataloge?
> > >
> > > +) Is there any other way to use serials for tables wich values can
> > also
> > > be manually inserted?
> >
> > Assuming a table t1 with serial column s, having the current
> > value of 1000, You can 'set' the serial value using this:
> >
> > Select max(s) from t1  --> finding the 1000 Insert t1 (s,<all
> > other mandatory columns>) values (1001, ....) Delete t1 where s = 1001
> >
> > Meaning: if a serial-column's value is given by hand and
> > greater than the current serial value the user-given value
> > will be used as new max value, meaning the next implicit
> > serial value will be 1 greater then the user-given one.
> >
> > If you always have a mixture of user-given values (sometime
> > being smaller than the max serial value), then you always
> > cause the possibility of duplicate keys.
> > Then using serial column is no good decision.
> > For a first reset after some filling from somewhere the
> > method given above will help.
> >
> > Elke
> > SAP Labs Berlin
> >
> > >
> > > Environment is
> > > - Linux / WindowsXP SP2
> > > - MaxDB version: 7.5.00.18 build 018-121-079-776
> > > - JDBC-driver-version: 7.6.0   build 000-000-003-264
> > >
> > > Different combinations of other (newer/older) versions are
> > also tested
> > > without success.
> > >
> > > Any help is greatly appreciate.
> > >
> > > --
> > > Erik Rehrmann
> > >
> > > --
> > > MaxDB Discussion Mailing List
> > > For list archives: http://lists.mysql.com/maxdb To unsubscribe:
> > http://lists.mysql.com/maxdb?unsub=elke.zabach@xxxxxxx
> >

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx