PostgreSQLスキルアップノート（自己啓発のための個人サイト）
隠れた列「システム列」ｏｉｄとｃｔｉｄ
【一覧に戻る】
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ 隠れた列「システム列」ｏｉｄとｃｔｉｄ
■■■■
■■■■
■■■■ 2013/02/10
■■■■ 使用環境：PostgreSQL9.1.7 (CentOS6.2)
(C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
★自己都合によりマニュアルへのリンクは9.2としています。ご了承下さい。
★このページはそのままカット&ペーストで実行して試すことができます。
【マニュアル】
システム列 →●[マニュアル]
オブジェクト識別子データ型 →●[マニュアル]
【マニュアル参考】
−
【参考記事】
−
■１■ テーブルにある隠れた列「システム列」
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
テーブルには暗黙的に用意されるシステム列が存在する。
システム列には以下のようなものがある。
・oid 行のオブジェクト識別子（オブジェクトID）
・ctid 行物理的位置
・tableoid テーブルのOID 継承を使用したテーブルなどで参照する価値あり。
・xmin 挿入時のトランザクションID
・xmax 削除時のトランザクションID
・cmin 挿入時のトランザクション内のコマンド識別子
・cmax 削除時のトランザクション内のコマンド識別子
以降ではoidとctidのみを取り上げる。
oid,ctidともに行の特定に利用できるが以下のような特性を持っている。
Oracleのrowidのような使い方をする場合には注意が必要。
■２■ ｏｉｄ
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
１．oidの概要
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
オブジェクト識別子（オブジェクトID）
・業務のテーブルの列にWITH OIDS句を使ってoidを作ることは可能。
個々の行にoidが与えられるので行の特定用途に使うことができる。
・OIDは後述のCTIDとは異なり、UPDATEしても番号は変化しない。
（当然Delete/Insertすれば変わるが）
★しかし以下の理由からOIDの利用は推奨されない。
・oidはデータベースクラスタ全体での通し番号。
・oidは有限。全体で32ビット（40億個）しかない。これを超えると重複が始まる。
・oidはシステムテーブルの管理用に各情報のプライマリキーとして使われている。
したがって業務のテーブルの行にoidを作成するとoidの枯渇を引き起こし、
システムテーブルが破綻する危険がある。(OIDの周回問題）
・新しいPostgreSQLバージョン(8.1以降)ではテーブル作成のデフォルトでOIDは用意されなくなっている。
もしどうしても使いたいなら、
CREATE時に明示的にWITH OIDS句を指定するか、default_with_oidsパラメータ設定で行うことができる。
２．oidの作成
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
前述のように普通にテーブルを作成してもoidは用意されない。
以下のいずれかが必要
・default_with_oidsパラメータで
postgre.conf default_with_oidsをon（デフォルトはoff）
set文でも可能 set default_with_oids = On;
・WITH OIDS句で
create table t1 (c1 varchar(10)) with oids;
３．oidはＤＢクラスタ全体の通し番号
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
postgres=# create table t1 (c1 varchar(10)) with oids;
postgres=# create table t2 (c1 varchar(10)) with oids;
database1=# create table t1 (c1 varchar(10)) with oids;
postgres=# insert into t1 values('aaa');
INSERT 262027 1
postgres=# insert into t2 values('aaa');
INSERT 262028 1
database1=# insert into t1 values('aaa');
INSERT 262029 1
・・postgresDBのt1,t2、database1DBのt1 通しで付与していることが分かる。
・・この調子でINSERTを続け４０億を超えると同じ番号を発生させてしまうということになる。
たとえ小規模なシステムであってもwith oidsは使用すべきではないと思われる。
４．UPDATEしてもoidは変化しない。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
後述のctidとの動作を比較
postgres=# select oid,ctid,* from t1 where c1='aaa';
oid | ctid | c1
--------+-------+-----
262027 | (0,3) | aaa
postgres=# update t1 set c1='aaaaa' where c1='aaa';
postgres=# select oid,ctid,* from t1 where c1='aaaaa';
oid | ctid | c1
--------+-------+-------
262027 | (0,4) | aaaaa
■４■ ｃｔｉｄ
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
１．ctidの概要
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
行の物理的位置。テーブル内で、行をブロックとオフセットの値で表す。
・ctidは更新やVACUUM FULLなどによって値が変わるので注意が必要であるが
それを理解した上で使用するなら、行を識別する目的での利用は可能。
２．UPDATEでctidが変化してしまう
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
（１）確認用テーブル作成
drop table test_ctid;
create table test_ctid (c1 numeric(5),c2 varchar(10));
insert into test_ctid values(1,'aaaaa');
insert into test_ctid values(2,'bbbbb');
insert into test_ctid values(3,'ccccc');
insert into test_ctid values(4,'ddddd');
begin;
select ctid,c1,c2 from test_ctid;
update test_ctid set c2='22222' where c1=2;
update test_ctid set c2='33333' where c1=3;
insert into test_ctid values(5,'eeeee');
commit;
（２）最初の状態
postgres=# select ctid,c1,c2 from test_ctid;
ctid | c1 | c2
-------+----+-------
(0,1) | 1 | aaaaa
(0,2) | 2 | bbbbb
(0,3) | 3 | ccccc
(0,4) | 4 | ddddd
(4 rows)
（３）２行更新、１行追加
postgres=# update test_ctid set c2='22222' where c1=2;
postgres=# update test_ctid set c2='33333' where c1=3;
postgres=# insert into test_ctid values(5,'eeeee');
（４）更新、追加後の状態
postgres=# select ctid,c1,c2 from test_ctid;
ctid | c1 | c2
--------+----+-------
(0,1) | 1 | aaaaa
(0,4) | 4 | ddddd
(0,9) | 2 | 22222 更新した行
(0,10) | 3 | 33333 更新した行
(0,11) | 5 | eeeee 追加した行
(5 rows)
★更新した行はすべてCTIDが変化してしまう
以上
