2008年9月 5日 (金)

トリガーカスケード制限 #2

さて、前回のつづき。

今回は無限ループする(実際にはしませんけど)させずにトリガーカスケード制現値を確認する。(前回の実験でも十分なんですが。。)

環境は前回と同じです。

シナリオ1

32テーブルに1トリガー/表で32のINSERTトリガーを定義し正しく動作することを確認する。(カスケード制現値が49のようなのでトリガーの32連鎖では問題ないはず!)

33テーブルを作成しておく。

SCOTT> l
1 begin
2 for seq in 1..33 loop
3 execute immediate 'create table t'||to_char(seq,'FM09')|| ' (seq# number)';
4 end loop;
5* end;
SCOTT> /

PL/SQLプロシージャが正常に完了しました。

SCOTT> select table_name from user_tables where table_name like 'T%' order by table_name;

TABLE_NAME
------------------------------
T01
T02
T03
T04

・・・・中略・・・・

T30
T31
T32
T33

33行が選択されました。

SCOTT>

作成した各表に1つづつ32のトリガーを定義する。
ちなみに、イメージとしては親表にINSERT文が発行されるとトリガーが起動し子表へデータをINSERTする。以下、子表へのINSERT文でトリガーが起動し孫表へINSERT....の繰り返しでトリガーを32回起動させる。

SCOTT> l
1 declare
2 ddlstr constant varchar2(1000)
3 := 'create or replace trigger trg__$$seq$$__'
4 || ' after insert on t__$$seq$$__ '
5 || ' for each row '
6 || 'begin'
7 || ' insert into t__$$nextSeq$$__ values(:new.seq# + 1); '
8 || 'end;';
9 wkstr varchar2(2000);
10 begin
11 for seq in 1..32 loop
12 wkstr := replace(
13 replace(
14 ddlstr,'__$$seq$$__',to_char(seq,'FM09')
15 ),
16 '__$$nextSeq$$__',
17 to_char(seq+1,'FM09')
18 );
19 -- dbms_output.put_line(wkstr);
20 execute immediate wkstr;
21 end loop;
22* end;
SCOTT> /

PL/SQLプロシージャが正常に完了しました。

SCOTT>
SCOTT> l
1 select text
2 from all_source
3 where name like 'TRG__'
4 and type = 'TRIGGER'
5* order by name,line
SCOTT> /

