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 AACFE475B8E
	for <pgsql-sql@postgresql.org>; Sat,  8 Mar 2003 18:21:35 -0500 (EST)
Received: (qmail 8091 invoked by uid 0); 8 Mar 2003 23:21:37 -0000
Date: Sun, 9 Mar 2003 00:21:37 +0100 (MET)
From: daniel alvarez <d-alvarez@gmx.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: rbt@rbt.ca, pgsql-sql@postgresql.org
MIME-Version: 1.0
References: <12296.1047161444@sss.pgh.pa.us>
Subject: Re: Atomicity of UPDATE, interchanging values in unique
X-Priority: 3 (Normal)
X-Authenticated-Sender: #0002681999@gmx.net
X-Authenticated-IP: [217.185.184.200]
Message-ID: <23176.1047165697@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/217
X-Sequence-Number: 12257

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