On 13-2-2019 17:48, Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support] wrote: > >>Recompiling stored > >>procedures, triggers, etc is always a good thing to do when upgrading. > >>Mark > > Why it is not done automatically on restore if there is source present?
I shouldn't have called it recompiling, because technically it is more recreating (or altering). And it is not so simple: the BLR of a stored procedure might still be valid and continue to work, but the stored source might not (eg consider reserved words). For example: create procedure dummy as declare boolean smallint; begin boolean = 1; end If you create this on Firebird 2.5 and restore it on Firebird 3.0, it will 'work' just fine. But if you create / recreate / create or alter it with this body on Firebird 3 it will fail with a 'Token unknown - line 4, column 9 boolean' as boolean is a reserved word since Firebird 3. Another situation would be for example certain ambiguous syntax that is no longer allowed, but when the statement was compiled it was transformed to low-level BLR which is not ambiguous and is still executable, but recompiling it will no longer work (or worse, in a version where the ambiguity was allowed, it could 'switch' between behavior depending on optimizer choices, although that is probably more a theoretical concern). In the above cases, it could of course fallback to the BLR, but now you have two possible routes for stored procedure creation during restore, which has its own cost in terms of complexity and maintenance. And a bit more in the 'farfetched' realm, but less benign than the previous, given the historic mutability of the metadata tables, it is impossible for Firebird to distinguish between the original and valid source and altered (but still compilable) source (eg a programmer/vendor that the replaced the source not with null, but with - for example -an empty procedure body). In such a situation, recreating from source may render the database inoperable because the new code does nothing, or maybe even introduce 'malicious' code (as someone replaced the body with a form of stored SQL injection). In short, the current way of working may not be ideal, but the alternative is probably worse. And, you should really test your upgrade between major versions anyway (even if Firebird were to recreate procedures from source). Mark -- Mark Rotteveel