Re: Question about OID and TCID

Since in-place seems not possible, I am trying to use arrays to store different ranges of that column, and it's fast to fetch a big array, so I believe an array is stored continuously on disk.

I can convert an array to a column easily by unnest() function, but I didn't find any function that converts a column to an array. Is there a efficient method to do that?

Thanks

----- Original Message -----
From: "Guillaume Lelarge" <guillaume(at)lelarge(dot)info>
To: "Zhipan Wang" <wzhipan(at)soe(dot)ucsc(dot)edu>
Cc: pgsql-general(at)postgresql(dot)org
Sent: Sunday, November 28, 2010 12:59:46 AM GMT -08:00 US/Canada Pacific
Subject: Re: [GENERAL] Question about OID and TCID

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