You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
C:\MIX\firebird\fb30>isql T0.FDB
Database: T0.FDB
SQL> show version;
ISQL Version: WI-T3.0.0.31557 Firebird 3.0 Beta 2
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T3.0.0.31557 Firebird 3.0 Beta 2"
on disk structure version 12.0
SQL> show table;
There are no tables in this database
SQL>
SQL> in z.sql;
Statement failed, SQLSTATE = 54001
Dynamic SQL Error
-Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256
After line 0 in file z.sql
Content of `z.sql`:
with recursive
c as (
select
rc.rdb$relation_name child_tab
,rc.rdb$constraint_name child_fk
,ru.rdb$const_name_uq parent_uk
,rp.rdb$relation_name parent_tab
from rdb$relation_constraints rc
join rdb$ref_constraints ru on
rc.rdb$constraint_name = ru.rdb$constraint_name
and rc.rdb$constraint_type = 'FOREIGN KEY'
join rdb$relation_constraints rp
on ru.rdb$const_name_uq = rp.rdb$constraint_name
where rc.rdb$relation_name <> rp.rdb$relation_name
)
,d as(
select
0 i
,child_tab
,child_fk
,parent_uk
,parent_tab
from c c0
where not exists( select * from c cx where cx.parent_tab= c0.child_tab )
union all
select
d\.i\+1
,c\.child\_tab
,c\.child\_fk
,c\.parent\_uk
,c\.parent\_tab
from d
join c on d\.parent\_tab = c\.child\_tab
)
,e as(
select
i
,child_tab
,child_fk
,parent_uk
,parent_tab
,(select max(i) from d) as mi
from d
)
,f as(
select distinct
0 i
,child_tab
from e where i=0
UNION DISTINCT
select
1
,child\_tab
from \(select child\_tab from e where i\>0 order by i\)
UNION DISTINCT
select
2
,parent\_tab
from e
where i=mi
)
,t as(
select
rt.rdb$trigger_name trg_name -- f.child_tab, rt.rdb$trigger_name, rt.rdb$trigger_type
from f
join rdb$triggers rt on f.child_tab = rt.rdb$relation_name
where rt.rdb$system_flag=0 and rt.rdb$trigger_inactive=0
)
select 'alter trigger '||trim(trg_name)||' inactive' sql_expr
from t
union all
select 'delete from '||trim(child_tab)
from f
union all
select 'alter trigger '||trim(trg_name)||' active'
from t
;
Interesting, v3 generates 258 contexts for this query while v2.5 generated only 225. I'm not sure this is a bug per se, maybe some implementation artifact. But it surely affects backward compatibility, so it's worth investigating deeper.
summary: Can`t prepare complex query ("Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256") in 3.0 but success do that in 2.5 => Error while preparin a complex query ("Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256")
summary: Error while preparin a complex query ("Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256") => Error while preparing a complex query ("Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256")
Submitted by: @pavel-zotov
Doing on EMPTY database:
C:\MIX\firebird\fb30>isql T0.FDB
Database: T0.FDB
SQL> show version;
ISQL Version: WI-T3.0.0.31557 Firebird 3.0 Beta 2
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T3.0.0.31557 Firebird 3.0 Beta 2"
on disk structure version 12.0
SQL> show table;
There are no tables in this database
SQL>
SQL> in z.sql;
Statement failed, SQLSTATE = 54001
Dynamic SQL Error
-Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256
After line 0 in file z.sql
Content of `z.sql`:
with recursive
c as (
select
rc.rdb$relation_name child_tab
,rc.rdb$constraint_name child_fk
,ru.rdb$const_name_uq parent_uk
,rp.rdb$relation_name parent_tab
from rdb$relation_constraints rc
join rdb$ref_constraints ru on
rc.rdb$constraint_name = ru.rdb$constraint_name
and rc.rdb$constraint_type = 'FOREIGN KEY'
join rdb$relation_constraints rp
on ru.rdb$const_name_uq = rp.rdb$constraint_name
where rc.rdb$relation_name <> rp.rdb$relation_name
)
,d as(
select
0 i
,child_tab
,child_fk
,parent_uk
,parent_tab
from c c0
where not exists( select * from c cx where cx.parent_tab= c0.child_tab )
)
,e as(
select
i
,child_tab
,child_fk
,parent_uk
,parent_tab
,(select max(i) from d) as mi
from d
)
,f as(
select distinct
0 i
,child_tab
from e where i=0
)
,t as(
select
rt.rdb$trigger_name trg_name -- f.child_tab, rt.rdb$trigger_name, rt.rdb$trigger_type
from f
join rdb$triggers rt on f.child_tab = rt.rdb$relation_name
where rt.rdb$system_flag=0 and rt.rdb$trigger_inactive=0
)
select 'alter trigger '||trim(trg_name)||' inactive' sql_expr
from t
union all
select 'delete from '||trim(child_tab)
from f
union all
select 'alter trigger '||trim(trg_name)||' active'
from t
;
PS. No such error on WI-V2.5.3.26730.
Commits: 4454a3f FirebirdSQL/fbt-repository@68fbcfa
The text was updated successfully, but these errors were encountered: