| Subject | Re: [firebird-support] force stored procedure recompile |
|---|---|
| Author | Helen Borrie |
| Post date | 2007-01-09T01:06:18Z |
At 07:11 AM 9/01/2007, you wrote:
do it at all unless you have exclusive access. That behaviour is to
be reverted in v.2.0.1, due to the outcry from the masses, but that's
in no way a vindication of silly database maintenance practices.
The 1.5 (and planned 2.0.1) behaviour is that the recompile is
allowed, with variable effects depending on the server model:
with Superserver, if the procedure has not been used since the
database was opened, it will take effect immediately. If it has been
used (and is therefore cached) it will not take effect until all
connections have detached and the database file has been reopened.
with Classic, any connections currently attached will continue to use
the old version; new connections will use the new version.
So, you see why it is silly to try to do this kind of thing when you
don't have exclusive access. You potentially have multiple users
processing the same data in different ways.
What *is* risky (and the DB engine doesn't stop you) is using a
select * to feed an insert operation, under any circumstances but
especially in PSQL modules. If your database metadata isn't set in
concrete then, at best, select * saves your poor, tired fingers a few
keystrokes in exchange for a palpable risk. Lucky if your metadata
changes cause the SP to throw exceptions - that's good and it's what
you want. But it's a real problem if there should be metadata
changes that don't affect the ability of the compiled SP to run
without exceptions. You create the potential for the SP to do things
you didn't intend.
./heLen
>Hi,No.
>We are using Firebird 1.5.2 and have created stored procedures which
>insert row copies with statements similar to:
>
> insert t1 from select * from t1 where...
>
>We have recently added new columns to this table t1, but have not
>recompiled this stored procedure. We are now getting errors where
>this statement fails.
>
>When we recompile the stored procedure with an ALTER PROCEDURE call
>the code works fine.
>
>1) Can we force a recompile with a gbak switch (or similar db
>maintenance operation)?
>2) What are the risks of calling ALTER PROCEDURE on a database that isIt is not so much "risky" as foolish. In v.2.0 (currently) you can't
>currently open and in use?
do it at all unless you have exclusive access. That behaviour is to
be reverted in v.2.0.1, due to the outcry from the masses, but that's
in no way a vindication of silly database maintenance practices.
The 1.5 (and planned 2.0.1) behaviour is that the recompile is
allowed, with variable effects depending on the server model:
with Superserver, if the procedure has not been used since the
database was opened, it will take effect immediately. If it has been
used (and is therefore cached) it will not take effect until all
connections have detached and the database file has been reopened.
with Classic, any connections currently attached will continue to use
the old version; new connections will use the new version.
So, you see why it is silly to try to do this kind of thing when you
don't have exclusive access. You potentially have multiple users
processing the same data in different ways.
What *is* risky (and the DB engine doesn't stop you) is using a
select * to feed an insert operation, under any circumstances but
especially in PSQL modules. If your database metadata isn't set in
concrete then, at best, select * saves your poor, tired fingers a few
keystrokes in exchange for a palpable risk. Lucky if your metadata
changes cause the SP to throw exceptions - that's good and it's what
you want. But it's a real problem if there should be metadata
changes that don't affect the ability of the compiled SP to run
without exceptions. You create the potential for the SP to do things
you didn't intend.
./heLen