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" <guilla...@lelarge.info> To: "Zhipan Wang" <wzhi...@soe.ucsc.edu> Cc: pgsql-general@postgresql.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
