Re: mvcc & DML on the same row

On Wed, Dec 15, 2010 at 2:50 PM, matteo durighetto
<desmodemone(at)gmail(dot)com> wrote:
> Hi,
>    I have an idea about mvcc and different DML of the same row in the
> same transaction.
> Normally when a backend do an unpdate on a row ( call it X ) , we done
> an insert and logical delete on this row  (0,1,2..N are the "version
> of the row) :
>
>   X0  (delete "old" row)
>   X1  (insert  "new" row)
>
> if  we continue the transaction and we do for example another update
> on this row (X) , we again redo the same operation:
>
>   X0  (deleted "old" row)
>   X1  (row inserted, NOW deleted) => not needed for rollback
>   X2  (insert "new" row  )
>
>
> But why we need all these versions of the same row on table, if for
> rollback we need only the original row X (X0) ?

The fact that we can't get rid of X1 until after the transaction
commits is an implementation limitation. But you obviously need both
X0 and X2, because the transaction might either commit or abort.

> So I think we need it in memory, not on physical space of table (ok
> there is the cache, but ..) or something similar, or this method is
> for transaction with isolation level at "read uncommited"?

I can't figure out exactly what this part is talking about. It's
completely impractical to add rows to a table without writing them
into shared buffers, which means they will eventually get flushed to
disk if not vacuumed, dropped, etc. first. We don't support read
uncommitted anyway (well, we do, but it's really still snapshot
isolation).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company