From: daniel alvarez <d-alvarez@gmx.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: rbt@rbt.ca
Cc: pgsql-sql@postgresql.org
Subject: Re: Atomicity of UPDATE, interchanging values in unique
Date: Sun, 9 Mar 2003 00:21:37 +0100 (MET)
Message-ID: <23176.1047165697@www5.gmx.net> (raw)
References: <12296.1047161444@sss.pgh.pa.us>
> >> 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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: d-alvarez@gmx.de, tgl@sss.pgh.pa.us, rbt@rbt.ca
Subject: Re: Atomicity of UPDATE, interchanging values in unique
In-Reply-To: <23176.1047165697@www5.gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox