| Subject | Re: [IBO] Add NOT NULL constraint within transaction |
|---|---|
| Author | Helen Borrie |
| Post date | 2007-01-26T00:45:02Z |
At 12:28 AM 26/01/2007, you wrote:
commit, the engine performs a lot of stuff to realise the new
structure, INCLUDING writing records into a number of system tables
(RDB$RELATION_FIELDS, RDB$FIELDS and others). If the DDL work is
uncommitted, this stuff isn't visible to the transaction.
THAT IS WHY YOU SHOULD NEVER PERFORM DDL AND DML IN THE SAME TRANSACTION !!!
If you try to do so, you will be LUCKY if an exception occurs that
prevents you from executing your DML. There are conditions where the
DML can execute and then you are in trouble, because you will have
logical corruption in your data. To some extent the loopholes have
been progressively closed in Fb 1.5 and Fb 2.0 but it is still
possible to cripple yourself if the gun is pointed directly at your foot.
because Firebird and InterBase don't support them. Savepoints are a
way to nest the work inside a transaction, i.e. by setting a
savepoint and later rolling back to that savepoint.
transaction control.
Indeed, you can use a TIB_Statement component to execute DDL
statements but you must isolate such statements from any dependent
DML and, indeed, from any dependent DDL.
A better approach in IBO to the scenario you're trying implement
would be a TIB_Script by which you execute and commit blocks of DDL
and DML in a succession of operations, swapping out the committed
statements and reading in the next block in a safe sequence.
But be aware that deploying a user application as a hands-free way to
modify the database structure is not to be considered lightly. You
can make 255 changes to any particular table in the life of a
database. When request # 256 arrives, all users will find the
database locked and unusable. The database will then have to be
taken off-line, gbak-ed and restored.
Additionally, you will have to be extremely careful to identify the
points of failure and manage the situation if some part of the operation fails.
You will have to be certain that you have some way to make such
applications a use-once thing that will self-destruct upon completion.
Helen
>Hi, greetings from a (satisfied) lurker.A DDL statement doesn't take effect until it is committed. At
>
>I want to add a NOT NULL constraint to an existing column. Unless I'm
>totally missing something, this can't be done in a single command and
>I'm on board with the idea of using a temporary column and
>accomplishing the task in several steps. I'm not super-experienced
>using transactions but I understand that FB supports DDL in
>transactions and this seems like a natural time to use one.
>
>The code below fails on the first update statement ("Column unknown
>TEMP_COL") though I would have thought that the new column could be
>"seen" within the transaction. The rollback line is executed and
>temp_col is not added, as expected.
commit, the engine performs a lot of stuff to realise the new
structure, INCLUDING writing records into a number of system tables
(RDB$RELATION_FIELDS, RDB$FIELDS and others). If the DDL work is
uncommitted, this stuff isn't visible to the transaction.
THAT IS WHY YOU SHOULD NEVER PERFORM DDL AND DML IN THE SAME TRANSACTION !!!
If you try to do so, you will be LUCKY if an exception occurs that
prevents you from executing your DML. There are conditions where the
DML can execute and then you are in trouble, because you will have
logical corruption in your data. To some extent the loopholes have
been progressively closed in Fb 1.5 and Fb 2.0 but it is still
possible to cripple yourself if the gun is pointed directly at your foot.
>I thought maybe savepoints was the way to go but now I'm really out ofDon't consider going there. IBO doesn't support nested transactions
>my depth and am also fuzzy on how/if IBO supports nested transactions.
because Firebird and InterBase don't support them. Savepoints are a
way to nest the work inside a transaction, i.e. by setting a
savepoint and later rolling back to that savepoint.
> The sequence of steps in the code runs fine without transactionAnother misconception. EVERYTHING in Firebird/IB runs under
>control but is unsafe, I should think.
transaction control.
>Am using FB2 & IBO 4.7 BetaNever, repeat NEVER try to combine DDL and DML in a single transaction.
>14. Many thanks for any insights.
Indeed, you can use a TIB_Statement component to execute DDL
statements but you must isolate such statements from any dependent
DML and, indeed, from any dependent DDL.
A better approach in IBO to the scenario you're trying implement
would be a TIB_Script by which you execute and commit blocks of DDL
and DML in a succession of operations, swapping out the committed
statements and reading in the next block in a safe sequence.
But be aware that deploying a user application as a hands-free way to
modify the database structure is not to be considered lightly. You
can make 255 changes to any particular table in the life of a
database. When request # 256 arrives, all users will find the
database locked and unusable. The database will then have to be
taken off-line, gbak-ed and restored.
Additionally, you will have to be extremely careful to identify the
points of failure and manage the situation if some part of the operation fails.
You will have to be certain that you have some way to make such
applications a use-once thing that will self-destruct upon completion.
Helen