Re: hot update doesn't work?

On Wed, May 12, 2010 at 11:34 AM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
>> I would to repeatably update non indexed column of temp table. I
>> expected cheap operation, but it isn't true.
>
> You're updating the row 100000 times within a single transaction.  I
> don't *think* HOT will reclaim a version of a row until the
> transaction which completed it is done and no other transactions can
> see that version any longer.  It does raise the question, though --
> couldn't a HOT update of a tuple *which was written by the same
> transaction* do an "update in place"?  I mean, the updating
> transaction doesn't need to see the old row after this, and other
> transactions shouldn't see it either.
>
> I suspect that somewhere in the subtransaction or referential
> integrity areas there may be some issues with that, but it would be
> a clever optimization if it could be pulled off.

scripting this outside of transaction does not exhibit the behavior --
even with autovac off relation size tops out arond 57k. vacuuming as
it goes seems to top out around 200 row versions before hot catches
them. I guess a good way to think of hot is a page level vacuum.

merlin