Le 27/11/2010 09:52, Zhipan Wang a écrit : > Hi, > > I want to access part of a table on the disk sequentially, i,e., when I get > to a tuple in the table, I need to read several pages of data in the table > starting from this tuple.
You shouldn't rely on the order on disk. It will change as soon as you update one. > I guess CTID could be translated to physical address on the disk to retrieve > this tuple, right? Yes, first number is the page number. The second one is the record number in that page. > If so, how do I use CTID to retrieve a particular tuple (or a page) in SQL? For example, SELECT * FROM yourtable WHERE ctid='(0,5)'; > Can I use OID to do this equally efficiently? No, they don't reflect the order on disk. > Another question is: when I update a tuple in a table, this tuple will get a > new CTID and it leaves a gap at the old CTID, and when I insert a new tuple, > it's appended to the end of the table, so the gap is always there. Does this > mean it actually inserts a new tuple and the out-dated tuple still occupies > the space? Yes. Other sessions could still need to see the old tuple values. > How can I write the updated tuple back to its original position to utilize > disk space more efficiently? You don't need to. PostgreSQL will deal with that. As soon as you do a VACUUM, PostgreSQL will be able to use the dead space for the next UPDATE or INSERT query (if there is enough space of course). -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
