![[Home]](/~megera/oddmuse/zen3.gif)
Difference between revision 2 and current revision
Changed:
< == PostgreSQL: 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 changes in update and vacuum (CLUSTER also changes ctid).
to
> == 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).
> <pre>
> 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)
> </pre>
Changed:
< ''ctid'' is good to access to '''1''' tuple only!!!, since
to
> Update tuple with ctid= (0,9)
> <pre>
> 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)
> </pre>
> A list of attributes could be obtained from ''pg_attribute'':
> <pre>
> 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)
> </pre>
> Observations:
> * ''ctid'' is good to access to '''1''' tuple only!!!, since
Changed:
< ''ctid'' is safe to use only withing one transaction !!!
< ''ctid'' could be used to bypass index on an update.
to
> * ''ctid'' is safe to use only within '''one''' transaction !!!
> * ''ctid'' could be used to bypass index on an update.
> References:
> * http://www.postgresql.org/docs/8.1/static/ddl-system-columns.html
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:
References:
