At 04:47 AM 1/04/2006, you wrote:

>Hey all,
>
>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...

What version of Firebird? AFAIK, it's not possible to change the
data type of a domain from numeric to double. Here is what you
should have observed (Fb 1.5.3):

create domain unstable as numeric(18,2);
commit;
create table unstabletable (
marker char,
f1 unstable,
f2 unstable);
commit;
create index unstableindex on unstabletable(f1);
commit;
alter domain unstable type double precision;
commit;

ISC ERROR CODE:335544351

unsuccessful metadata update
Cannot change datatype for UNSTABLE. Conversion from base type
BIGINT to DOUBLE PRECISION is not supported.

So, if it actually happened, then the problem may indicate something
worse than the "obvious" - you used direct DML on the system tables
to effect these domain changes (or you used a tool that does that).

If indeed it was these changed domains that resulted in this error,
the actual run-time situation could/would arise if your SP was trying
to treat an integer variable as being valid for some comparison
operation on the changed fields, e.g. the parameter for a where
clause or an input variable.

Remember, PSQL doesn't know about domains, so the assumption must be
that, if a domain type change done using ALTER DOMAIN is successful,
i.e. valid, then column references will be valid. I believe ALTER
TABLE and ALTER DOMAIN do check dependencies on SP arguments, since
they are checkable; anything inside the module is unavailable, of course.

>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.
>Everything works fine now, but I am a little worried that
>something might be wrong with either my DB, or firebird itself.

I would be worried.

If you did actually "change" these domains by direct DML on the
system tables then it's asking for trouble, and not just because of
subtle dependencies like variables in PSQL modules. A number like
12345.66 as a numeric could easily be stored as 12345.6666666666667
as a double. If the column is indexed or, worse, has a constraint on
it, then your database is going to become corrupt as soon as you
begin updating or inserting data. Even 0 as a numeric is not the
same as 0 as a double.

Did you use a database tool to make these changes or did you script
them yourself? Unfortunately, there are some third-party tools out
there that modify the system tables directly, *by design*
circumventing the integrity rules of the database engine.

./heLen