Issue Details (XML | Word | Printable)

Key: CORE-2472
Type: New Feature New Feature
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: K. A.
Votes: 10
Watchers: 6
Operations

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

Add RECOMPILE PROCEDURE, RECOMPILE FUNCTION, RECOMPILE VIEW commands

Created: 25/May/09 03:21 PM   Updated: 26/Jun/13 03:05 PM
Component/s: Engine
Affects Version/s: None
Fix Version/s: None


 Description  « Hide
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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
K. A. added a comment - 25/May/09 03:46 PM
I forgot to mention Triggers:

RECOMPILE TRIGGER TR_TEST;

Adriano dos Santos Fernandes added a comment - 25/May/09 03:53 PM
And why do you do regular recompilations?

What problem you intent it to solve when you do?

Cosmin Apreutesei added a comment - 26/May/09 10:46 AM
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.



Vlad Khorsun added a comment - 26/May/09 11:08 AM
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