> Just this morning, I noticed a flood of error messages coming from

my Web server. A certain stored procedure I was

> running was causing an "Index Unexpectedly Deleted". What I come to

find out is, there are fields referenced in said

> stored procedure which had changed datatypes (mostly

numeric(something) -> Double Precision). Obvioulsy, there were no

> dependency errors when these fields changed, and I am perplexed as

to why...

>

Hi Jason,

Firebird can be a bit rough around the edges when it comes to metadata
changes. I recall at one stage we refactored a stored procedure that
was internally used by several other stored procedures, and our
upgrade patch missed one of them. Although that particular stored
procedure was to support a depricated application, we planned on
leaving it there until the new application had proved itself. The
refactoring was pretty basic, from memory we dropped one of the input
parameters (which was never actually used by the procedure). There
were no complaints - until you attempted to retore a backup.

About 20 minutes later I had discovered and fixed the stored procedure
with the problem and all is now well.

The same issue also occured when you add a not null field to a table
and do not do anything about pre-existing records. This issue was
discovered and fixed before it even went through QA though.

> On top of this, I needed to remake/alter said stored procedure to

reflect the new field types. I was thinking, I

> SHOULDNT be able to drop this procedure because a few calculated

fields are dependent on it. But sure enough, I was able

> to drop it and readd it without a complaint from the server.

Ideally, you should not be able to add a not null field to a table
with existing records, but rather you should add the field, fill all
records with values, then add the not null constraint. Unfortunately
there is no ability to change the not null flag through a DDL
statement (although if you know what you are doing it can be achieved
through system tables).

What I am trying to get at is that Firebird could better prevent us
from shooting ourselves in the foot. Changing the field types without
recompiling the stored procedure is shooting yourself in the foot and
so should be prevented.

> Everything works now, but I am a little worried
> something might be wrong with either my DB, or firebird itself.
>

1) Fixing Firebird

I imagine these sorts of issues will become more rare as the
dependency rules are tightened. Search the bug tracker, and if it is
not there you could report the bug (providing a simple test case to
demonstrate).

2) Preventing issues

a) Don't assume Firebird will check all dependencies when you do
something major (it should but it doesn't)
b) Do not perform DDL changes with anyone else connected to the database
c) Make a backup BEFORE any DDL changes (and test it works).
d) Backup then Restore AFTER any DDL changes (I imagine in your case
the database would not restore). This has the added benefit of
rebalancing all indices and removing all garbage.

3) Dry run

Ideally you would have run your upgrade script on a test machine
database, then run your test cases against the upgraded database. I
suspect that any test case that called that procedure would fail, and
you would then be able to prevent it before releasing it to your customer.

Adam