Re: Unique index: update error

On Sep 14, 2006, at 9:16 PM, Golden Liu wrote:
> Suppose there are too tuples in a table t, named
> id
> ---
> 1
> 2
>
> and there is a unique index on id. Now we do an update on table t
> update t set id=id+1
>
> Since PG executes the update one tuple at a time, it updates tuple "1"
> to "2" and insert it into the index. Before insert into the index, it
> check whether the id is still "unique" or not. No, it's not, old tuple
> "2" is still in the table. So an error is raised.
>
> I try to solve this problem this way:
> First, update the table t but DON'T update the index.
> Next, find all the tuples updated by this command and insert them into
> the unique index.

Isn't that what a deferred constraint normally does?

I suspect that your change adds a non-trivial overhead, which means
we don't want it to be the normal case.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)