Re: Unique index: update error - Mailing list pgsql-hackers

From Jim Nasby
Subject Re: Unique index: update error
Date
Msg-id C41306B6-3B93-4344-B556-9B029C99A910@enterprisedb.com
Whole thread Raw
In response to Unique index: update error  ("Golden Liu" <goldenliu@gmail.com>)
Responses Re: Unique index: update error  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
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@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)




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




pgsql-hackers by date:

Previous
From: Jim Nasby
Date:
Subject: Re: Mid cycle release?
Next
From: "Jim C. Nasby"
Date:
Subject: relation cache statistics (was: -HEAD planner issue wrt hash_joins on dbt3 ?)