2006-03-05

PostgreSQL: about system attributes - tableoid,oid,ctid

tableoid is the oid of a table, oid - id for identification (only if table was created WITH OIDS), ctid - tuple id in its table (block#,id in block). ctid reflects physical location of tuple and changed in update and vacuum (CLUSTER also changes ctid).

test=# select *,tableoid,ctid,xmin,xmax,cmin,cmax from s;
  a  | tableoid | ctid  |  xmin  | xmax | cmin | cmax
-----+----------+-------+--------+------+------+------
  17 |  1019426 | (0,4) | 179947 |    0 |    0 |    0
 156 |  1019426 | (0,6) | 179971 |    0 |    0 |    0
 -11 |  1019426 | (0,7) | 179975 |    0 |    0 |    0
  15 |  1019426 | (0,9) | 179988 |    0 |    4 |    0
(4 rows)

currtid(tableoid::oid,ctid::tid), currtid2(table_name::text,ctid::tid) return new ctid after last update.

Update tuple with ctid= (0,9)

test=# update s set a=-15 where a=15;
UPDATE 1
Time: 4.767 ms
test=# select *,tableoid,ctid,xmin,xmax,cmin,cmax from s;
  a  | tableoid |  ctid  |  xmin  | xmax | cmin | cmax
-----+----------+--------+--------+------+------+------
  17 |  1019426 |  (0,4) | 179947 |    0 |    0 |    0
 156 |  1019426 |  (0,6) | 179971 |    0 |    0 |    0
 -11 |  1019426 |  (0,7) | 179975 |    0 |    0 |    0
 -15 |  1019426 | (0,10) | 180510 |    0 |    0 |    0
(4 rows)
test=# select currtid(1019426,'(0,9)');
 currtid
---------
  (0,10)
(1 row)

A list of attributes could be obtained from pg_attribute:

test=# select oid from pg_class where relname='s';
   oid   
---------
 1019426
test=# select attname from pg_attribute where attrelid=1019426;
 attname  
----------
 tableoid
 cmax
 xmax
 cmin
 xmin
 ctid
 a
(7 rows)

Observations:

  • ctid is good to access to 1 tuple only!!!, since no hashjoin, mergejoin operations are defined for tid datatype.
  • ctid is safe to use only within one transaction !!!
  • ctid could be used to bypass index on an update.

References: