Skip site navigation (1) Skip section navigation (2)

Re: Unique index: update error

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jim Nasby <jimn(at)enterprisedb(dot)com>
Cc: Golden Liu <goldenliu(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Unique index: update error
Date: 2006-09-18 04:14:14
Message-ID: 15231.1158552854@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Jim Nasby <jimn(at)enterprisedb(dot)com> writes:
> On Sep 14, 2006, at 9:16 PM, Golden Liu wrote:
>> 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.

> I suspect that your change adds a non-trivial overhead, which means  
> we don't want it to be the normal case.

There's a bigger problem:

	begin;
	update tab set col1 = ... where unique_key = ...;
	update tab set col2 = ... where unique_key = ...;
	commit;

If the first update doesn't insert index entries into unique_key's
index, then the second update won't find the tuples it needs to update
(unless we hack the planner to not trust the index as valid ... and
then it'd fall back on a seqscan, which is hardly acceptable anyway).

The scheme that I've thought about involves inserting index entries as
usual, but instead of having the aminsert code error out immediately
upon finding a duplicate, have it make an entry in a list of things
that need to be rechecked before commit.  This wins as long as potential
conflicts are uncommon.  Performance could suck if the list gets too
large --- but we have more or less the same hazard now for foreign-key
checks, and it mostly works well enough.  (In fact, maybe the existing
deferred trigger event list is the thing to use for the deferred
conflict rechecks.)

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Jim C. NasbyDate: 2006-09-18 04:16:32
Subject: Re: [HACKERS] One of our own begins a new life
Previous:From: Jim C. NasbyDate: 2006-09-18 04:08:16
Subject: relation cache statistics (was: -HEAD planner issue wrt hash_joins on dbt3 ?)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group