Subject Re: [ib-support] RE: [Firebird-Java] OBJECT in use during Foreign key creation
Author Ann W. Harrison
At 12:04 PM 3/26/2003 -0800, Robert DiFalco wrote:

>If I have a connection to the database, connection "A".
>Then another process connects to the database (connection "B").
> -- "B" CAN create TABLES, but....
> -- "B" can NOT create any foreign keys between tables.

Sorry for the double posting, but this is an interesting problem
which we should fix in Firebird2. We can't in 1.5 because it
requires an ODS change. Here's the deal.

The intention of the designer of InterBase was to allow metadata
updates when running multi-user. That requires that certain
types of object notify all users that they just changed. For
example, every transaction holds "interest" locks on the
"index root page" of every table it has used.

When someone creates a new index, all transactions that have
referenced the table being indexed get a blocking signal from
the lock manager. They refresh their index information and
begin maintaining the index immediately. This works in classic
and superserver.

When foreign keys were added, someone should have introduced
a new page type to hold referential information for each table.
It wasn't. As a result, there's no way to let all transactions
know that they should begin following the foreign key rules.
Borland could, I suppose, have allowed foreign key definitions in
Superserver where inter-transaction communication is simple,
but instead, they decided to restrict foreign key definition to
single user mode and never got around to fixing the problem when
they did make ODS changes.


We have answers.