From: | Jim Nasby <jimn(at)enterprisedb(dot)com> |
---|---|
To: | Golden Liu <goldenliu(at)gmail(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Unique index: update error |
Date: | 2006-09-18 03:35:12 |
Message-ID: | C41306B6-3B93-4344-B556-9B029C99A910@enterprisedb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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)
From | Date | Subject | |
---|---|---|---|
Next Message | Jim C. Nasby | 2006-09-18 04:08:16 | relation cache statistics (was: -HEAD planner issue wrt hash_joins on dbt3 ?) |
Previous Message | Tom Lane | 2006-09-18 02:31:30 | Re: 8.2 beta blockers |