On 13-2-2019 17:48, Karol Bieniaszewski liviuslivius@...
[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