ALTER FRAGMENT x UNIQUE CONSTRAINT
A user moved a table to another dbspace with ALTER FRAGMENT; afterwards an 'update tab set id = id + 1 where id >= value' on a uniquely-constrained column began raising unique-constraint violations (typically once more than ~10 rows were affected), though dropping and recreating the constraint cured it. Art Kagel argued it was luck/row ordering and that checking is only truly deferred with SET CONSTRAINTS ALL DEFERRED; the poster countered that logged databases check at statement end. Another user reproduced it, reporting the constraint's index appeared changed to a plain unique index (constraint gone from dbschema but still in the catalogs), and called it a bug. No fix or official answer is recorded beyond recreating the constraint.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello All.
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;
where column id is the column with the unique constraint defined.
Of course I don´t like this solution but, as we know, deferred
constraint allow this kind of construction, as constraint violations
are checked after the statement finishes, and then no violation occurs.
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?
Thanks in advance.
rdtbra
reitota@gmail.com wrote:
> Hello All.
No bug, I think, rather, that before the ALTER FRAGMENT the rows were being
processed in a different order than before so you just lucked out.
Are you EXPLICITELY setting constraints to defer or assuming that such are
deferred to the completion of the statement? If you are depending on this
behavior implicitely you have just been very lucky so far. Constraints are
checked row-by-row unless you:
BEGIN WORK;
SET CONSTRAINTS ALL DEFERRED;
udpate....
COMMIT WORK;
Then the constraint checking is deferred until the COMMIT statement.
Art S. Kagel
> 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;>
> where column id is the column with the unique constraint defined.
>
> Of course I don't like this solution but, as we know, deferred
> constraint allow this kind of construction, as constraint violations
> are checked after the statement finishes, and then no violation occurs.
>
> 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?
>
> Thanks in advance.
>
> rdtbra
>
Art,
You are correct in the DEFERRED behaviour. I did not pay attention when
I was writing as I should mean IMMEDIATE.
But you see, when using databases with log, constraints are checked
IMMEDIATE. But this kind of checking is done after statement completion
and not on a row basis, as you stated. Only with databases without log,
constraints are checked on a row basis, and my database has unbuffered
log active.
So I still see it as a bug.
Am I correct?
Thank you.
rdtbra
Art S. Kagel wrote:
> reitota@gmail.com wrote:
> > Hello All.
>
>
> No bug, I think, rather, that before the ALTER FRAGMENT the rows were being
> processed in a different order than before so you just lucked out.
>
> Are you EXPLICITELY setting constraints to defer or assuming that such are
> deferred to the completion of the statement? If you are depending on this
> behavior implicitely you have just been very lucky so far. Constraints are
> checked row-by-row unless you:
>
> BEGIN WORK;
> SET CONSTRAINTS ALL DEFERRED;
> udpate....
> COMMIT WORK;
>
> Then the constraint checking is deferred until the COMMIT statement.
>
> Art S. Kagel
>
> > 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;> >
> > where column id is the column with the unique constraint defined.
> >
> > Of course I don´t like this solution but, as we know, deferred
> > constraint allow this kind of construction, as constraint violations
> > are checked after the statement finishes, and then no violation occurs.
> >
> > 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?
> >
> > Thanks in advance.
> >
> > rdtbra
> >
> > So I still see it as a bug. > > Am I correct? Nope, Art is correct. Superboer.
> 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
> 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
What do you mean the flags changed,what flags, where?
A unique constraint implies a unique index to validate the uniqueness
efficiently.
What is a simple unique index? As opposed to what?
It was a unique index and now it is a unique index what is the
difference?
Model-Bosch, Tilman wrote:
>
> 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