Issue Details (XML | Word | Printable)

Key: CORE-1544
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Saulius Vabalas
Votes: 3
Watchers: 3

If you were logged in you would be able to see more operations.
Firebird Core

RDB$procedures generator overflow

Created: 26/Oct/07 12:52 PM   Updated: 29/May/15 05:06 PM
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

Issue Links:

Planning Status: Considered for inclusion
Target: 2.5 Alpha 1
QA Status: Done with caveats
Test Details:
1) test executes about ~3-4 minutes on Pentium 3.0 GHz with SATA HDD
2) creation and dropping of *each* SP is done in AUTONOMOUS transactions. Start of discussion with dimitr why it is need to do in such way: 10-mar-2015 19:10

 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
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   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
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.

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.

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.

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

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.

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.

Dmitry Yemanov added a comment - 25/Dec/08 12:04 PM
The last comment by Vlad applies to databases in older ODS (pre-11.2) only.

Pavel Cisar added a comment - 29/Jun/11 09:18 AM
QA test added.