X-Original-To: pgsql-sql@postgresql.org
Received: from mx0.gmx.net (mx0.gmx.net [213.165.64.100])
	by postgresql.org (Postfix) with SMTP id 35C004758C9
	for <pgsql-sql@postgresql.org>; Sat,  8 Mar 2003 16:17:12 -0500 (EST)
Received: (qmail 11364 invoked by uid 0); 8 Mar 2003 21:17:13 -0000
Date: Sat, 8 Mar 2003 22:17:13 +0100 (MET)
From: daniel alvarez <d-alvarez@gmx.de>
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Subject: Atomicity of UPDATE, interchanging values in unique column
X-Priority: 3 (Normal)
X-Authenticated-Sender: #0002681999@gmx.net
X-Authenticated-IP: [217.185.184.254]
Message-ID: <32207.1047158233@www5.gmx.net>
X-Mailer: WWW-Mail 1.6 (Global Message Exchange)
X-Flags: 0001
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 8bit
X-Archive-Number: 200303/211
X-Sequence-Number: 12251


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 <d-alvarez@gmx.de>

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