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?

It's a bug. It's even a bug tested for by mySQL's crashme script under
the title "atomic updates". Joe Celko's workaround for "eary SQL
implementations" is:

BEGIN;
UPDATE tab SET pk = - (pk + 1);
UPDATE tab SET pk = - (pk);
END;

Hope that helps,

Mike Mascari
mascarm@mascari.com