> >> 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!