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

Thu, 6 Apr 2000 19:30:37 -0500
Brian Hirt <bhirt@mobygames.com>
Unique Key Violation 7.0 vs. 6.5.3
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 Service and other goodies at
  http://donb.photo.net.

Thu, 06 Apr 2000 17:37:54 -0700
Don Baccus <dhogaza@pacifier.com>
Re: 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=> 

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

Thu, 6 Apr 2000 20:58:04 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Unique Key Violation 7.0 vs. 6.5.3

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

Thu, 6 Apr 2000 20:09:35 -0500
Brian Hirt <bhirt@mobygames.com>
Re: Unique Key Violation 7.0 vs. 6.5.3
> 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

Fri, 07 Apr 2000 10:23:43 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Unique Key Violation 7.0 vs. 6.5.3