Hi,

We contniue have have problems with firebird server crashing - seemingly as
a result of dynamicly creating stored procedures and attemptng to use these
on other connections.

We have a single process connected to a SS database. I have explained in a
previous post(attached to the end of this post) what we are trying to do.
Following Allan's advice that 'Clients will cache metadata on a per
connection basis' we have rigged our system to close connections after use -
and then only use stored procedures either created on the current
connection/transaction or by connection/transactions that where commited and
closed before the current connection opens. Still it crashes. This kind of
begs the question anyhow - if a stored procedure is not fully visible to a
connection/transaction such that attempting to execute will cause the server
to crash - shouldent the stored procedure perhaps not be visible at all !!

Does anyone else attempt to create and use stored procedures dynamicly ? -
Or does anyone know of rules regarding the creation and usage of stored
procedures on an active database ? Can anyone sugest a way in which stored
procedures can be created and used in a database where DML statements are
being executed also - which firebird server does not choke on ?


Thanks
Ryan


Original Post

We have used interbase and firebird for a couple of years in our application
and so far it has run very well. One of the software modules we are working
on now though is crashing Firebird 1.5 Final on windows often(other
platforms arnt tested yet).

Once these crashes have occured the database will often show index and
database page errors upon validation. Once this has occured crashes can
occur very reproducably. We have seen that a backup and restore can be done
and produces a database which shows no errors uppon validation and then the
database appears to run OK until the next crash.

One significant way in which this software module varies from others in the
application is that it uses fairly complicated stored procedures and creates
them dynamicly - while other threads also create stored procedures and
read/write to the database perhaps using them.

The application uses jaybird and currently we use a transaction isolation
level isc_tpb_consistency. Each seperate transaction uses stored procedures
either created in its context or by a previous transaction that has already
commited. If the same stored procedure happens to be required by two
concurrent transactions then it is created twice. The intention is that this
will be cleaned up later.

We have used other methods previously. This method seems to crash the server
least - at one point we thought it worked - but we still get crashes at
points.

It is difficult to pin down exactly what statement crashes the server. The
application has good logging but although it generally follows the creation
of a number of stored procedures, the crash follows this at seemingly random
points. The application does have a number of concurrent threads at times.
But sometimes the crashes occur while their is a single thread working
primarily.

I can provide sample databases/log files/crash files and more infomation if
anybody might be able to help solve this problem.

Any advice as to if this is even possible with firebird would be appreciated
or if their any particular rules to follow when creating stored procedures
dynamicly ?