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
El 10/02/18 a las 15:41, Helen Borrie hele...@iinet.net.au
[firebird-support] escribió:
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.car...@argencasas.com>
> ------------------------------------
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> 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