Commented by: @dyemanov

Before going into a deep discussion, it would be appreciated if you would post an example (SQL code snippet) of what exactly works in v1.5 and doesn't work in v2.1.2.

Commented by: Hilmar Brodner (syn)

Sorry, I didn't hink it was necessary to post a snippet as this was done before in CORE888 and this is a well documented behaviour that basically affects all changes in a live environment.

The problem even found its way into the release notes:
Firebird_v2.0.1.ReleaseNotes.pdf
Page 11:
"
An important reversion to 1.5 behaviour has occurred in this sub-release, as follows:
In Firebird 2.0, a deliberate restriction was imposed to prevent anyone from dropping, altering or recreating a
PSQL module if it had been used since the database was opened. An attempt to prepare the DDL statement
would result in an "Object in Use" exception.
Many people complained that the restriction was unacceptable because they depended on performing these
metadata changes "on the fly". The restriction has therefore been removed. However, the reversion in no way
implies that performing DDL on active PSQL modules is "safer" in Firebird 2.0.1 than it was in V.1.5.
"
Now, some time and versions later, we seem to be back to this behaviour.

Here is the SQL from CORE888 again:

---------------------------------------------------
SET TERM ^ ;
CREATE PROCEDURE TestProc
AS
BEGIN
EXIT;
END ^
SET TERM ; ^

EXECUTE PROCEDURE TestProc;

DROP PROCEDURE TestProc;
---------------------------------------------------

Commented by: @dyemanov

I've just verified: the above SQL executes without errors on v2.1.2.

Commented by: Hilmar Brodner (syn)

CREATE TABLE T1 (
ID INTEGER,
FIELD1 INTEGER
);

CREATE TABLE T2 (
ID INTEGER,
FIELD2 INTEGER
);

CREATE VIEW V1(
T1_FIELD,
T2_FIELD)
AS
select
a.field1,
b.field2
from t1 a
LEFT OUTER JOIN t2 b ON http://a.id = http://b.id
;

Client 1:
SELECT * FROM V1;

Client 2:
DROP VIEW V1; -- This causes object in use

CREATE VIEW V1(
T1_FIELD,
T2_FIELD)
AS
select
a.field1,
b.field2
from t1 a
LEFT OUTER JOIN t2 b ON http://a.id = http://b.id
;

COMMIT WORK;

The above SQL from CORE888 also does not execute if the "EXECUTE PROCEDURE TestProc;" is done from a second client.

Both work without problems in FB 1.5 and - according to release notes - FB 2.0.1.

Commented by: @dyemanov

The SQL from CORE888 still executes fine in v2.1.2 even if the EXECUTE PROCEDURE statement is done from a second client. Tested with ISQL.
As for your other sample, it's a different situation. You may find a complete explanation below.

There are different rules for metadata modifications performed while an object is used by a prepared statement or active transaction. Originally, they were:

a) you cannot drop a table or view while it's used by someone else
b) you can alter/drop procedure while it's used by someone else
c) you cannot create/drop foreign key while someone else is connected to a database

Variations in practice:

Rule (a) is accidentally broken in v1.5.2, v1.5.3, v1.5.4 and v1.5.5. These versions don't validate usage counters of metadata objects. Versions v1.5.0 and v1.5.1 (as well as all v1.0.x versions) works as expected and don't allow such modifications.
Rule (b) was disabled in v2.0.0 (thus rejecting modifications) and re-enabled in v2.0.1.
Rule (c) was removed starting with v2.0.0.

So, this ticket is wrong regarding procedures and correct regarding tables/views, but the latter is only because of the bug in recent v1.5.x versions.

Commented by: Hilmar Brodner (syn)

OK, understood. I didn't differentiate between rules a) and b).

I also understand that you are saying that the behaviour with regards to views and tables in FB1.5.5 is actually a bug. But that brings us back to my first point: May have been a bug, but never resulted in any harm.

How do you develop a database with 20+ programmers without being able to alter views or tables without having 20+ copies of the main database? How do you update the database at a customer if you have to disconnect 100+ clients each time? We are talking high impact to the work that is being able to be done. And once again: Finally we are able to create/drop foreign keys, but no longer able to do the same for views and tables? Is there a way around this? We would love to upgrade from 1.5.5 (and our community would love us to as well) but that just wouldn't work, given that restriction. Are there any plans to reintroduce the bug from 1.5 - I know that is not the idea of progress but with regards to procedures this is what happended as well. How do others work around this problem?

(Sorry for all the nagging, but this is a huge thing for us :)

Commented by: @dyemanov

In development, personally, I had no problems with 20+ copies of the database maintained in sync via the SQL script and CVS repository. In production, I would never recommend to upgrade metadata with connected users unless you understand the consequences (like users still seeing the older metadata for the whole working day). But surely these recommendations may not suit your requirements.

Now, getting back to your issues, DROP TABLE and DROP VIEW seem being the only problems you experience, as we have already proved that the issue doesn't exist for procedures, there wasn't any test case for ALTER TABLE posted and there's no ALTER VIEW statement in the discussed FB versions.

Here I agree that you have a problem. As for DROP TABLE, the current situation will remain the same for the foreseeable future, like it nor not. However, it seems possible to implement in-memory versioning for views, thus allowing concurrent metadata modifications (this is what works for procedures, opposite to your suggestion that they just have usage counting disabled). But this is a somewhat major change and I doubt it can be introduced in point releases of the v2.x series. It should be researched in depth before making a decision.

This is unlikely to please you much, but so far I have only two suggestions for this ticket:

1) Resolve the bug as "won't fix" (i.e. everything works as designed).
2) Convert it into an improvement request asking for multi-version control for ALTER/DROP VIEW like it's done for procedures.

Commented by: Hilmar Brodner (syn)

I'm still wondering why we are the first people to stumble across this :)

Thank you for you detailed answer. I did some tests with FB2.5 (2 client connections, using IBExpert):
Results:
No problems with
- Altering views
- Altering tables
- Altering procedures
- Altering triggers
- Adding/removing foreign keys

"Object in use" with
- Dropping views
- Dropping tables

Which basically boils down to us having to wait till FB2.5 (hoping that RELAXED_ALIAS_CHECKING will still be available) - but being happy nonetheless as "ALTER VIEW" will resolve all our problems.

I also found interesting that adding a new field to view "v1" with client 1 resulted in client 2 being able to see the field in a "select * from v1" without prior commit.

Thank you very much for your time again. Great help!