ファイヤープロジェクト
テーブル作成
2003-12-29T14:35+09:00   matsu
制約とその指定方法について簡単に記述し,作成した問題に対して,テーブルを作成してみる.
まず最初にDBを作成してみる.
$ su postmaster
$ createdb benchmark
次にリモートからのアクセス設定を行なう./etc/postgresql/pg_hba.confを編集する(※).
# Put your actual configuration here
...省略...
host benchmark リモートマシンIP リモートマシンサブネットマスク crypt
次に設定を読み込む.
# /etc/init.d/postgresql reload
これでリモートからアクセスできる.
※ あらかじめ/etc/postgresql/postgresql.confでリモートアクセス自体を許可する設定をしておく必要がある.
tcpip_socket = 1
PostgreSQLでは,テーブル作成,変更時に列に対して以下の制約を指定することができる.
NOT NULL制約
値がNULLであってはならない.
UNIQUE制約
値がテーブル内でユニークでなければならない.ただし値はNULLであってもよく,値がNULLであるレコードが複数あってもよい.UNIQUE制約のある列は候補キーである.
主キー制約(実体整合性制約)
それが主キーであるための制約.UNIQUE制約+NOT NULL制約.
CHECK制約
CHECKにより指定した任意の制約.
参照整合性制約
外部キーに指定された列の値は,NULLを除いてそれと同値を持つレコードが参照先のテーブルに存在しなければならない.
これらのうち,UNIQUE制約とCHECK制約は今回のテーブル作成では使用しないので,ここで紹介しておく.まずUNIQUE制約.
benchmark=> CREATE TABLE has_unique(
benchmark(> unique_num INTEGER UNIQUE);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'has_unique_unique_num_key' for table 'has_unique'
CREATE

benchmark=> \d has_unique 
        Table "has_unique"
   Column   |  Type   | Modifiers 
------------+---------+-----------
 unique_num | integer | 
Unique keys: has_unique_unique_num_key
UNIQUE制約を設定すると,自動的にインデクスが作成される.試しにUNIQUE制約に違反してみる.
benchmark=> INSERT INTO has_unique  
benchmark-> VALUES (1);
INSERT 25976 1
benchmark=> INSERT INTO has_unique 
benchmark-> VALUES (1);
ERROR:  Cannot insert a duplicate key into unique index has_unique_unique_num_key
次にCHECK制約.CHECK制約では任意の制約を課すことができる.CONSTRAINTを使用することで,CHECK制約に名前を指定することができる.複数のCHECK制約を指定する際には,各CHECK制約に名前がついていると違反した制約の特定に便利である.
CONSTRAINT 制約名 CHECK(...)
今回は値が正でなければならないという制約にしてみた.
benchmark=> CREATE TABLE has_check(
benchmark(> i INTEGER,
benchmark(> CONSTRAINT bt0 CHECK(i > 0));
CREATE

benchmark=> \d has_check 
      Table "has_check"
 Column |  Type   | Modifiers 
--------+---------+-----------
 i      | integer | 
Check constraints: "bt0" (i > 0)
では,これに違反してみる.
benchmark=> INSERT INTO has_check 
benchmark-> VALUES (-100);
ERROR:  ExecAppend: rejected due to CHECK constraint bt0
エラーメッセージに違反したCHECK制約名が表示されている.
ではテーブルclusterを作成してみる.テーブルを作成するSQLの構文を以下に示す.
CREATE TABLE テーブル名 (フィールド名 データ型,フィールド名 データ型,....);
テーブルclusterでは,idを主キーとする.
id INT PRIMARY KEY
さらにシステム名nameではNOT NULL制約をつけとく.
name TEXT NOT NULL
属性nameとnetworkでは,PostgreSQL独自のデータ型TEXTを使用している.これは可変調文字列である.このデータ型がない場合は,文字列長n(バイト)を指定してCHAR(n)などとする必要がある.上記を踏まえてSQLを作成した(create-table-cluster.sql).
CREATE TABLE cluster (
id INT PRIMARY KEY,
name TEXT NOT NULL,
cpunum INT,
cpuclock INT,
memory INT,
network TEXT);
これを実行してみる.
$ psql -h HOST benchmark < create-table-cluster.sql 
Password: 
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'cluster_pkey' for table 'cluster'
CREATE
メッセージにあるとおり,主キーをテーブルに設定すると,インデクスが自動的に作成される.作業結果を確認してみる.
benchmark=> \d cluster
        Table "cluster"
  Column  |  Type   | Modifiers 
----------+---------+-----------
 id       | integer | not null
 name     | text    | not null
 cpunum   | integer | 
 cpuclock | integer | 
 memory   | integer | 
 network  | text    | 
Primary key: cluster_pkey

benchmark=> select * from pg_indexes where tablename = 'cluster';
 tablename |  indexname   |                            indexdef                            
-----------+--------------+----------------------------------------------------------------
 cluster   | cluster_pkey | CREATE UNIQUE INDEX cluster_pkey ON "cluster" USING btree (id)
(1 row)

benchmark=> \z cluster
Access privileges for database "benchmark"
  Table  | Access privileges 
---------+-------------------
 cluster | 
(1 row)
最後の\zでは,テーブルへのアクセス権を確認している.SQL標準ではテーブルを作成しただけでは,作成者以外の一切のアクセスは許可されない.今回はやらないが,必要に応じてGRANTやREVOKEを実行する(具体的な方法は先の記事で書いた).
次にテーブルresult_netpipeを作成してみる.キーは二つのパラメータと計測したクラスタのcluster_idとする.
PRIMARY KEY(type, size, cluster_id)
さらにcluster_idは外部キーである.参照整合性制約をつけとく.
cluster_id INT
  CONSTRAINT cluster_id_reference REFERENCES cluster(id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
CONSTRAINTの後のcluster_id_referenceは制約名であり,エラーメッセージの出力の際に表示される.複数の制約を規定している際の問題解決時に重宝する.さらにONで続く節がある.これは,DELETE,UPDATE時に参照整合性制約に触れたときに,どういった操作(参照操作)を行なうかを設定している.ここではDELETE時にはエラーを出力するだけNO ACTION(デフォルト),UPDATE時にはカスケードして変更を反映するCASCADEを指定した.例えばcluster_idが1から10に変更されると,result_netpipeのcluster_idも1のものは10にUPDATEされる.参照操作を以下にまとめた.
NO ACTION
何もしない.エラー表示のみ
RESTRICT
エラーかどうかのチェックを事前に行なう点を除き,NO ACTIONと同じ(NO ACTIONはデータの変更後にエラーのチェックを行なう).PosgreSQLでは同じ実装らしい.
SET DEFAULT
外部キーである列にデフォルト値が設定されていれば,それを設定する.
SET NULL
NULLを設定する.
CASCADE
被参照キーが変更されたとき,参照側の値も変更する.
計測日exec_dateはDATE型にした.
exec_date DATE
SQL92では
'YYYY-MM-DD'
で指定するが,PostgreSQLでは他にもいろんな表記ができる.以下にその一部をあげておく.
'YYYYMMDD'
'YYYY MM DD'
'MM/DD/YYYY'
以上を踏まえて作成したSQLを以下に示す(create-table-result_netpipe.sql).
CREATE TABLE result_netpipe (
type TEXT,
size INT,
cluster_id INT
  CONSTRAINT cluster_id_reference REFERENCES cluster(id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
mbps FLOAT8 NOT NULL,
exec_date DATE,
PRIMARY KEY(type, size, cluster_id)
);
実行してみた.
$ psql -h thor benchmark < create-table-result_netpipe.sql
Password: 
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'result_netpipe_pkey' for table 'result_netpipe'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
外部キーを指定したので,参照整合性制約をチェックするトリガが作成されたようだ.
テーブルresult_netpipeと同様である.作成したSQL(create-table-result_linpack.sql).
CREATE TABLE result_linpack(
n INT,
nb INT,
cluster_id INT
  CONSTRAINT cluster_id_reference REFERENCES cluster(id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
gflops FLOAT8 NOT NULL,
exec_date DATE,
PRIMARY KEY(n, nb, cluster_id)
);
実行してみた.
$ psql -h thor benchmark < create-table-result_linpack.sql
Password: 
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'result_linpack_pkey' for table 'result_linpack'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
本来なら,前頁と本頁の間にデータモデルを具体化する作業が入るはずだが,今回は面倒なので前頁のer図にとどめている.そのため,CREATE TABLEが完了した今,ようやくデータモデルが明確になった.データモデルの作成は,直接的には更新不整合を論理的に排除するための活動ともいえるだろうか.更新不整合には以下がある.
修正不整合
修正するときになんか困る.あるデータを複数のレコードがもっていると,それを修正するときに全部を修正しなければならない.修正もれがあると,不整合が生じる.今回はresult_linpackとresult_netpipeのcluster_idが怪しいのだが,外部キーに指定し,参照整合性制約を課すことで修正不整合を防いでいる.
挿入不整合
挿入するときになんか困る.result_linpackやresult_netpipeでは計測してなくてもパラメータだけを入れたくなることがあるかもしれないが,cluster_idは外部キーなので,挿入できない...パラメータだけのレコードなんていらない.
削除不整合
削除するときになんか困る.clusterのレコードを消すと,該当するクラスタの計測結果がresult_linpackやresult_netpipeから消えてしまう.clusterのレコードを消すという行為自体がそういう意味なんだから,それはよいのだが,result_linpackやresult_netpipeのパラメータ情報も消えてしまう...挿入不整合と同様,パラメータだけのレコードなんていらない.だいたいせっかく計測したんだから,消すな.
論理的には更新不整合が発生しうるが,現実問題(あるいは運用問題)としては問題ないようだ.
matsu(C)
Since 2002
Mail to matsu