You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Emily Gouge <eg...@refractions.net> on 2024/08/19 02:26:14 UTC
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
delete from test.root;
--results in: ERROR 54038: Maximum depth of nested triggers was exceeded.
Re: Maximum depth of nested triggers was exceeded.
Posted by Emily Gouge <eg...@refractions.net>.
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.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
>> delete from test.root;
>> --results in: ERROR 54038: Maximum depth of nested triggers was exceeded.
>
>
Re: Maximum depth of nested triggers was exceeded.
Posted by Rick Hillegas <ri...@gmail.com>.
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.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
> delete from test.root;
> --results in: ERROR 54038: Maximum depth of nested triggers was exceeded.