Обсуждение: UPDATE has a bug to update tables with an index of 2 columns
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D POSTGRESQL BUG REPORT TEMPLATE =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D Your name : Andreas Schmitz Your email address : andreas.schmitz@as-dataservice.de System Configuration --------------------- Architecture (example: Intel Pentium) : Intel Pentium III Operating System (example: Linux 2.0.26 ELF) : Linux 2.4.19 PostgreSQL version (example: PostgreSQL-7.3.2): PostgreSQL-7.3.2 Compiler used (example: gcc 2.95.2) : gcc 2.95.3 Please enter a FULL description of your problem: ------------------------------------------------ Short: UPDATE has a bug to update tables with an index of 2 columns Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: ---------------------------------------------------------------------- First, you create a table test2 as this one: create table test2 ( v1 int4, v2 int4, CONSTRAINT uq_test UNIQUE (v1,v2) ); Now, you inserting some data: insert into test2 values (0,0); insert into test2 values (0,1); insert into test2 values (0,2); Now, you make the following update: update test2 set v2=3Dv2+2; ERROR: Duplizierter Wert kann nicht in =BBUnique=AB-Index uq_test eingef=FCgt werden This means, that the value is duplicated, but this is wrong, because the statement add to every value 2 and should check the constraints at last. If I drop the unique index with: alter table test2 drop constraint uq_test; .. and do the same update: update test2 set v2=3Dv2+2; and then recreate the unique constraint with: alter table test2 add constraint uq_test UNIQUE (v1,v2); it works fine! If you know how this problem might be fixed, list the solution below: --------------------------------------------------------------------- Best regards --=20 Andreas Schmitz AS-DataService <http://www.as-dataservice.de> Kastanienallee 24 D-54662 Speicher Tel.: (0 65 62) 93 05 17 Fax: (0 65 62) 93 05 18 Email: andreas.schmitz@as-dataservice.de <mailto:andreas.schmitz@as-dataservice.de> Ust-IdNr.: DE211466407 Handelsregister: HRA 1869 - Amtsgericht Bitburg <http://www.as-dataservice.de>
On Friday 16 May 2003 21:46, Andreas Schmitz wrote: > Please enter a FULL description of your problem: > ------------------------------------------------ > > Short: UPDATE has a bug to update tables with an index of 2 columns (...) > update test2 set v2=3Dv2+2; > ERROR: Duplizierter Wert kann nicht in =BBUnique=AB-Index uq_test > eingef=FCgt werden -> "Cannot insert a duplicate key into unique index uq_test" I would contend this is not a bug but a feature (or at most not-yet-implemented functionality, i.e. no ability to defer constraints other than foreign keys). A possible workaround would be to create a trigger which enforces the constraint. Ian Barwick barwick@gmx.net
Ian Barwick wrote: >I would contend this is not a bug but a feature (or at most >not-yet-implemented functionality, i.e. no ability to defer >constraints other than foreign keys). > > Oh, I don't think so, because when I use some complex WHERE clause the statements don't use the index uq_test (EXPLAIN...) and it works find. This Statement works also fine: update test2 set v1=0 where v1=0; And therefor, I think this is a bug. >A possible workaround would be to create a trigger which >enforces the constraint. > > What do you mean with enforces the constraint? Sorry, I can't follow you. Can you describe, how I enforce the Constraint? Thanx a lot. Best regards -- Andreas Schmitz AS-DataService <http://www.as-dataservice.de> Kastanienallee 24 D-54662 Speicher Tel.: (0 65 62) 93 05 17 Fax: (0 65 62) 93 05 18 Email: andreas.schmitz@as-dataservice.de <mailto:andreas.schmitz@as-dataservice.de> Ust-IdNr.: DE211466407 Handelsregister: HRA 1869 - Amtsgericht Bitburg <http://www.as-dataservice.de>
On Sat, 17 May 2003, Ian Barwick wrote: > On Friday 16 May 2003 21:46, Andreas Schmitz wrote: > > > Please enter a FULL description of your problem: > > ------------------------------------------------ > > > > Short: UPDATE has a bug to update tables with an index of 2 columns > (...) > > update test2 set v2=3Dv2+2; > > ERROR: Duplizierter Wert kann nicht in =BBUnique=AB-Index uq_test > > eingef=FCgt werden > > -> "Cannot insert a duplicate key into unique index uq_test" > > I would contend this is not a bug but a feature (or at most > not-yet-implemented functionality, i.e. no ability to defer > constraints other than foreign keys). IIRC, it's a known bug. The constraint is checked at the wrong time, even for non-deferred unique constraints the check is supposed to happen after all the rows have been changed not as the rows are being changed.
Stephan Szabo wrote: >On Sat, 17 May 2003, Andreas Schmitz wrote: > > > >>Stephan Szabo wrote: >> >> >> >>>On Sat, 17 May 2003, Ian Barwick wrote: >>> >>> >>> >>> >>> >>>>On Friday 16 May 2003 21:46, Andreas Schmitz wrote: >>>> >>>> >>>> >>>> >>>> >>>>>Please enter a FULL description of your problem: >>>>>------------------------------------------------ >>>>> >>>>>Short: UPDATE has a bug to update tables with an index of 2 columns >>>>> >>>>> >>>>> >>>>> >>>>(...) >>>> >>>> >>>> >>>> >>>>>update test2 set v2=v2+2; >>>>>ERROR: Duplizierter Wert kann nicht in ?Unique?-Index uq_test >>>>> eingef?gt werden >>>>> >>>>> >>>>> >>>>> >>>>-> "Cannot insert a duplicate key into unique index uq_test" >>>> >>>>I would contend this is not a bug but a feature (or at most >>>>not-yet-implemented functionality, i.e. no ability to defer >>>>constraints other than foreign keys). >>>> >>>> >>>> >>>> >>>IIRC, it's a known bug. The constraint is checked at the wrong time, even >>>for non-deferred unique constraints the check is supposed to happen after >>>all the rows have been changed not as the rows are being changed. >>> >>> > >No. It's going to take some infrastructure work to change. Unless someone >with a clue about the index code and time works on it, I wouldn't expect >it for 7.4. Fundamentally the issue is that you'd have to make changes to >the index code to allow duplicates in unique indexes, provide some way to >check at statement end to make sure the duplicates have been resolved and >store the information necessary to do so (since you wouldn't want to walk >the entire index in general) or some other mechanism with the same final >result. > > Oh, this is not so beautifull. Is there a way to work around without dropping the unique index? Perhaps disabling the index, but only for the current session? Or any other ideas or do you approximately know the releasedate of 7.4? Thanx a lot for your Help. Best regards -- Andreas Schmitz AS-DataService <http://www.as-dataservice.de> Kastanienallee 24 D-54662 Speicher Tel.: (0 65 62) 93 05 17 Fax: (0 65 62) 93 05 18 Email: andreas.schmitz@as-dataservice.de <mailto:andreas.schmitz@as-dataservice.de> Ust-IdNr.: DE211466407 Handelsregister: HRA 1869 - Amtsgericht Bitburg <http://www.as-dataservice.de>
> Or any other ideas or do you approximately know the releasedate of 7.4? A 7.4 beta release is supposed to be made on July 1. It will probably be September when the production version is released.