2010/12/15 matteo durighetto <desmodemone@gmail.com>:
> But why we need all these versions of the same row on table, if for
> rollback we need only the original row X (X0) ?
And the "previous" value of row X during the execution of a statement
(because statements don't see their own changes, think INSERT INTO a
SELECT * FROM a). And any values that we might need to ROLLBACK TO
SAVEPOINT to.
> So I think we need it in memory, not on physical space of table (ok
> there is the cache, but ..) or something similar
It must be possible to push out those changes from memory to disk
anyway, because there is no limit on how many rows a transaction can
update in PostgreSQL (vs. Oracle's "snapshot too old" problems). But
then, keeping the locally updated rows in some kind of special
per-transaction cache or in the global page cache isn't that
different.
Also, updating the same row many times in one transaction is probably
not regarded a very typical use case.
Note that other DBMSs may implement MVCC more along the lines you
specified; AFAIR, InnoDB uses such an approach. This may mean that
they don't need VACUUM.
I think that the consensus is that there is a trade-off between doing
VACUUM-like things synchronously, or having the possibility to do it
asynchronously at times when load is low. In PostgreSQL, the latter
was chosen.
Btw, this topic has been discussed at length in the past, please check
the archive.
> or this method is for transaction with isolation level at "read
> uncommited"?
PostgreSQL implements READ UNCOMMITTED as READ COMMITTED (providing a
higher level of isolation than requested is allowed by the standard),
so that is definitely not the reason.
Nicolas