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.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>
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
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=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.
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>
Import Notes
Reply to msg id not found: 20030517093707.M99346-100000@megazone23.bigpanda.comReference msg id not found: 20030517093707.M99346-100000@megazone23.bigpanda.com | Resolved by subject fallback
