Problem with editing procedures being in use #7428
Comments
|
Hello. Could you look into this problem? |
|
Try to add input parameters at last position and with default value. |
|
Yeah, thanks, I could try it, but it is still not a solution to the problem. |
|
I can hardly see any bug in the engine. You change the declaration (contract) of the procedure and want the old version to be called from the already existing connections, while by design the new version becomes immediately visible. Your approach means that matching in the metadata cache should be based not just on name, but on the complete signature (name + parameters). This smells like a new feature to me. |
|
Moreover, any new connection established right after the procedure was altered will never see its old version but it will be not prepared to deal with more input parameters either. This makes the whole effort somewhat pointless. |
That's not true, Really. In nowait transaction I ran the procedure, in 2nd - wait - I added a new parameter. First connection is unable to run this procedure at all, but the 3rd connection started after the altering, no wait, launches the procedure with added parameter with completely no problem |
Maybe it is by design, but do you really think this design is right? If you allow changing procedure input parameters in wait transaction, we assume it's not harmful, forbidden and we are safe to do it. Now doing this makes already connected users to forcely reconnect to be able to run the procedure at all. The stability drops. |
|
That's why in big systems procedures are never changed. New procedures are created instead. |
|
That's how we deal with it now, but we seek for a much professional solution. We have to remember to drop every old procedure after adding a new one, so that the DB is not full of unused procedures. |
|
You must not drop them. They are still needed for old applications which you don't want to break. |
|
That's not the case in our situation. We offer such a feature as user defined procedures being launched in specific places of the system, after given activities, as saving a document, etc. There is only one version of ERP system at time, there is no possibility to even start the system with the older version of the ERP system. |
I just tried and new connection expectedly raises "Input parameter mismatch" error. You cannot do |
|
But why would the new connection execute the procedure with one parameter? The new connection was established after changing the procedure so it is natural it has to run with two parameters. |
In this case your system is small and you should have no problem to upgrade database structure in the same technical downtime while you upgrade the application. |
We are the second main company mentioned on Firebird webpage in case studies :-) |
How should the running application know what to pass in the second parameter if one second ago it didn't know about its existence at all? |
|
On 1/9/23 16:10, Dmitry Yemanov wrote:
But why would the new connection execute the procedure with one
parameter? The new connection was established after changing the
procedure so it is natural it has to run with two parameters.
How should the running application know what to pass in the second
parameter if one second ago it didn't know about its existence at all?
Specially if that procedure is used by many people?
|
The application should be able to run the procedures with the complete exact DLL at the time of establishing the connection. |
If that seems to be a problem for you, maybe it will be easier just to show in the error message the list of processes using given procedure? |
I mean when we normally try to edit the procedure on read commited nowait and we can not do it because of users actively using it. |
|
Any solution would be appreciated. In current situation we either have to ask for the downtime to just implement a simple change in one user defined procedure or to create another one and, with several new procedures a week, the database will have crazy number of unused procedures which are useless and can only be a reason of problems. |
|
On 1/9/23 16:24, Tomasz Dubiel wrote:
Any solution would be appreciated.
Something like disable any modification of procedures in use?
In current situation we either have to ask for the downtime to just
implement a simple change in one user defined procedure or to create
another one
Last not understood. Do you want to say that creation of new procedure
affects already running transactions?
and, with several new procedures a week, the database will have crazy
number of unused procedures which are useless and can only be a reason
of problems.
Also not understood. Adding nightly job dropping unneeded procedures is
simple task. I hope you do not have _SO_ long running transactions ?
|
|
Currently, old version of the procedure is visible to the priorly prepared statements, but newly prepared statements will see the new version of the procedure. This is by design and this is how our metadata cache was working since the very beginning. And changing it to the new rules (keeping the old version visible until the connection ends) will likely to break way more applications that we may expect, because lots of users prefer to see the new procedure logic (i.e. its body) without reconnecting. Changing the body is much more common than changing the parameters. |
Let me remind you the main problem. I want to edit the procedure - Firebird gives me a message that it is unable to do it, because SOMEONE uses it. OK, then who uses it? Firebird doesnt tell me this. OK, but the change is urgent. What do I do? I call the customer to STOP THE WHOLE SYSTEM with users all around the country. This way I make the change fast enough. No, Im not saying anything like this that creation of new procedure affect already running transactions. At all.??? And the last one - we have hundreds of customers. Each one can have completely own procedures, doing exactly what the given customer needs. The needs in even one company can change over a course of time. There is no way to easily detect unwanted procedures, not used by anybody. |
|
Can't we skip metadata cache during statement preparation and read procedure definition from system tables according to rules of the transaction used for that?.. |
|
On 1/9/23 16:42, Dimitry Sibiryakov wrote:
Can't we skip metadata cache during statement preparation and read
procedure definition from system tables according to rules of the
transaction used for that?..
If we always do that that will be great performance penalty.
I.e. I suppose you sugegst to do it in a case of some speicific errors
during statement preparation, yes? That's formally possible with one
poorly technical issue - the procedure in src is sometimes referenced
not by pointer but by ID (resolved in metadata cache). I.e. suggested is
not trivial change.
|
|
On 1/9/23 16:41, Tomasz Dubiel wrote:
There is no way to easily detect unwanted procedures, not used by anybody.
Very simple. When you want to modify procedure but instead have to
create the new one add the old procedure to the list of procedures to be
deleted, that may be an SQL table for example.
|
No. Nowadays memory is big so we can have big page cache. Won't it work as effective as a metadata cache because system tables pass through it anyway? |
That still requires extra work. I would expect from the RDBMS to be able to handle it in much more professional way. |
|
On 1/9/23 16:51, Dimitry Sibiryakov wrote:
I suppose you sugegst to do it in a case of some speicific errors
during statement preparation, yes?
No. Nowadays memory is big so we can have big page cache. Won't it
work as effective as a metadata cache because system tables pass
through it anyway?
No. It will be many times slower. Compare difference between search in
some tree in memory using complex criteria (some loop or recursion
needed) and getting value from array by index (2-3 asm instructions).
|
|
On 1/9/23 17:15, Dimitry Sibiryakov wrote:
getting value from array by index (2-3 asm instructions).
Yes, but related locks, AST delivering, etc may decrease difference.
All mentioned is needed only when DDL statement is in effect.
In current (per-attachment) design meta-cache is protected by attachment
mutex, the only thing one needs to do when using object from it is
increment use counter, even not atomic one.
|
I doubt any DBMS have such a feature. Do you know one ?
Did you tries to use input params with default values ? It was introduced for more-or-less similar purposes. |
So metadata cache object is kinda reference-counted. What may be wrong is DDL transaction simply replace it with the new version? Those requests that keep own copies of old one would be able to continue using it, right? |
|
This is more or less how it works now. But Tomasz needs new requests of existing connections to use the old copy of the procedure. |
I have no idea, but I would be very surprised to hear that adding a new input parameter in Oracle or MSSQL breaks the possibility of using the procedure for all existing connections. Is it really that way? |
I would like to stress this out, because it is really the case in Firebird now. When I modify the procedure being in use and add an input value without a default value, the existing attachment is no longer able to run the procedure at all - neither with the old set of inputs, nor with the new. |
Everyone have its own problems. For example, in MSSQL you may drop table used in stored procedure and it (SP) will not run anymore. And nobody have idea about such broken SP until attempt to execute it. In Firebird this is impossible.
Of course I know.
You asked for "any solution". I offer one that may be used immediately (no need to wait for a fix).
Set default value to NULL and make SP work "as before" when such param is NULL.
This returns us to the my initial question - do you know any DBMS that works this way ? I'm almost sure - no, it is not exists. And there is a reasons for it.
When SP is altered in no-wait transaction, Firebird already report "object in use" error. All you additionally need in this case: it is info - which attachments is uses this SP. This information could be obtained from lock manager, while it is not too easy. If this really helps you (I'm not sure), we can think in this direction... |
|
And now this is really a bug. |
Of course, that would be huge help. If I can't edit the procedure, because the problem is that someone uses it, then all I need to remove the reason is to know what attachments use that, so I can proceed. |
|
Looks like it was fixed in fb4, see #6414 (yes, that ticket about another issue) @tomaszdubiel18: could you try fb4 or fb5 ? |
|
Is it about @dyemanov comment or my last comment? |
It is about current ticket (issue), sorry to be not clear. I.e. it looks as fixed since v4.
Needs investigation, can't say right now. |
While I never used it and found it to have some difficulties, I think something like Oracle edition-based metadata to be useful in this type of scenario. |
|
@hvlad I suppose you were talking about checks of I do not know what is right because all choices are very bad. It's first a conceptual problem before an implementation problem. |
Should I create another issue? Because @dyemanov confirmed here the bug with dropping objects and that what you mentioned, is something another, but very useful in my case. |
|
For the possibility to return attachments using given database object I created a separate topic: #7454 |
I already asked you to check FB4 with your real test case. I can check my artificial test only and it makes no guarantee for your real case. Or I can prepare build of FB3 with supposed fix for you. On success, we can discuss including it into FB3 with team.
Yes, looks likely. |
|
If you could, it would be easier for me to check on FB3 with fix than to install another Firebird server. |
|
Here it is Firebird-3.0.11.33654_x64.7z.zip |
|
I'm not able to download this. Every time it gets around 5 or 6 MB out of 7 and the transfer stops. |
|
It went through finally, I will check. |
|
I execute procedure in one nowait transaction, in second wait transaction I add, modify, delete input and output parameters and when executing with the current set of input parameters, I get no errors. It looks it works correctly. |
Hello.
Firebird 3.0.10 SuperServer.
We have one connection with nowait (isc_tpb_read_committed, isc_tpb_rec_version, isc_tpb_nowait).
We have second connection with wait (isc_tpb_read_committed, isc_tpb_rec_version, isc_tpb_wait).
Second connection executes create or alter procedure... - we add an another input parameter to an existing procedure, compile and commit.
Then we run select * from this_procedure from the first connection - when we run a procedure without a changes, we get an error:
"invalid request BLR at offset 160.
Input parameter mismatch for procedure..."
We should still be able to run the old version of procedure, without using a new parameter. Even if we try to execute a procedure with a new parameter, we get a similar error:
"Dynamic SQL Error.
Input parameter mismatch for procedure...".
Everything is ok, when we make changes to the main begin end block or to the output parameters. However, any change to the input parameters (adding, editing or deleting an input parameter) breaks the possibility of using that procedure for all already existing connections.
The text was updated successfully, but these errors were encountered: