Re: UPDATE

Tom Lane wrote:
> Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> writes:
>> AFAIK if you run the UPDATE while there are no other concurrent
>> transactions, Pg will write the new values in-place. It still updates
>> the WAL first, but it won't create whole new copies of each record as
>> well. That's the case at least if the field you're updating isn't
>> involved in any indexes; I think doing this relies on HOT, and won't
>> work if the column being altered is involved in one or more indexes.
>
> This is not correct; PG *never* overwrites an existing record (at least
> not in any user-accessible code paths).

That's what I always thought, but I encountered some odd behaviour while
trying to generate table bloat that made me think otherwise. I generated
a large table full of dummy data then repeatedly UPDATEd it. To my
surprise, though, it never grew beyond the size it had at creation time
... if the transaction running the UPDATE was the only one active.

If there were other transactions active too, the table grew as I'd expect.

Is there another explanation for this that I've missed?

> Yes, this is a lot slower than what MyISAM does. As noted, there are
> countervailing advantages.

For example, Pg doesn't have to accept all sorts of invalid data,
truncate things to fit instead of complaining, and silently coerce other
invalid input to defaults just to avoid the possibility to having to
raise an error - since it can just roll the changes back.

Ugh.

--
Craig Ringer