Re: [TLM] Re: How to insert on duplicate key?

On Fri, 28 Dec 2007, Robert Treat wrote:

> A given transaction doesn't have a way to determine if there are live
> transaction looking at the row, that would require quite a bit of
> knowledge about what else is occuring in the system to be able to
> determine that. That level of knowledge/complexity is what vacuum takes
> care of.

One reason it doesn't happen automatically is that it would slow down the
client doing the update. Sorting through to figure out which rows can be
seen by which clients is better done later for a number of reasons; some
samples:

-It's more likely there won't be any transactions still referencing the
original row as time moves forward. If you do it the instant the row is
dead, odds are higher there's still somebody using the original one and
you can't prune it yet anyway.

-It's more efficicent to sort through a bunch of these in bulk than to
do them one at a time.

-You need to have a similar vacuum process happening regularly anyway to
analyze your tables and keep statistics about them up to date, so might as
well do both of those things at once.

The downside is that vacuum can have a relatively high impact on the
system, but the answer there is to do it more often so that any individual
vacuum is less difficult.

It'a also worth mentioning that some of this update row reuse happens more
automatically in V8.3 with a new feature called HOT, so in some cases this
particular issue has already has a workaround everyone can get in the near
future.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD