Re: Atomicity of UPDATE, interchanging values in unique

> >> There must be a better solution than the additional dummy update.
> 
> How about swapping all the other columns, and preserving the identity of
> the primary key?  Arguably, swapping primary keys is a violation of the
> relational model to begin with.

You misunderstood what I'm saying. Of course updating a primary key would
be a cardinal sin. But this is not about primary keys. I did not even
mention it.
It is about exchanging unique values in an ordinary data column having a
unique
index on it. I observed that an update is not completely atomic, because the
constraints are validated as the indexes are accessed (probably once per
row)
and a single UPDATE swapping the values will fail. Observe:

UPDATE sometable SET unique_col =  CASE WHEN unique_col = firstvalue THEN secondvalue           ELSE  firstvalue  END
WHERE unique_col = firstvalue     OR unique_col = secondvalue

ERROR:  Cannot insert a duplicate key into unique index
sometable_unique_col_idx


The question is how to perform the swapping without having to use an
additional dummy
update. This approach works, but is ugly:

BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  UPDATE sometable SET someuniquecol = (SELECT MAX(someuniquecol) FROM
sometable) + 1  WHERE someuniquecol = 1;
  UPDATE sometable SET someuniquecol = 2 WHERE someuniquecol = 1;  UPDATE sometable SET someuniquecol = 1    WHERE
someuniquecol= (SELECT MAX(someuniquecol) FROM sometable) + 1;
 

COMMIT;


Regards,   Daniel Alvarez <d-alvarez@gmx.de>

-- 
+++ GMX - Mail, Messaging & more  http://www.gmx.net +++
Bitte lächeln! Fotogalerie online mit GMX ohne eigene Homepage!