firebird
firebird copied to clipboard
FirebirdSQL
Remove context limit for stored procedures/functions/triggers as well as for user SQL queries [CORE809]
Submitted by: marcgeldon (marcgeldon)
Is duplicated by CORE2694
Votes: 12
SFID: 1412839# Submitted By: marcgeldon
Have a look at the summary. If a stored procedure / trigger is too complex, you'll get the following error:
Invalid token. Invalid request BLR at offset xxxx. "context not defined (BLR error)" or "Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256"
Every table/procedure reference means a "context" and their total count is limited by 256 contexts. Removing or extending this limit would allow more complex PSQL code to be developed.
Commented by: Alice F. Bird (firebirds)
Date: 2006-01-25 13:29 Sender: dimitr Logged In: YES user_id=61270
Size limit for SPs/triggers and limit of available record source contexts are two different ones. However, I agree that both should be removed/extended.
Modified by: @pcisar
issuetype: New Feature [ 2 ] => Improvement [ 4 ]
assignee: Dmitry Yemanov [ dimitr ]
SF_ID: 1412839 =>
Modified by: @dyemanov
Component: Engine [ 10000 ]
SF_ID: 1412839 =>
Modified by: @pcisar
Workflow: jira [ 10833 ] => Firebird [ 15269 ]
Modified by: @dyemanov
description: SFID: 1412839# Submitted By: marcgeldon
Have a look at the summary. If a stored procedure / trigger is too long, you'll get the following error:
Invalid token. Invalid request BLR at offset xxxx. context not defined (BLR error).
Best wishes,
Marc Geldon
=>
SFID: 1412839# Submitted By: marcgeldon
Have a look at the summary. If a stored procedure / trigger is too complex, you'll get the following error:
Invalid token. Invalid request BLR at offset xxxx. "context not defined (BLR error)" or "Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256"
Every table/procedure reference means a "context" and their total count is limited by 256 contexts. Removing or extending this limit would allow more complex PSQL code to be developed.
summary: Remove limit for size of stored procedures / triggers => Remove context limit for stored procedures / triggers
Commented by: @abzalov
Due to the below issuies are closed, does it mean that in Firebird v3.0 context limit for stored procedures/triggers are removed or extended? - CORE4684 - CORE4710
Commented by: @dyemanov
No, v3.0 has the same limit of 256 contexts. However, it has some minor improvements, in particular it generates less contexts for N-way unions.
Modified by: @dyemanov
Fix Version: 4.0 Beta 1 [ 10750 ]
Fix Version: 4.0 Alpha 1 [ 10731 ] =>
Commented by: Volker Rehn (vr2_s18)
This can close a gap and provide a method to bulk load within user/attachment/transaction contexts. A simple example would be an insert script within a dynamically generated and executed execute block. This does not have the restrictions of external tables. It won't be as fast as external tables, but those are just too restrictive in many cases.
Why is there a limit for update/insert/delete, but I can set as many stored procedures as I want? Or is there a limit there too? I tried to create a stored procedure with several updates and I was able to execute the stored procedure within a execute block more than 1000 times.
The limit applies to the BLR (byte code) where every table reference is encoded with a single-byte number. Thus the limit is static and does not affect how many times the procedure is executed.
And how is it possible to execute an unlimited number of stored procedures within an execute block?
- BLR is per-procedure, so one procedure call cannot have more than 255 table references, but many different calls do not share this limit.
- EXECUTE PROCEDURE does not create a context, so EXECUTE BLOCK may have lots of them. However, SELECT FROM PROCEDURE creates a context, so your EXECUTE BLOCK cannot have more than 255 SELECTs from procedures.
Is this really hard to have blr with 2, 3, 4 bytes context number instead of current one? Is Blr versioned?
Thanks for reminder, I believe we should schedule this improvement for v6.
This shouldn't be very hard, I've attempted it once. BLR is indeed versioned and we may just raise the BLR version number instead of duplicating all BLR verbs that contain stream number(s). But I'd prefer to use new BLR version only if the code really overflows the 255 limit. This would make the usual code backward compatible and down-gradable if required.
Yes, backward compatibility is an important consideration. I'm not sure how challenging it will be to check for a 256 context overflow at the beginning of BLR generating. However, if it's a difficult task, you could introduce a flag in, for example, RDB$Database, allowing users to switch from the old to the new system. All newly compiled objects would then use the new version of BLR. This could be implemented through a command or even during the restore process with a switch. Please consider my comment as just a suggestion; the more perspectives we have, the better, even if some ideas might not be ideal.