Re: Postgres unique index checking and atomic transactions

Greg Stark wrote:

>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?
>
>
>
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.
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