Hello Helen,

    One of the most dangerous consequences of forgetting to update values of the new added field, when it has constraints, is that, when recovering a database from a backup, errors are risen and the recover process aborts. Although backups are done raising no error, that doesn't guarantees that data is consistent. Only when a restore is done, data integrity is fully checked. Normally, backups are done far more frequently that restore, as they are done at least on a daily basis.

    To prevent or at least mitigate this risk, a cron job could be created in order to daily recover the database from its last daily backup onto another file.

    gbak -replace_database -se localhost:service_mgr -user sysdba -password <sysdba_psw> <path_to_last_daily_backup.fbk> <path_to_temporary_file.fdb>

    If an inconsistence were found, an error would be thrown and the cron job user will receive an e-mail.

    Is there a better way to test full integrity of all the data than a restore ?

Thanks

Aldo Caruso

 

Hello Aldo,

> My questions are the following:

> 1) Is the intended effect to fill behind the scenes a newly created
> field with its default value when there is a not null constraint ?

No. Only inserts subsequent to the commit of the DDL for the new
field will use the default in the case where no value is provided.

Note, also, that default values apply only to inserts and only where
the field is absent from the field list for the insert.

> 2) Could this behind the scenes filling fail because of an update or
> insert of another concurrent transaction ?

There is no "behind the scenes filling". If you add a NOT NULL field
to an existing table, or change a nullable field to NOT NULL, then you
are responsible for filling the field yourself, immediately after the
DDL is committed.

update mytable set newfield = 1 where newfield is null

update mytable set existingfield = 1 where existingfield is null

As for the effect on concurrent transactions, you should not be
attempting to change the structure of a table while it is in use.

HB

> ------------------------------------
> Posted by: Aldo Caruso <aldo.caruso@...>
> ------------------------------------

> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu. Try FAQ and other links from the left-side menu there.

> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/

> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> ------------------------------------

> Yahoo Groups Links

--
Kind regards,
Helen Borrie