TEXT
--------------------------------------------------------------------------------
trigger trg01 after insert on t01 for each row begin insert into t02 values(
:new.seq# + 1); end;

trigger trg02 after insert on t02 for each row begin insert into t03 values(
:new.seq# + 1); end;

trigger trg03 after insert on t03 for each row begin insert into t04 values(
:new.seq# + 1); end;

・・・・中略・・・・

trigger trg30 after insert on t30 for each row begin insert into t31 values(
:new.seq# + 1); end;

trigger trg31 after insert on t31 for each row begin insert into t32 values(
:new.seq# + 1); end;

trigger trg32 after insert on t32 for each row begin insert into t33 values(
:new.seq# + 1); end;


32行が選択されました。

SCOTT>

実行!

SCOTT> insert into t01 values(1);

1行が作成されました。

SCOTT>

正常にINSERTできたようですね。

SCOTT> set serveroutput on size 10000
SCOTT> l
1 declare
2 seq# number;
3 begin
4 for i in 1..33 loop
5 execute immediate 'select * from t'||to_char(i,'fm09') into seq#;
6 dbms_output.put_line('T'||to_char(i,'fm09')||':'||to_char(seq#));
7 end loop;
8* end;
SCOTT> /
T01:1
T02:2
T03:3

・・・・中略・・・・

T31:31
T32:32
T33:33

PL/SQLプロシージャが正常に完了しました。

SCOTT>

シナリオ2

33テーブルに1トリガー/表で33のINSERTトリガーを定義し正しく動作することを確認する。(カスケード制現値が49のようなのでトリガーの33連鎖でも問題ないはず!)


さきほどのデータはじゃまなのでロールバックしておく。

SCOTT> rollback;

ロールバックが完了しました。


テーブルとトリガーを各1つ追加作成し、トリガーを33連鎖させる。

SCOTT> create table t34 as select * from t33;

表が作成されました。

SCOTT> l
1* create or replace trigger trg33 after insert on t33 for each row begin insert into t34 values(:new.seq# + 1); end;
SCOTT>

SCOTT> l
1 select text
2 from all_source
3 where name like 'TRG__'
4 and type = 'TRIGGER'
5* order by name,line
SCOTT> /

TEXT
--------------------------------------------------------------------------------
trigger trg01 after insert on t01 for each row begin insert into t02 values(
:new.seq# + 1); end;

trigger trg02 after insert on t02 for each row begin insert into t03 values(
:new.seq# + 1); end;


・・・・中略・・・・


trigger trg32 after insert on t32 for each row begin insert into t33 values(
:new.seq# + 1); end;

trigger trg33 after insert on t33 for each row begin insert into t34 values(:new
.seq# + 1); end;


33行が選択されました。

SCOTT>


どうなりますか!。実行してみる。

SCOTT> insert into t01 values(1);

1行が作成されました。

SCOTT>

トリガーの33連鎖は無事終了。

SCOTT> l
1 declare
2 seq# number;
3 begin
4 for i in 1..34 loop
5 execute immediate 'select * from t'||to_char(i,'fm09') into seq#;
6 dbms_output.put_line('T'||to_char(i,'fm09')||':'||to_char(seq#));
7 end loop;
8* end;
SCOTT> /
T01:1
T02:2
T03:3

・・・・中略・・・・

T31:31
T32:32
T33:33
T34:34

PL/SQLプロシージャが正常に完了しました。

SCOTT>


シナリオ3

50テーブルに1トリガー/表でINSERTトリガーを定義し正しく動作することを確認する。(カスケード制現値が49のようなのでトリガーの50連鎖ではエラーがになるはず!)

SCOTT> rollback;

ロールバックが完了しました。

SCOTT> l
1 begin
2 for i in 35..52 loop
3 execute immediate 'create table t'||to_char(i,'fm09')|| ' as select * from t34';
4 end loop;
5* end;
SCOTT> /

PL/SQLプロシージャが正常に完了しました。

SCOTT>
SCOTT> l
1 declare
2 ddlstr constant varchar2(1000)
3 := 'create or replace trigger trg__$$seq$$__'
4 || ' after insert on t__$$seq$$__ '
5 || ' for each row '
6 || 'begin'
7 || ' insert into t__$$nextSeq$$__ values(:new.seq# + 1); '
8 || 'end;';
9 wkstr varchar2(2000);
10 begin
11 for seq in 34..50 loop
12 wkstr := replace(
13 replace(
14 ddlstr,'__$$seq$$__',to_char(seq,'FM09')
15 ),
16 '__$$nextSeq$$__',
17 to_char(seq+1,'FM09')
18 );
19 -- dbms_output.put_line(wkstr);
20 execute immediate wkstr;
21 end loop;
22* end;
SCOTT> /

PL/SQLプロシージャが正常に完了しました。

SCOTT>
SCOTT> l
1 select text
2 from all_source
3 where name like 'TRG__'
4 and type = 'TRIGGER'
5* order by name,line
SCOTT> /

TEXT
--------------------------------------------------------------------------------
trigger trg01 after insert on t01 for each row begin insert into t02 values(
:new.seq# + 1); end;

trigger trg02 after insert on t02 for each row begin insert into t03 values(
:new.seq# + 1); end;

trigger trg03 after insert on t03 for each row begin insert into t04 values(
:new.seq# + 1); end;

・・・・中略・・・・

trigger trg48 after insert on t48 for each row begin insert into t49 values(
:new.seq# + 1); end;

trigger trg49 after insert on t49 for each row begin insert into t50 values(
:new.seq# + 1); end;

trigger trg50 after insert on t50 for each row begin insert into t51 values(
:new.seq# + 1); end;


50行が選択されました。

SCOTT>


では実行!

SCOTT> insert into t01 values(1);
insert into t01 values(1)
*
行1でエラーが発生しました。:
ORA-00036: 再帰的SQLレベルの最大値(50)を超えました ORA-06512:
"SCOTT.TRG50", 行1
ORA-04088: トリガー'SCOTT.TRG50'の実行中にエラーが発生しました ORA-06512:
"SCOTT.TRG49", 行1
ORA-04088: トリガー'SCOTT.TRG49'の実行中にエラーが発生しました ORA-06512:
"SCOTT.TRG48", 行1
ORA-04088: トリガー'SCOTT.TRG48'の実行中にエラーが発生しました ORA-06512:
"SCOTT.TRG47", 行1
ORA-04088: トリガー'SCOTT.TRG47'の実行中にエラーが発生しました ORA-06512:
"SCOTT.TRG46", 行1
ORA-04088: トリガー'SCOTT.TRG46'の実行中にエラーが発生しました ORA-06512:
"SCOTT.TRG45", 行1
ORA-04088: トリガー'SCOTT.TRG45'の実行中にエラーが発生しました ORA-06512:
"SCOTT.TRG44", 行1
ORA-04088: トリガー'SCOTT.TRG44'の実行中にエラーが発生しました ORA-06512:
"SCOTT.TRG43", 行1
ORA-04088: トリガー'SCOTT.TRG43'の実行中にエラーが発生しました ORA-06512:
"SCOTT.TRG42", 行1
ORA-04088: トリガ


SCOTT>

おおお〜〜〜。 :) でましたね。トリガーを50連鎖させたところで制限を超えたようだ。

最後に、トリガーを49連鎖させることが可能なことを確認しておく。

シナリオ4

49テーブルに1トリガー/表で49のINSERTトリガーを定義し正しく動作することを確認する。(カスケード制現値が49のようなのでトリガーの49連鎖は正常に実行できるはず!)

SCOTT> alter trigger trg50 disable;

トリガーが変更されました。

SCOTT>
SCOTT> l
1 select count(src.text)
2 from
3 all_source src join user_triggers trg
4 on src.name = trg.trigger_name
5 where
6 src.name like 'TRG__'
7 and src.type = 'TRIGGER'
8 and trg.status = 'ENABLED'
9 order by
10 src.name,
11* src.line
SCOTT> /

COUNT(SRC.TEXT)
---------------
49

SCOTT>
SCOTT> rollback;

ロールバックが完了しました。

SCOTT> insert into t01 values(1);

1行が作成されました。

SCOTT>

ということで、Linux(32bit)環境ではトリガーカスケード最大値は49ということになる。

Windowsや32bit/64bitで違いはあるのだろうか??? 違いが無かったらマニュアルが間違っているということか?!・・・・・・。別途調査予定。

| | コメント (0) | トラックバック (0)

2008年9月 3日 (水)

トリガーカスケード制限 #1

やっと時間が取れるようになったのでしばらくは純粋にデータベースと戯れることができそうだ。w

ということで、キムラデービーブログ:徹底比較!! PostgreSQL vs MySQLパート2 with 勝手に vs Firebird (13)に面白そうなこと(以下の引用部分)が書いてあったので実際に試してみる事にした。

*2:  カスケードは、例えばOracleは32個に制限されており、それ以下に設定することも可能です。

 これは例えば、あるトリガーから発せられたSQLによりトリガーが発せらるような場合で、ヘタすると、それが無限ループになってしまう。それを防ぐような形で制限されている(のだと思います。それか、もしかしたら実装上の制限かもしれません)

 勇気ある人は、無限にお互いを呼び合うようなトリガーを書いて、ご自分のプラットフォームで実行させてみてください。そして結果を木村まで知らせてください(^_^;)


環境はOracle11g EE for Linux x86/CentOS5という組み合わせです。詳しい環境は以前の記事をご参考に。

Oracle de XMLDB #6 - Linux x86版再び。
Mac de Ruby on Rails - #2 - Oracleと遊ぶ #1

では実験開始。

今回はある表に定義したINSERTトリガーをカスケードさせ無限に起動する状況を作ってみた。

1)まず、トリガーを定義する以下のような単純な表を作成。

SCOTT> desc master
名前 NULL? 型
----------------------------------------- -------- ----------------------------
SEQ# NUMBER


2)定義するトリガーはmaster表へのINSERT文で起動しシーケンスから連番を取得し
  トリガーが定義されているmaster表へ再度INSERT文を発行する。(無限ループするカスケードを実装した)

SCOTT> select text      
2 from all_source
3 where name = 'INS_CASCADE_TEST'
4 order by line;

TEXT
--------------------------------------------------------------------------------
TRIGGER INS_CASCADE_TEST
after insert on master
begin
insert into master values(master_seq.nextval);
end;


上記トリガーを起動すれば、Oracleのマニュアル:論理データベースの制限のトリガー・カスケード制限に記載されている最大値に達すればエラーになるはず。無限ループするカスケードを実装したとしても実行途中でエラーとなり停止するはずだ。
マニュアルにはプラットーフォーム依存だが、通常は32と記載されている。私の記憶違いでなければ、この値はOracle7の頃から変っていない。


3)では実行!

・実行前のシーケンス値の確認!。

SCOTT> select last_number
2 from user_sequences
3 where sequence_name = 'MASTER_SEQ';

LAST_NUMBER
-----------
1


・実行!

SCOTT> insert into master values(-100);
insert into master values(-100)
*
行1でエラーが発生しました。:
ORA-00036: 再帰的SQLレベルの最大値(50)を超えました ORA-06512:
"SCOTT.INS_CASCADE_TEST", 行2
ORA-04088: トリガー'SCOTT.INS_CASCADE_TEST'の実行中にエラーが発生しました ORA-06512:
"SCOTT.INS_CASCADE_TEST", 行2
ORA-04088: トリガー'SCOTT.INS_CASCADE_TEST'の実行中にエラーが発生しました ORA-06512:
"SCOTT.INS_CASCADE_TEST", 行2
ORA-04088: トリガー'SCOTT.INS_CASCADE_TEST'の実行中にエラーが発生しました ORA-06512:
"SCOTT.INS_CASCADE_TEST", 行2
ORA-04088: トリガー'SCOTT.INS_CASCADE_TEST'の実行中にエラーが発生しました ORA-06512:
"SCOTT.INS_CASCADE_TEST", 行2
ORA-04088: トリガー'SCOTT.INS_CASCADE_TEST'の実行中にエラーが発生しました ORA-06512:
"SCOTT.INS_CASCADE_TEST", 行2
ORA-04088: トリガー'SCOTT.INS_CASCADE_TEST'の実行中にエラーが発生しました ORA-06512:
"SCOTT.INS_CASCADE_TEST", 行2
ORA-04088: トリガー'SCOTT.INS_CASCADE_TEST'の実行中にエラーが発生しました ORA-06512:


予想通りエラーにはなったもののプラットフォーム依存と記載されている通り?、Linux(32bit)環境ではカスケードできる最大値は32ではなく、49というコンピュータの世界では半端な値になっているようだ。LinuxではなくWindowsでは32なのだろうか????

SCOTT> 
SCOTT> select last_number
2 from user_sequences
3 where sequence_name = 'MASTER_SEQ';

LAST_NUMBER
-----------
51

SCOTT>

次回は、linux(32bit)環境でトリガーカスケード制限が49であるということを無限ループするトリガーを使わずに確認してみることにする。

カスケードの制限値を超えた場合にどうなるのかなんて確認したことなど一度も無かったので面白い発見だ! :)

| | コメント (0) | トラックバック (1)