テーブル定義の変更と依存関係

テーブル定義を変更しようとしたら依存関係でハマったので、ちょっとまとめておこう。

単純なcreate table文でも依存関係は発生する。pg_typeにテーブル名と同じ"型"を登録するからだ。依存関係の情報はpg_dependから得られる。

ishida=> CREATE TABLE tbl1 (fld1 integer);
CREATE TABLE
ishida=>
 SELECT classid::regclass, * FROM pg_depend d
  WHERE EXISTS
       (SELECT * FROM pg_class c
         WHERE c.oid = d.refobjid AND c.relname = 'tbl1');
-[ RECORD 1 ]--------------
classid     | pg_type
classid     | 1247
objid       | 4238396
objsubid    | 0
refclassid  | 1259
refobjid    | 4238395
refobjsubid | 0
deptype     | i

ishida=> ALTER TABLE tbl1
           ADD CONSTRAINT tbl1_pkey PRIMARY KEY (fld1);
(sql省略)
-[ RECORD 1 ]--------------
classid     | pg_type
classid     | 1247
objid       | 4238396
objsubid    | 0
refclassid  | 1259
refobjid    | 4238395
refobjsubid | 0
deptype     | i
-[ RECORD 2 ]--------------
classid     | pg_constraint
classid     | 16386
objid       | 4238398
objsubid    | 0
refclassid  | 1259
refobjid    | 4238395
refobjsubid | 1
deptype     | a

deptypeには、n(NORMAL), a(AUTO), i(INTERNAL), p(PIN)のいずれかが入る。NORMALな依存関係はDROP CASCADEでのみ削除できる。AUTOとINTERNALは親をDROPすると自動的に削除される。AUTOは子だけを個別に削除することも可能だ。 ここまでの例ではAUTOとINTERNALしかないので、DROP TABLE tbl1;は問題無く実行できる。 ではtbl1.fld1を外部キーとして参照する別のテーブルを作ってみよう。この場合、tbl1とtbl2の間に依存関係ができるのではなく、tbl1とtbl2の外部キー制約の間に依存関係ができる。(つまり、DROP TABLE tbl1 CASCADE;すると、tbl2が削除されるのでははく、tbl2の外部キー制約が削除される)。

ishida=> CREATE TABLE tbl1 (fld1 integer PRIMARY KEY);
ishida=> CREATE TABLE tbl2 (fld2 integer, FOREIGN KEY (fld2) 
           REFERENCES tbl1(fld1));
(sql省略)
-[ RECORD 1 ]--------------
classid     | pg_type
classid     | 1247
objid       | 4238405
objsubid    | 0
refclassid  | 1259
refobjid    | 4238404
refobjsubid | 0
deptype     | i
-[ RECORD 2 ]--------------
classid     | pg_constraint
classid     | 16386
objid       | 4238407
objsubid    | 0
refclassid  | 1259
refobjid    | 4238404
refobjsubid | 1
deptype     | a
-[ RECORD 3 ]--------------
classid     | pg_constraint
classid     | 16386
objid       | 4238410
objsubid    | 0
refclassid  | 1259
refobjid    | 4238404
refobjsubid | 1
deptype     | n

ishida=> DROP TABLE tbl1;
NOTICE:  constraint $1 on table tbl2 depends on table tbl1
ERROR:  cannot drop table tbl1 because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

(続く、、、かも）

依存関係の追跡
pg_depend