RDB$procedures generator overflow [CORE1544]

[firebird-automations]

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]

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]

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]

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]

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]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

Modified by: @pcisar

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

[firebird-automations]

Modified by: @dyemanov

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

Target: 2.5 Alpha 1 [ 10224 ]

Fix Version: 2.5 Alpha 1 [ 10224 ]

[firebird-automations]

Modified by: @dyemanov

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

Planning Status: Considered for inclusion

[firebird-automations]

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]

Modified by: @dyemanov

Fix Version: 2.5 Beta 1 [ 10251 ]

Fix Version: 2.5 Alpha 1 [ 10224 ] =>

[firebird-automations]

Modified by: @dyemanov

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

[firebird-automations]

Modified by: @dyemanov

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

[firebird-automations]

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

[firebird-automations]

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]

Commented by: @dyemanov

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

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA332 [ QA332 ]

[firebird-automations]

Commented by: @pcisar

QA test added.

[firebird-automations]

Modified by: @pcisar

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

[firebird-automations]

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