Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

RDB$procedures generator overflow [CORE1544] #1961

Closed
firebird-automations opened this issue Oct 26, 2007 · 19 comments
Closed

RDB$procedures generator overflow [CORE1544] #1961

firebird-automations opened this issue Oct 26, 2007 · 19 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Saulius Vabalas (svabalas)

Is related to QA332

Votes: 3

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

Commits: 98534ec 02bc98b

====== 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

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Saulius Vabalas (svabalas)

- 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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Claudio Valderrama C. (robocop)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Saulius Vabalas (svabalas)

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 13352 ] => Firebird [ 14228 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Open [ 1 ]

Target: 2.5 Alpha 1 [ 10224 ]

Fix Version: 2.5 Alpha 1 [ 10224 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Open [ 1 ]

Planning Status: Considered for inclusion

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 2.5 Beta 1 [ 10251 ]

Fix Version: 2.5 Alpha 1 [ 10224 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

The last comment by Vlad applies to databases in older ODS (pre-11.2) only.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA332 [ QA332 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

QA test added.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment