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

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.

I've created a small database with only one table called "nation".  This
table was created with this command :
CREATE TABLE nation(
    n_nationkey          bigint NOT NULL,
    n_name               char(25)
);
When I have inserted 25 rows concerning countries ans have updated on row.
Before doing an update, the values of xmin, xmax and ctid were :
testvacuum=# select xmin, xmax, ctid, * from nation;
  xmin  | xmax |  ctid  | n_nationkey |          n_name
--------+------+--------+-------------+---------------------------
 140049 |    0 |  (0,1) |           0 | ALGERIA
 140049 |    0 |  (0,2) |           1 | ARGENTINA
 140049 |    0 |  (0,3) |           2 | BRAZIL
 140049 |    0 |  (0,4) |           3 | CANADA
 140049 |    0 |  (0,5) |           4 | EGYPT
 140049 |    0 |  (0,6) |           5 | ETHIOPIA
 140049 |    0 |  (0,7) |           6 | FRANCE
 140049 |    0 |  (0,8) |           7 | GERMANY
 140049 |    0 |  (0,9) |           8 | INDIA
 140049 |    0 | (0,10) |           9 | INDONESIA
 140049 |    0 | (0,11) |          10 | IRAN
 140049 |    0 | (0,12) |          11 | IRAQ
 140049 |    0 | (0,13) |          12 | JAPAN
 140049 |    0 | (0,14) |          13 | JORDAN
 140049 |    0 | (0,15) |          14 | KENYA
 140049 |    0 | (0,16) |          15 | MOROCCO
 140049 |    0 | (0,17) |          16 | MOZAMBIQUE
 140049 |    0 | (0,18) |          17 | PERU
 140049 |    0 | (0,19) |          18 | CHINA
 140049 |    0 | (0,20) |          19 | ROMANIA
 140049 |    0 | (0,21) |          20 | SAUDI ARABIA
 140049 |    0 | (0,22) |          21 | VIETNAM
 140049 |    0 | (0,23) |          22 | RUSSIA
 140049 |    0 | (0,24) |          23 | UNITED KINGDOM
 140049 |    0 | (0,25) |          24 | UNITED STATES
(25 rows)

Then I updated the row where the "n_name" was "IRAQ", and replaced it by
"ITALY" :
testvacuum=# update nation set n_name='ITALY' where n_nationkey=11;
UPDATE 1
testvacuum=# select xmin, xmax, ctid, * from nation;
  xmin  | xmax |  ctid  | n_nationkey |          n_name
--------+------+--------+-------------+---------------------------
 140049 |    0 |  (0,1) |           0 | ALGERIA
 140049 |    0 |  (0,2) |           1 | ARGENTINA
 140049 |    0 |  (0,3) |           2 | BRAZIL
 140049 |    0 |  (0,4) |           3 | CANADA
 140049 |    0 |  (0,5) |           4 | EGYPT
 140049 |    0 |  (0,6) |           5 | ETHIOPIA
 140049 |    0 |  (0,7) |           6 | FRANCE
 140049 |    0 |  (0,8) |           7 | GERMANY
 140049 |    0 |  (0,9) |           8 | INDIA
 140049 |    0 | (0,10) |           9 | INDONESIA
 140049 |    0 | (0,11) |          10 | IRAN
 140049 |    0 | (0,13) |          12 | JAPAN
 140049 |    0 | (0,14) |          13 | JORDAN
 140049 |    0 | (0,15) |          14 | KENYA
 140049 |    0 | (0,16) |          15 | MOROCCO
 140049 |    0 | (0,17) |          16 | MOZAMBIQUE
 140049 |    0 | (0,18) |          17 | PERU
 140049 |    0 | (0,19) |          18 | CHINA
 140049 |    0 | (0,20) |          19 | ROMANIA
 140049 |    0 | (0,21) |          20 | SAUDI ARABIA
 140049 |    0 | (0,22) |          21 | VIETNAM
 140049 |    0 | (0,23) |          22 | RUSSIA
 140049 |    0 | (0,24) |          23 | UNITED KINGDOM
 140049 |    0 | (0,25) |          24 | UNITED STATES
 140061 |    0 | (0,26) |          11 | ITALY
(25 rows)

By doing this update, I see that a new xmin, xmax and ctid have been
computed and that the new tuple with the name "ITALY" appears at the end
of the table.
I have tried to found in the source code what has been done during the
update, exploring the "ExecUpdate" method in the
"backend/executor/execMain.c" file, the "heap_update" method in the
"backend/access/heap/heapam.c" file, the structure defined in the
"include/access/htup.h" file, ... but it is not very easy for someone
not familiar with the code...

I hope someone could answer these questions :
- what are the new values for xmin, xmax and ctid for an updated tuple ?
- what about the old tuple ? what is the value for xmax ?
- 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

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


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 ?
- does the old index tuple link to the new index tuple ?
- if not, how the B-tree can be still balanced ? is it necessary to
rebuild the index ?

Thank you very much for your help.
Regards,
Alexandra DANTE