Re: Surrogate keys (Was: enums)

On Sat, Jan 14, 2006 at 09:33:39PM +0100, Martijn van Oosterhout wrote:
> On Sat, Jan 14, 2006 at 11:06:07AM -0500, mark(at)mark(dot)mielke(dot)cc wrote:
> > Not to completely defend the practice - but in some applications,
> > INSERT is much less frequent than UPDATE, and that UPDATE requires a
> > unique check on the primary key and the surrogate key, as well as an
> > update, should be considered (and I believe is considered) a
> > PostgreSQL performance bug. It's undesirable and unnecessary behaviour
> > for the majority of uses (where they key does not change as a part of
> > the update).
> Unique check? An index is an index and when you do an UPDATE the new
> tuple has to be added to the index. At this point it doesn't matter if
> the index is unique or not, all indexes cost something.

> Since after the UPDATE the tuple with that primary key appears two (or
> more) times in the table, a check needs to be made that they don't
> overlap timewise. Are you claiming you could avoid this check and still
> guarentee correctness in the face of concurrent transactions?

I'm claiming that I agree with this TODO item:

- Prevent index uniqueness checks when UPDATE does not modify the column

Uniqueness (index) checks are done when updating a column
even if the column is not modified by the UPDATE.

Definately, the check is unnecessary. If it was unique before we made
the change, we know it will be unique after we've made the change.
The check shouldn't be performed for the primary key, or for the
surrogate key, if neither of these keys are modified in any way.

Perhaps you are challenging my addition of the phrase "as well as an
update", with a hint on my part, that I feel the update is unnecessary
as well. I may have been wrong to add these 5 words. The MVCC
implementation has numerous costs, and perhaps this is one of them
that cannot be avoided. :-(

Cheers,
mark

--
mark(at)mielke(dot)cc / markm(at)ncf(dot)ca / markm(at)nortel(dot)com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada

One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...

http://mark.mielke.cc/