pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAtomicity of UPDATE, interchanging values in unique column
6+ messages / 3 participants
[nested] [flat]
* Atomicity of UPDATE, interchanging values in unique column
@ 2003-03-08 21:17 daniel alvarez <d-alvarez@gmx.de>
0 siblings, 1 reply; 6+ messages in thread
From: daniel alvarez @ 2003-03-08 21:17 UTC (permalink / raw)
To: pgsql-sql
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!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Atomicity of UPDATE, interchanging values in unique column
@ 2003-03-08 21:33 Rod Taylor <rbt@rbt.ca>
parent: daniel alvarez <d-alvarez@gmx.de>
0 siblings, 1 reply; 6+ messages in thread
From: Rod Taylor @ 2003-03-08 21:33 UTC (permalink / raw)
To: daniel alvarez <d-alvarez@gmx.de>; +Cc: pgsql-sql
> UPDATE sometable SET unique_col =
> CASE WHEN unique_col = firstvalue THEN secondvalue
> ELSE firstvalue
> END
> WHERE unique_col = firstvalue
> OR unique_col = secondvalue
(See last comment)
> 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;
This one will always fail unless you DEFER unique constraints --
something we don't support with PostgreSQL, but some others do.
> 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?
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...
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Atomicity of UPDATE, interchanging values in unique column
@ 2003-03-08 21:48 daniel alvarez <d-alvarez@gmx.de>
parent: Rod Taylor <rbt@rbt.ca>
0 siblings, 1 reply; 6+ messages in thread
From: daniel alvarez @ 2003-03-08 21:48 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: pgsql-sql
> 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 <d-alvarez@gmx.de>
--
+++ GMX - Mail, Messaging & more http://www.gmx.net +++
Bitte lächeln! Fotogalerie online mit GMX ohne eigene Homepage!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Atomicity of UPDATE, interchanging values in unique
@ 2003-03-08 21:56 Rod Taylor <rbt@rbt.ca>
parent: daniel alvarez <d-alvarez@gmx.de>
0 siblings, 1 reply; 6+ messages in thread
From: Rod Taylor @ 2003-03-08 21:56 UTC (permalink / raw)
To: daniel alvarez <d-alvarez@gmx.de>; +Cc: pgsql-sql
On Sat, 2003-03-08 at 16:48, daniel alvarez wrote:
> > 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?
Certainly.. But you have to find someone willing to do a the work for
little gain. There are lots of issues more important to most of the
developers.
> There must be a better solution than the additional dummy update.
You could try hiding it behind a function, but I'm afraid thats the only
sane way to do it. Select into temp table, delete both, and insert
values back in again is another :)
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Atomicity of UPDATE, interchanging values in unique
@ 2003-03-08 22:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rod Taylor <rbt@rbt.ca>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2003-03-08 22:10 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: daniel alvarez <d-alvarez@gmx.de>; pgsql-sql
>> 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.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Atomicity of UPDATE, interchanging values in unique
@ 2003-03-08 23:21 daniel alvarez <d-alvarez@gmx.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: daniel alvarez @ 2003-03-08 23:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: rbt@rbt.ca; pgsql-sql
> >> 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!
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-03-08 23:21 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-08 21:17 Atomicity of UPDATE, interchanging values in unique column daniel alvarez <d-alvarez@gmx.de>
2003-03-08 21:33 ` Rod Taylor <rbt@rbt.ca>
2003-03-08 21:48 ` daniel alvarez <d-alvarez@gmx.de>
2003-03-08 21:56 ` Re: Atomicity of UPDATE, interchanging values in unique Rod Taylor <rbt@rbt.ca>
2003-03-08 22:10 ` Re: Atomicity of UPDATE, interchanging values in unique Tom Lane <tgl@sss.pgh.pa.us>
2003-03-08 23:21 ` Re: Atomicity of UPDATE, interchanging values in unique daniel alvarez <d-alvarez@gmx.de>
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