UPDATE has a bug to update tables with an index of 2 columns - Mailing list pgsql-bugs

=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>