Re: Questions about update, delete, ctid... - Mailing list pgsql-general

Hello Martijn, hello List,

Thank you very much.
I still have some questions, further to your answers :

Martijn van Oosterhout wrote:

>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.
>
>
>
I've just seen that I've done a mistake in my example. My question was :
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 | *IRAQ*
Could you give me more details about the link between the old and the
new version, please ?
For me, the link is the c_tid, but maybe I'm wrong...

>>- 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.
>
>
>
My question about the "infomask" strucutre was linked to the code of
VACUUM. I've seen in the "lazy_scan_heap method that the
"HeapTupleSatisfiesVacuum" method is called. In this method, according
to the value of "infomask", a tuple is defined as "dead" or not.
That's why I wonder if the "infomask" structure is changed after an
commited update or delete, and what are the values set ?

>>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.
>
>
So, consequently, it is not necessary to rebuild the B-tree index after
an update or a delete.
Is it correct ?

>Hope this helps,
>
>

Regards,
Alexandra