[Monetdb-developers] Infinite loop...

Romulo Goncalves R.A.Goncalves at cwi.nl
Wed May 23 17:08:53 CEST 2007


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




More information about the developers-list mailing list