Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Andreas Schmitz
Date:
=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>

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

From
Ian Barwick
Date:
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

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

From
Andreas Schmitz
Date:
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>

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

From
Stephan Szabo
Date:
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.

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

From
Andreas Schmitz
Date:
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>

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

From
Bruno Wolff III
Date:
> 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.