Thread: Atomicity of UPDATE, interchanging values in unique column
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!
> 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
> 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!
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
>> 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
> >> 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!