At 09:06 AM 6/07/2010, you wrote:

>Hi, All
>
>I'm currently using FB2.1.3, and found an annoying issue/bug in FireBird, let say, I have a table called SERVICES, and there is a stored procedure that includes the following:
>
> insert into SERVICES(xxx, yyyy) values (aaa, bbb)
>
>and the stored procedure was compiled, and its source code was removed from RDB$PROCEDURES table. Then later on, I added a new not-null field with a default value into the SERVICE table e.g.:
>
>ALTER TABLE SERVICES
> ADD POSTED CHAR(1) NOT NULL DEFAULT 'N';
>
>What was happened was that, when execute above stored procedure, the SERVICES table have the records which the POSTED field were ALL NULL(shouldn't never happen, but it happens), UNTIL the above stored procedure gets re-compile again.
>
>Its not good at all, which means, every time when I add a new field, I will have to re-compile all the stored procedures that have the tables involved, and it doesn't make sense to me at all.
>
>Is it a bug?

Did you know that, when you add a new, non-nullable field to a table, you must ALSO course through the existing data and apply the default to all of the records manually? Firebird *does not* automatically fill new fields of old records with data.

Whenever you do metadata actions like this you should put the database off-line and then log in with exclusive access. If you try to do it without putting the DB offline first, you will have old versions of the table and the stored procedures in the cache, amongst other problems.

Commit work when you have completed the metadata changes. Then populate the empty field in a fresh transaction and commit work. Lastly, log out, before putting the database back on-line. After that, all should work as you expect.

./heLen