Re: Are SQL commands "atomic" ?

On Thu, 7 Jun 2001, Gerald Gutierrez wrote:

> 
> I'm using 7.1.1 right now, and have the following table:
> 
>   id |   s
> ----+-------
>    1 | alpha
>    2 | beta
>    3 | gamma
>    4 | delta
> (4 rows)
> 
> I'd like to switch the id of "beta" to 3 and the id of "gamma" to 2 ("flip" 
> them). Since id is the PK, it must remain unique and so I can't just set 
> the two lines using two UPDATEs.
> 
> My solution is:
> 
> UPDATE t1 SET id=id#1 WHERE id=2 OR id=3; -- # is the XOR operator
> 
> where 2#1=3 and 3#1=2. One statement will change both values as I want. But 
> when I run the statement, the server replies with:
> 
> ERROR:  Cannot insert a duplicate key into unique index t1_pkey
> 
> If the statement is "atomic", then if the statement succeeds, the IDs will 
> be unique and the error is incorrect. Does this imply that SQL statements 
> are not actually atomic? 

Not exactly.  It's a bug in the implementation of the unique constraint.
The unique constraint is being checked per-row rather than per-statement.