Re: AW: ALTER FRAGMENT x UNIQUE CONSTRAINT

ahum, i guess i need to read the manual; having a unique constraint allows to do an update without setting constraints all deferred and the checking is done at commit time....???!! (921...) this may be incorrect too.... and if the alter fragment changes the unq constraint to an unq index then this is a bug for sure. may be this is related to having check constraints on a table in order to attach fragments without rebuilding tables???!!! dono. still a bug. Superboer. Model-Bosch, Tilman schreef: > > reitota@gmail.com > .. > > I have recently used ALTER FRAGMENT to move a table from one > > dbspace to > > another, and this table had a unique constraint defined in a specific > > column. After the move we began to experiment a curious IDS behaviour. > > The application updates this column like this: > > > > update id set id = id + 1 where id >= value;> > > ... > > Before the alter fragment this worked well, but after the operation we > > began to experiment unique constraint violations. Most strange is that > > this happens when we filter more than ten rows approximatelly. With > > less than this, there is no problem at all. Also if I drop the > > constraint and recreate it the problem goes away, but this is not > > acceptable for big tables. > > > > Another point is that this happens with the version I´m using 7.31 > > UD8, but also with versions 9.21, 9.40 and 10, as I´ve tested. I did > > not find any restriction to the alter fragment and constraints in the > > documentation up until now. > > > > Does anyone know if this is a bug with the alter fragment > > statement, or > > any restriction with alter fragment and constraints? > > > > I reproduced the error on a test table and found out that the internal > index which exists to implement the UNIQUE constraint has been modified > to a simple unique index (key flags). > So the statement will fail depending on whether the value 'id+1' already > exists in the table or not. > > The the UNIQUE constraint has been dropped from dbschema output , but is > still indicated as being active the system catalog tables. > > I did not find this behaviour documented anywhere > > So, I'd vote for bug :-/ > > Regards > Tilman > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list