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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
