I tend to agree with you, that that's how it should be... I don't know what the standards have to say about it though.So I have to adjust a primary key by adding one to every existing record. Obviously this isn't a routine operation, my data model isn't that messed up. It's a one-time manual operation.
However when I tried to do the equivalent of:
update tab set pk = pk + 1
I got
ERROR: Cannot insert a duplicate key into unique index tab_pkey
Is that right? Obviously after completing the query there would be no duplicate keys. Is this a case where I would need deferred constraints to allow this? Even for immediate constraints shouldn't a single sql update be able to go ahead as long as it leaves things in a consistent state?
You cannot have unique constraints deferred either - only FKs, because the uniqueness is checked right when you attempt to insert the key into the index, and that cannot wait till the end of transaction, because then your current transaction would not be able to use that index (it would be nice to be able to postpone the insertin till the end of the statement though - for performance reasons - but that's not the way it works) :-(
The good news though is that, if you drop (or disable) your pk index before the update, and recreate (reindex) afterwards, your update statement should actually perform better ...
Dima
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
