Thread

1. UPDATE has a bug to update tables with an index of 2 columns

   Andreas Schmitz <andreas.schmitz@as-dataservice.de> — 2003-05-16T19:46:24Z

   ============================================================================
                           POSTGRESQL BUG REPORT TEMPLATE
   ============================================================================
   
   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=v2+2;
   ERROR:  Duplizierter Wert kann nicht in »Unique«-Index uq_test
           eingefügt 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=v2+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
   -- 
   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>
   
   
   
   
   
   
   
   
   

2. Re: UPDATE has a bug to update tables with an index of 2 columns

   Ian Barwick <barwick@gmx.net> — 2003-05-16T22:57:35Z

   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).
   
   A possible workaround would be to create a trigger which
   enforces the constraint.
   
   
   Ian Barwick
   barwick@gmx.net
   
   
   

3. Re: UPDATE has a bug to update tables with an index of 2 columns

   Andreas Schmitz <andreas.schmitz@as-dataservice.de> — 2003-05-17T07:38:10Z

   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>
   
    
   
   
   

4. Re: UPDATE has a bug to update tables with an index of 2

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-05-17T16:15:09Z

   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
   > >         eingefgt 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.