From: daniel alvarez <d-alvarez@gmx.de>
To: pgsql-sql@postgresql.org
Subject: Atomicity of UPDATE, interchanging values in unique column
Date: Sat, 8 Mar 2003 22:17:13 +0100 (MET)
Message-ID: <32207.1047158233@www5.gmx.net> (raw)
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!
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
Subject: Re: Atomicity of UPDATE, interchanging values in unique column
In-Reply-To: <32207.1047158233@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