update inside transaction violates unique constraint? - Mailing list pgsql-general

From Andrew Sullivan
Subject update inside transaction violates unique constraint?
Date
Msg-id 20000926164302.G22812@bpl.on.ca
Whole thread Raw
Responses Re: update inside transaction violates unique constraint?  (Andrew Sullivan <sullivana@bpl.on.ca>)
List pgsql-general
Hi,

I've probably just overlooked something, but a (quick-ish, I admit)
look at the ML archive, my own memory, and the TODO list isn't
turning anything up.  Flame me if I'm wasting everyone's time (well,
with this.  We needn't get into other things, or I'll never get out
of asbestos).

I have a table which keeps some name and IP pairs:

    \d name_and_ip
       Table "name_and_ip"
 Attribute |    Type     | Modifier
-----------+-------------+----------
 name      | varchar(30) | not null
 ip        | inet        | not null
Indices: name_and_ip_pairs,
         name_and_ip_pkey


Now, I have some values

 name |       ip
------+-----------------
 foo  | 192.168.186.248
 bar  | 192.168.186.249
(2 rows)

If I do

#UPDATE name_and_ip set ip = '192.168.186.249' where name = 'foo';

I get an error (which is good).  But, if I do

#BEGIN;
#SELECT * FROM name_and_ip WHERE name = 'foo' OR name = 'bar' FOR
    UPDATE;
#UPDATE name_and_ip SET ip = '192.168.186.249' where name = 'foo';
UPDATE 1
#COMMIT;
COMMIT

#SELECT * FROM name_and_ip WHERE name = 'foo' OR name = 'bar';
 name |       ip
------+-----------------
 bar  | 192.168.186.249
 foo  | 192.168.186.249
(2 rows)

Have I done something wrong here, or have I stumbled on a bug?
Shouldn't this cause an error and a rollback?

--
Andrew Sullivan                                      Computer Services
<sullivana@bpl.on.ca>                        Burlington Public Library
+1 905 639 3611 x158                                   2331 New Street
                                   Burlington, Ontario, Canada L7R 1J4

pgsql-general by date:

Previous
From: Lamar Owen
Date:
Subject: Re: Re: web programming
Next
From: Bruce Momjian
Date:
Subject: Re: FreeBSD Softupdates??