Re: Surrogate keys (Was: enums)

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?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.