[#CORE-1544] RDB$procedures generator overflow - Firebird RDBMS Issue Tracker

Firebird Core

RDB$procedures generator overflow

Created: 26/Oct/07 12:52 PM Updated: 29/Jun/11 09:18 AM

Component/s:

None

Affects Version/s:

2.0.0, 1.5.4, 2.0.1, 2.1 Alpha 1, 2.1 Beta 1, 2.0.2, 2.0.3, 1.5.5

Fix Version/s:

2.5 Beta 1

Time Tracking:

Not Specified

Issue Links:

Relate

This issue is related to:

~~QA-332~~ Test for CORE-1544

Target:	2.5 Alpha 1
Planning Status:	Considered for inclusion

Description

« Hide

FB engine does not allow creating new stored procedures as soon as internal procedure id generator reaches max value of smallint - 32839:

select gen_id(rdb$procedures,0) from rdb$database
GEN_ID
===========
32839

Our database does not have so many SP's, but this limit is reached over a time (6 months or less) due to dynamic procedure creation/deletion process. Regular DB backup/restore workaround is just too slow for 24/7 environment customers, because it's taking over 6 hours.

Dimitry suggested couple solutions:
- make ID's longer in ODS12 for upcoming versions
- Generate IDs modulo MAX_SSHORT (i.e. wrap them at the 32K boundary) and try to store the procedure record until success. This would mean slightly slower creation process but would allow to fill available ID gaps instead of overflowing. The practical problem is that we'll need to change or drop system triggers which means a minor ODS change. If the patch could be implemented in triggers only, it would be great. Otherwise, we'll need to put extra code into the engine to handle older ODS versions... this is what I highly dislike

Would be great if this problem can be addressed in all next FB versions: 1.5.5, 2.1 as a workaround and permanent solution built into ODS12.

Yours Sincerely,
Saulius Vabalas

Dir. Software Engineering

GLDS, Inc.
5954 Priestly Drive
Carlsbad, CA 92008

All

Comments

Work Log

Change History

Version Control

Subversion Commits

Sort Order:

[ Permalink | « Hide ]

Sean Leyne added a comment - 26/Oct/07 01:05 PM

First, a MODULO type solution will not solve the problem. Consider that your database could have a SP with ID = 1, then through your "dynamic procedure creation/deletion process" the next ID will be 1. In this case, the engine will die, since the ID is already in use.

Second, the EXECUTE BLOCK and EXECUTE STATEMENT syntax could be used to eliminate, if not significantly reduce, the number of SP IDs which your application requires.

[ Permalink | « Hide ]

Saulius Vabalas added a comment - 26/Oct/07 01:41 PM

- MODULO would help only if it is a part of the loop which scans for holes
(not used Id's). That's why it will be slower.
- Can not use EXECUTE BLOCK and EXECUTE STATEMENT in our case. Dynamic procedures are used for reporting purposes.

[ Permalink | « Hide ]

Claudio Valderrama C. added a comment - 27/Oct/07 01:09 AM

I don't see why dynamic procedure generation can't be replaced by execute block.
Sincerely, I don't see why messing with the internal FB procedures handling to satisfy a request for an usage that seems mostly a bizarre alteration of metadata due to "strange" application design.

[ Permalink | « Hide ]

Saulius Vabalas added a comment - 29/Oct/07 08:27 PM

multiple reasons:
- We need named procedures, because more than one is created at the time and they call each other. SP's are used for reporting purposes
- Backward compatibility with FB 1.53 and Interbase databases

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 10/Jun/08 02:04 AM

The ODS changes are already committed for Alpha 1, the final part of the fix will be done for Beta 2.

[ Permalink | « Hide ]

Vlad Khorsun added a comment - 25/Dec/08 09:30 AM

The fix involved obtaining value from generator RDB$PROCEDURES in DYN_define_procedure().
But the same generator also used in pre-store system trigger on RDB$PROCEDURES.
Therefore real RDB$PROCEDURE_ID is not the same as DYN_define_procedure assigned and every second generator values are lost.
I offer to change system trigger RDB$TRIGGER_28 to not assign value if already assigned.