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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
