[SQL] Atomicity of UPDATE, interchanging values in unique column

[daniel alvarez]

UPDATE statements are not completely atomic in that index entries are
updated
separately for each row. A query interchanging two values within a column
declared
UNIQUE will fail due to the attempt of inserting a duplicate temporarily. It
seems
like Postgres validates constraints on indexes each time the implementation
modifies
the index, rather than on the logical transaction boundaries.

I tried:

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


And:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

UPDATE sometable SET unique_col = firstvalue WHERE unique_col = secondvalue;
UPDATE sometable SET unique_col = secondvalue WHERE unique_col = firstvalue;

COMMIT;


And both queries fail.

Of course I could prevent this by first updating one of the entries with a
dummy value:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

UPDATE sometable SET unique_col = dummy WHERE unique_col = secondvalue;
UPDATE sometable SET unique_col = secondvalue WHERE unique_col = firstvalue;
UPDATE sometable SET unique_col = firstvalue WHERE unique_col = dummy;

COMMIT;


But that's more like in a 3GL language and does not cleanly express what I
want.

How can I interchange two values in a unique column? Am I missing something
really
obvious (like a swap statement)? Is there any reason besides performance for
not
making index accesses fully ACID-compliant? Doesn't MVCC require this
anyway?

Thanks for your time,   Daniel Alvarez <[EMAIL PROTECTED]>

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


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Atomicity of UPDATE, interchanging values in unique column

[daniel alvarez]

> The first is what you want.  PostgreSQL needs some work in the
> evaluation of unique indexes to properly support it.
> 
> Namely, when it sees a conflict when inserting into the index, it needs
> to record the fact, and revisit the conflict at the end of the command. 
> Lots of work...

OK. The long-term goal would then be to get rid of such oddities. But what
can I do right now as a user to solve that issue for my application?

There must be a better solution than the additional dummy update.

Any ideas?

Daniel Alvarez Arribas <[EMAIL PROTECTED]>

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


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org