Re: Questions about update, delete, ctid...

On Fri, Jul 28, 2006 at 12:00:19PM +0200, DANTE Alexandra wrote:
> Hello List,
>
> I try to understand perfectly the mecanisms used to update / delete a
> tuple (and consequently those used in VACUUM) and I've got some questions.

<snip example>

> I hope someone could answer these questions :
> - what are the new values for xmin, xmax and ctid for an updated tuple ?

xmin is the transaction that created the tuple (ie your XID)
xmax is the transaction that deleted the tuple (ie zero, it's not dead)
ctid is wherever it ends up on disk

> - what about the old tuple ? what is the value for xmax ?

Your XID, given you deleted it.

> - is it correct to think that the ctid of the old version of the tuple
> is a link to newer version ? In my example, is it correct to think that
> the tuple :
> 140049 | 0 | (0,12) | 11 | IRAQ
> has become :
> new value | 0 | (0,26) | 11 | ITALY

The word "become" is not really right. The old version has become
invisible to you and the new version is visible. Some other
transactions will see the old one, some the new one. However, there is
a link between the old and the new version do detect conflicting
updates.

> - what are the values set in the "infomask" structure for the old
> version of the tuple ?

I don't think there are any changes. Until your transaction commits you
can't really know if the tuple is really deleted or not. The first
transaction to read the tuple after your transaction commits will
update the bits.

> And then, after all these questions about tables, I've got questions
> about index. Imagine that we have an index of the "n_name" column, after
> the update :
> - is it correct to think that a new index tuple has been created ?

Yes

> - does the old index tuple link to the new index tuple ?

No

> - if not, how the B-tree can be still balanced ? is it necessary to
> rebuild the index ?

The b-tree code attempts to keep itself balanced. But it does nothing
special for an UPDATE, it works the same as an INSERT.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.