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.

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.

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?

1. BLR is per-procedure, so one procedure call cannot have more than 255 table references, but many different calls do not share this limit.
2. 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.