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

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Andreas Schmitz
Your email address : andreas(dot)schmitz(at)as-dataservice(dot)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(dot)schmitz(at)as-dataservice(dot)de
<mailto:andreas(dot)schmitz(at)as-dataservice(dot)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=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(at)gmx(dot)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(dot)schmitz(at)as-dataservice(dot)de
<mailto:andreas(dot)schmitz(at)as-dataservice(dot)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=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.