| Subject | Re: [firebird-support] Re: Problem with Execute Statement in Fb |
|---|---|
| Author | Helen Borrie |
| Post date | 2004-08-02T00:12:48Z |
At 11:01 PM 1/08/2004 +0000, you wrote:
trigger. EXECUTE STATEMENT puts this ability at your disposal but that
doesn't mean it's a rational thing to do. PSQL was designed to forbid DDL
statements and the availability of EXECUTE STATEMENT doesn't somehow
magically change the integrity rules of the database engine.
1.As Martijn pointed out, you can run out of generators. The limit depends
on two things: the generator id (a smallint - so you can't create any more
generators after you reach id # 32,767) and the page size (there is only
one page for storing generators).
2. Because the statement is unknown to the trigger at compile time, you
have an untrappable exception if something goes wrong with the statement in
your EXECUTE STATEMENT string at run-time, e.g. you're attempting to create
a generator with a name that already exists or with invalid characters in
the identifier.
3. Timing and transaction isolation. Firebird and InterBase will crash if
you try to use uncommitted metadata. The new generator won't be accessible
until the transaction in which it is created commits. The transaction will
recognise that the trigger exists, but it can't use it because it is
uncommitted.
So, if you have any code anywhere in the same transaction that tries to
refer to this uncommitted metadata, then - boom! Going back to your
application...if your transaction has tiConcurrency isolation and is
committed using CommitRetaining, the new generator won't be visible to any
further statements in your application that try to refer to it, either
(including any DML statements that cause any trigger to fire that refers to
this generator).
4. If the transaction rolls back, the generator won't exist at all.
In short, the system is doing its best to let you know that this is a
really bad idea. Listen to it. Find some other way to do this.
/heLen
> > Did you know there's a maximum amount of generatorsIMO, it's a terrible design strategy to create or manipulate metadata in a
> > you can create?
> >
>
>Sure I know... but i tested it with an empty database with same
>behaviour....
>
>May be this could be a IBO bug or something like that...any way,
>some ideas are wellcome
trigger. EXECUTE STATEMENT puts this ability at your disposal but that
doesn't mean it's a rational thing to do. PSQL was designed to forbid DDL
statements and the availability of EXECUTE STATEMENT doesn't somehow
magically change the integrity rules of the database engine.
1.As Martijn pointed out, you can run out of generators. The limit depends
on two things: the generator id (a smallint - so you can't create any more
generators after you reach id # 32,767) and the page size (there is only
one page for storing generators).
2. Because the statement is unknown to the trigger at compile time, you
have an untrappable exception if something goes wrong with the statement in
your EXECUTE STATEMENT string at run-time, e.g. you're attempting to create
a generator with a name that already exists or with invalid characters in
the identifier.
3. Timing and transaction isolation. Firebird and InterBase will crash if
you try to use uncommitted metadata. The new generator won't be accessible
until the transaction in which it is created commits. The transaction will
recognise that the trigger exists, but it can't use it because it is
uncommitted.
So, if you have any code anywhere in the same transaction that tries to
refer to this uncommitted metadata, then - boom! Going back to your
application...if your transaction has tiConcurrency isolation and is
committed using CommitRetaining, the new generator won't be visible to any
further statements in your application that try to refer to it, either
(including any DML statements that cause any trigger to fire that refers to
this generator).
4. If the transaction rolls back, the generator won't exist at all.
In short, the system is doing its best to let you know that this is a
really bad idea. Listen to it. Find some other way to do this.
/heLen