You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
One of major pains in updating many client databases in remote areas is recompilation of STORED PROCEDURES.
Having this feature will ease the incremental Metadata updates in remote sites without including their ALTER statements. Some databases may consist of thousands of SPs and having their ALTER commands in every new release will result in *HUGE* update scripts. Considering incremental nature of updates (in which a single updater file will update any version of the Metadata to the last version; this happens in live projects a lot) and the result will be a very big file. Having this feature will help over coming this problem.
Procedures often need recompilation because of changes in other procedures, or whatever dependencies. I found oracle's behavior the best -- invalidate dependent procedures and triggers and let the programmer try to recompile them later -- a solution which implies the need for a RECOMPILE statement. I think this was requested in the tracker but I can't find it now.
As a side observation, IBExpert found a niche in solving those kinds of problems in the GUI that should be solved in the engine but require a hard time lobbying about (and developing, nonetheless). So you could give it a shot -- it has the options to recompile one or all procs and triggers.
Real issue is that procedure's\trigger's request stay in metadata cache until engine stop.
Such "recompile" could just mark current request instance in cache as obsolete. It will force engine to reload BLR and generate new execution tree at next reference.
This is something engine should (and can) do automatically. Unfortunately, current code in metadata cache seems not ready for such task.
We can continue discussion about it in architect list
Submitted by: K. A. (parshua)
Votes: 11
One of major pains in updating many client databases in remote areas is recompilation of STORED PROCEDURES.
Having this feature will ease the incremental Metadata updates in remote sites without including their ALTER statements. Some databases may consist of thousands of SPs and having their ALTER commands in every new release will result in *HUGE* update scripts. Considering incremental nature of updates (in which a single updater file will update any version of the Metadata to the last version; this happens in live projects a lot) and the result will be a very big file. Having this feature will help over coming this problem.
Examle:
RECOMPILE PROCEDURE SP_TEST;
RECOMPILE FUNCTION F_TEST; (for PSQL functions)
RECOMPILE VIEW V_TEST;
If not possible to add the command to SQL, it may be possible to add this functionality through a system STORED PROCEDURE named SP_RECOMPILE:
EXECUTE SP_RECOMPILE('SP_TEST');
The STORED PROCEDURE will automatically find the type of object and create and execute the resulting alter statement to recompile the object.
The text was updated successfully, but these errors were encountered: