Thread: Unique Key Violation 7.0 vs. 6.5.3
Hi,
In doing some more 7.0 testing, I ran across a difference in functionality
concerning unique indexes and errors that are reported when you try to
violate the index. I'm not sure if this change is intentional, so I'm
bringing it up here. In 6.5.3, if you try to update a row that violates
a unique index, the query fails and said error is reported to the
application. However, in 7.0 the query succeeds, but updates 0 rows. Hence,
no errors are reported back to the application. This is not normally
a problem because I typically check the constrait before updating.
in 7.0/beta3
basement=> update foobar set unique_colum = '2000-04-09' where foobar_id = 32;
UPDATE 0
basement=>
in 6.5.3
basement=> update foobar set unique_colum = '2000-04-09' where foobar_id = 32;
ERROR: Cannot insert a duplicate key into a unique index
basement=>
--brian
--
The world's most ambitious and comprehensive PC game database project.
http://www.mobygames.com
At 07:30 PM 4/6/00 -0500, Brian Hirt wrote: > This is not normally >a problem because I typically check the constrait before updating. If true, this is actually a BIG problem since many applications will catch any error rather than check first, since this is more efficient... - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
> Hi, > > In doing some more 7.0 testing, I ran across a difference in functionality > concerning unique indexes and errors that are reported when you try to > violate the index. I'm not sure if this change is intentional, so I'm > bringing it up here. In 6.5.3, if you try to update a row that violates > a unique index, the query fails and said error is reported to the > application. However, in 7.0 the query succeeds, but updates 0 rows. Hence, > no errors are reported back to the application. This is not normally > a problem because I typically check the constrait before updating. > > > in 7.0/beta3 > basement=> update foobar set unique_colum = '2000-04-09' where foobar_id = 32; > UPDATE 0 > basement=> > > in 6.5.3 > basement=> update foobar set unique_colum = '2000-04-09' where foobar_id = 32; > ERROR: Cannot insert a duplicate key into a unique index > basement=> Works here: test=> insert into kk values (1);INSERT 18740 1test=> insert into kk values (1);ERROR: Cannot insert a duplicate key intounique index ii -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
It seems that I was a bit trigger happy with this one. I should have spent a bit more time researching this one. I'm not quite sure how I came to the conclusion I did. I re-ran my tests and everything works as it should. Sorry. --brian On Thu, Apr 06, 2000 at 08:58:04PM -0400, Bruce Momjian wrote: > > Hi, > > > > In doing some more 7.0 testing, I ran across a difference in functionality > > concerning unique indexes and errors that are reported when you try to > > violate the index. I'm not sure if this change is intentional, so I'm > > bringing it up here. In 6.5.3, if you try to update a row that violates > > a unique index, the query fails and said error is reported to the > > application. However, in 7.0 the query succeeds, but updates 0 rows. Hence, > > no errors are reported back to the application. This is not normally > > a problem because I typically check the constrait before updating. > > > > > > in 7.0/beta3 > > basement=> update foobar set unique_colum = '2000-04-09' where foobar_id = 32; > > UPDATE 0 > > basement=> > > > > in 6.5.3 > > basement=> update foobar set unique_colum = '2000-04-09' where foobar_id = 32; > > ERROR: Cannot insert a duplicate key into a unique index > > basement=> > > Works here: > > test=> insert into kk values (1); > INSERT 18740 1 > test=> insert into kk values (1); > ERROR: Cannot insert a duplicate key into unique index ii > > -- > Bruce Momjian | http://www.op.net/~candle > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 -- The world's most ambitious and comprehensive PC game database project. http://www.mobygames.com
> In doing some more 7.0 testing, I ran across a difference in functionality
> concerning unique indexes and errors that are reported when you try to
> violate the index. I'm not sure if this change is intentional, so I'm
> bringing it up here. In 6.5.3, if you try to update a row that violates
> a unique index, the query fails and said error is reported to the
> application. However, in 7.0 the query succeeds, but updates 0 rows. Hence,
> no errors are reported back to the application. This is not normally
> a problem because I typically check the constrait before updating.
>
>
> in 7.0/beta3
> basement=> update foobar set unique_colum = '2000-04-09' where foobar_id = 32;
> UPDATE 0
> basement=>
>
> in 6.5.3
> basement=> update foobar set unique_colum = '2000-04-09' where foobar_id = 32;
> ERROR: Cannot insert a duplicate key into a unique index
> basement=>
I'm not sure how your table looks like, but seems following test with
current (not b3) works here:
test=# create table foobar (unique_column date unique, foobar_id int primary key);
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'foobar_unique_column_key' for table 'foobar'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'foobar_pkey' for table 'foobar'
CREATE
test=# insert into foobar values('2000-4-8', 32);
INSERT 2231126 1
test=# insert into foobar values('2000-4-9', 33);
INSERT 2231127 1
test=# update foobar set unique_column = '2000-04-09' where foobar_id = 32;
ERROR: Cannot insert a duplicate key into unique index foobar_unique_column_key
--
Tatsuo Ishii