Dear developers,
I opened a bug related to an infinite loop created by this example:
create table t1(id int, name varchar(1024), age int); create table t2(id int, age int);
create PROCEDURE p1(id int, age int) BEGIN insert into t2 values(id, age); END;
create PROCEDURE p1() BEGIN declare id int, age int; set id = 1; set age = 23; call p1(id, age); END;
create trigger test_0 after insert on t2 BEGIN ATOMIC insert into t1 values(1, 'monetdb', 24); call p1(); END;
insert into t2 values(0, 24);
AS you can see the procedure p1() will do an insertion in t2 which will fire the trigger test_0 again and all the process will be repeated again.
I decided to check what happen in another DBMS (I checked postgres because it is used as a reference). Looking to this example http://archives.postgresql.org/pgsql-general/2003-07/msg01530.php
It seems postgres has the same problem, but should we consider this a problem or bug?
SQL with procedures (function...) can almost be considered a programming language so it is not our responsibility to kill loops created by users SQL code. If an user see a infinite loop he has to construct the schema in such way that the infinite loops are avoided.
Another point is the restriction that we created for recursive triggers (we only allow at least one call for each trigger in each statement execution) which I think it is not correct. Imagine that the user has an "if clause" to recursively fire a trigger 10 times and after that 10 times the trigger is not fired anymore. Should we restrict this example in our system? I do not think so. However, If the clause check is wrong and an infinite loop is created it is the user who has to correct his schema.
The big conclusion of the day is: More programing language features do we offer to the user less control we have over his SQL code.
If everyone agree I will close the bug infinite loop and remove the loop check for triggers.
Regards, Romulo