Hello Martijn, hello List,
Last question on this subject, what's happened during a SELECT query on
a tuple just updated and commited ?
I followed in the source code the links between these methods :
CreateQueryDesc, ExecutorStart, ExecutorRun, ExecutePlan, ExecSelect but
I still have a question :
- during a SELECT query on a tuple just updated and commited, does
the executor first detect the old tuple and then via the c_tid link go
to the new version of the tuple ? or go directly to the new version ?
- is it the same for the index ?
Could you tell me where theses checks are done in the source code ?
Thank you very much !
Regards,
Alexandra DANTE
Martijn van Oosterhout wrote:
On Mon, Jul 31, 2006 at 11:04:58AM +0200, DANTE Alexandra wrote:
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...
Well, in your case where there are no other transactions running, yes.
In the general case there may have been other updates so all you know
is that the new tuple is a descendant of the old one. The chain of
t_ctid links can be arbitrarily long.
Note: with multiple psql sessions you can see some of this happening.
======= Session 1 =======
test=# begin;
BEGIN
test=# set TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
SET
test=# select xmin, xmax, cmin, cmax, * from a;
xmin | xmax | cmin | cmax | country
--------+------+------+------+---------
277264 | 0 | 0 | 0 | IRAQ
(1 row)
======= Session 2 =======
test=# select xmin, xmax, cmin, cmax, * from a;
xmin | xmax | cmin | cmax | country
--------+------+------+------+---------
277264 | 0 | 0 | 0 | IRAQ
(1 row)
test=# update a set country = 'ITALY';
UPDATE 1
test=# select xmin, xmax, cmin, cmax, * from a;
xmin | xmax | cmin | cmax | country
--------+------+------+------+---------
277269 | 0 | 0 | 0 | ITALY
(1 row)
======= Session 1 again =======
test=# select xmin, xmax, cmin, cmax, * from a;
xmin | xmax | cmin | cmax | country
--------+--------+--------+------+---------
277264 | 277269 | 277269 | 0 | IRAQ
(1 row)
As you can see now, both sessions are seeing different views of the
same table. The old tuple has now updated xmax and cmin values. If
session two updated the tuple again it would get a cmax value also. You
can't see the t_ctid link here, but there is one from the old row to
the new one.
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 ?
It was Tom who pointed this out to me first: the infomask is not the
important part. The infomask is just a cache of the results of tests.
The problem is that checking if a transaction has been committed or not
can be reasonably expensive since it might have to check on disk. If
you had to do that every time you looked up a tuple the performence
would be terrible. So what happens is that the first time someone looks
up the status of a transaction and finds it's committed or aborted, it
sets that bit so no-one else has to do the test.
The basic result is that you can read the code as if the infomask was
blank and the result should be the same. The only difference is that
various bits allow the code to skip certain tests because somebody has
already done them before. The end result should be the same.
So, consequently, it is not necessary to rebuild the B-tree index after
an update or a delete.
Is it correct ?
You never have to rebuild the index. The whole system is designed so
many people can be reading and writing the index simultaneously without
getting in eachothers way.
Hope this helps,
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
