Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010
    Posts
    4

    Unanswered: Question about OID and CTID

    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. I guess CTID could be translated to physical address on the disk to retrieve this tuple, right? If so, how do I use CTID to retrieve a particular tuple (or a page) in SQL?

    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? How can I write the updated tuple back to its original position to utilize disk space more efficiently?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Your question seems all wrong. What exactly are you trying to achieve?

    Using CTID is normally not a good idea.

    You should select rows based on the values of your primary key (or any other "regular" column in your table)

  3. #3
    Join Date
    Nov 2010
    Posts
    4
    I am trying out some ideas proposed in a paper (Database Cracking). Some operations I need to use on postgres are sort of low level. I need to retrieve some tuples (usually stored continuously on the disk) by their offsets in the table, which should be much more efficient than using indexes to look up each tuple. According to what I have read, ctid seems to be the only value related to physical structure of the table on the disk, so I want to take advantage of it. I know ctid is not stable and may cause some problems, but if I can do in-place update, the problems may be avoided by careful design.

  4. #4
    Join Date
    Nov 2010
    Posts
    4
    Quote Originally Posted by shammat View Post
    Your question seems all wrong. What exactly are you trying to achieve?

    Using CTID is normally not a good idea.

    You should select rows based on the values of your primary key (or any other "regular" column in your table)
    I am trying out some ideas proposed in a paper (Database Cracking). Some operations I need to use on postgres are sort of low level. I need to retrieve some tuples (usually stored continuously on the disk) by their offsets in the table, which should be much more efficient than using indexes to look up each tuple. According to what I have read, ctid seems to be the only value related to physical structure of the table on the disk, so I want to take advantage of it. I know ctid is not stable and may cause some problems, but if I can do in-place update, the problems may be avoided by careful design.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •