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
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.
Dir. Software Engineering
5954 Priestly Drive
Carlsbad, CA 92008