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.