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(at)gmx(dot)de>

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