Re: Maximum depth of nested triggers was exceeded.
Hi Rick, Thanks for the information. I will log a bug report when I get the chance in case somebody else runs into a similar issue, but it's good to know to not expect any immediate changes. Thanks, Emily On 2024-08-19 8:25 a.m., Rick Hillegas wrote: At first glance, it does not appear that there is any recursion involved here. However, as implemented, preceding triggers are still active when later triggers begin executing. This causes the active trigger stack to exceed its limited size. I am not an expert in this part of the code and I don't know how well Derby's trigger stack models the behavior of the SQL Standard's understanding of the trigger stack. Some of these complexities are discussed at https://issues.apache.org/jira/browse/DERBY-1261 You are welcome to log a bug. As a practical matter, I don't think it will be addressed for a long time. Another bug which could be logged would be to make the size of the trigger stack user-configurable. That bug would be easier to address. In the near term, the only workaround I can think of would be to build your own version of Derby, setting DB2_MAX_TRIGGER_RECURSION to a higher number than 16 in org/apache/derby/shared/common/reference/Limits.java Hope this helps, -Rick On 8/18/24 7:26 PM, Emily Gouge wrote: Hi, I have a single table which is referenced by more than 16 other tables. These 16+ children tables all have "on delete set null" foreign key constraints and update triggers which log changes to a change log table. When I try to delete from the root table I am running into a "ERROR 54038: Maximum depth of nested triggers was exceeded". I'm a bit confused because I don't think these are obviously nested. Any thoughts or suggestions on resolving this? Derby Version: 10.17.1.0 Below is a simple set of SQL statements that reproduces the issue I am having. Thanks, Emily create schema test; --single root table create table test.root(id integer primary key); --child tables with foreign keys create table test.child1(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child2(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child3(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child4(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child5(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child6(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child7(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child8(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child9(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child10(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child11(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child12(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child13(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child14(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child15(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child16(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child17(keyid integer primary key, id integer references test.root(id) on delete set null); -- logging table and triggers for logging create table test.log(id integer); create trigger trg_child1 after update on test.child1 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child2 after update on test.child2 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child3 after update on test.child3 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child4 after update on test.child4 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child5 after update on test.child5 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child6 after update on test.child6 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child7 after update on test.child7 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child8 after update on test.
Re: Maximum depth of nested triggers was exceeded.
At first glance, it does not appear that there is any recursion involved here. However, as implemented, preceding triggers are still active when later triggers begin executing. This causes the active trigger stack to exceed its limited size. I am not an expert in this part of the code and I don't know how well Derby's trigger stack models the behavior of the SQL Standard's understanding of the trigger stack. Some of these complexities are discussed at https://issues.apache.org/jira/browse/DERBY-1261 You are welcome to log a bug. As a practical matter, I don't think it will be addressed for a long time. Another bug which could be logged would be to make the size of the trigger stack user-configurable. That bug would be easier to address. In the near term, the only workaround I can think of would be to build your own version of Derby, setting DB2_MAX_TRIGGER_RECURSION to a higher number than 16 in org/apache/derby/shared/common/reference/Limits.java Hope this helps, -Rick On 8/18/24 7:26 PM, Emily Gouge wrote: Hi, I have a single table which is referenced by more than 16 other tables. These 16+ children tables all have "on delete set null" foreign key constraints and update triggers which log changes to a change log table. When I try to delete from the root table I am running into a "ERROR 54038: Maximum depth of nested triggers was exceeded". I'm a bit confused because I don't think these are obviously nested. Any thoughts or suggestions on resolving this? Derby Version: 10.17.1.0 Below is a simple set of SQL statements that reproduces the issue I am having. Thanks, Emily create schema test; --single root table create table test.root(id integer primary key); --child tables with foreign keys create table test.child1(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child2(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child3(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child4(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child5(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child6(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child7(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child8(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child9(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child10(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child11(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child12(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child13(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child14(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child15(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child16(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child17(keyid integer primary key, id integer references test.root(id) on delete set null); -- logging table and triggers for logging create table test.log(id integer); create trigger trg_child1 after update on test.child1 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child2 after update on test.child2 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child3 after update on test.child3 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child4 after update on test.child4 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child5 after update on test.child5 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child6 after update on test.child6 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child7 after update on test.child7 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child8 after update on test.child8 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child9 after update on test.child9 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child10 after update on test.child1
Maximum depth of nested triggers was exceeded.
Hi, I have a single table which is referenced by more than 16 other tables. These 16+ children tables all have "on delete set null" foreign key constraints and update triggers which log changes to a change log table. When I try to delete from the root table I am running into a "ERROR 54038: Maximum depth of nested triggers was exceeded". I'm a bit confused because I don't think these are obviously nested. Any thoughts or suggestions on resolving this? Derby Version: 10.17.1.0 Below is a simple set of SQL statements that reproduces the issue I am having. Thanks, Emily create schema test; --single root table create table test.root(id integer primary key); --child tables with foreign keys create table test.child1(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child2(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child3(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child4(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child5(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child6(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child7(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child8(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child9(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child10(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child11(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child12(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child13(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child14(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child15(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child16(keyid integer primary key, id integer references test.root(id) on delete set null); create table test.child17(keyid integer primary key, id integer references test.root(id) on delete set null); -- logging table and triggers for logging create table test.log(id integer); create trigger trg_child1 after update on test.child1 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child2 after update on test.child2 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child3 after update on test.child3 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child4 after update on test.child4 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child5 after update on test.child5 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child6 after update on test.child6 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child7 after update on test.child7 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child8 after update on test.child8 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child9 after update on test.child9 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child10 after update on test.child10 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child11 after update on test.child11 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child12 after update on test.child12 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child13 after update on test.child13 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child14 after update on test.child14 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child15 after update on test.child15 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child16 after update on test.child16 REFERENCING old as old for each row insert into test.log values(old.keyid); create trigger trg_child17 after update on test.child17 REFERENCING old as old for each row insert into test.log values(old.keyid); -- no data is needed to create the problem delet
Re: Maximum depth of nested triggers was exceeded.
Rami Ojares / PDF-Comics Oy writes: > Yes, as I expected. > If I execute those same statements from a separate connection then > everything works as expected. > No more "Maximum depth of nested triggers was exceeded." > So this is definately a bug. Thanks for bringing this to our attention! You can further help speed up fixing this bug by filing a JIRA issue. If possible, attaching a repro will help even more. Dag
Re: Maximum depth of nested triggers was exceeded.
Yes, as I expected. If I execute those same statements from a separate connection then everything works as expected. No more "Maximum depth of nested triggers was exceeded." So this is definately a bug. I am using currently 10.5.3.1 - rami Hi, Now I have confirmed a following (rather alarming) issue. I have a before trigger on inserting a row in a table. That trigger either throws an exception or returns 0. Now on the client side a create a connection (autoCommit = true) And I start inserting rows all of which result in an exception thrown by the before trigger. In my trigger function I print to console each time the trigger is entered. For the first 16 rows the trigger is entered and throws an exception (as expected). But after that the trigger is not entered and "Maximum depth of nested triggers was exceeded." exception is thrown for all the rest of the triggers. Derby reference says that "The maximum trigger recursion depth is 16." But my trigger does not cause other triggers to fire. The insert statements are separate transactions but created from the same connection. It seems that derby starts adding the trigger recursion counter for completely separate statements?!? I have to test now if I open a separate connection for each statement what is going to happen. Will report soon. - rami
Maximum depth of nested triggers was exceeded.
Hi, Now I have confirmed a following (rather alarming) issue. I have a before trigger on inserting a row in a table. That trigger either throws an exception or returns 0. Now on the client side a create a connection (autoCommit = true) And I start inserting rows all of which result in an exception thrown by the before trigger. In my trigger function I print to console each time the trigger is entered. For the first 16 rows the trigger is entered and throws an exception (as expected). But after that the trigger is not entered and "Maximum depth of nested triggers was exceeded." exception is thrown for all the rest of the triggers. Derby reference says that "The maximum trigger recursion depth is 16." But my trigger does not cause other triggers to fire. The insert statements are separate transactions but created from the same connection. It seems that derby starts adding the trigger recursion counter for completely separate statements?!? I have to test now if I open a separate connection for each statement what is going to happen. Will report soon. - rami
