Dear Firebird Architect Team ,

In firebird when we create table with FK throws error
when there are multiple connection. So I modified the
code of firebird where there is exclusive connection
check , now it works. But I like to understand why
there is such a check in FireBird code.
Following is the crisp of discussion I had with
members in firebird support forum. For entire thread
refer this link
http://groups.yahoo.com/group/firebird-support/messagesearch?query=multi%20connection%20meta%20data%20update

Note : Karthick stands for me and FB stands for
firebird-support forum members.

<start>
Karthick : when we have multiple connection and when
we try to create table with "foreign key" it throws
meta data error.
FB : when there are multiple connection meta data
update is not allowed , even there is no "interesting"
transaction ("interesting" = any transaction that is
in any state except Committed).
Karthick : drop table (including tables which has
foreign key) got dropped successfully when there are
multiple connection. I hope even this involves meta
data updates. Unlike create table this does not thrown
any errror.
FB: The reason for that is that when a foreign key
constraint is created, all users must immediately
start checking that
their operations are consistent with the constraint.
Karthick : I have debugged firebird code and commented
where exclusive connection check is made. Then with
firebird I was able to create table with FK in
multiple connection. Let me know what is the
significance in getting exclusive connection for
foreign key based index alone.
FB : The code that prevents creating FK prevents
database corruption. With your change, existing
connections will not be notified about creation of new
constraint, thus they will be able to insert
inconsistent data.
Karthick: Within in one transaction when I create
table (say T2) with FK other connection cannot insert
records to the table (say T2) since it is not aware of
T2. It can insert records only when connection one is
commited. In that case there cannot be any data
corruption. We checked the same with multi connection
, table creation in one connection and data insertion
in other. Like FK , PK also involves index creation
why there is exclusive connection check only in FK.
<last Reply> FB: There wouldn't be any problems when
the table and the FK are created within the same
transaction. But, what happens if they are not created
in the same transaction.
<end>

FK can be created for existing table in different
transaction by alter table alone (if i am wrong
correct me). In that case , when alter table is
executed to change FK of Table say 'A1' then if other
transaction tries to insert data etc to the same table
'A1' , it could be locked until alter table
transaction is committed. After alter transaction is
commited , second transaction lock can be removed and
if it commits the insert record , FB can throw error
that data is wrong. By this way of handling FireBird
need not check for exclusive connecton while creating
table.

So please do let me know the reason behind
exclusive connection check of FireBird. And whether my
fix is proper for 'create table' with FK at multi
connection scenario.

Herewith I have pasted the snippet of the modified
code and also the source file,

Modified file is uploaded at below link ,
http://f1.grp.yahoofs.com/v1/oORXQRS0rkja1_IVesi1-_EDSX4_sxxsoR5h0VryOueaOvK_KA0Xwz7VmNjkNVQwOHmYe1FSsAlf7WX5rjL2LCRF0L-3/dfw.cpp
snippet of the Modified source is given below
"/jrd/dfw.cpp".

/* Actually create the index */
//<multi connection meta data handling - start>
int i2,i1;
partner_relation = (JRD_REL) NULL_PTR;
if (idx.idx_flags & idx_foreign)
{
/* Get an exclusive lock on the database if the
index being
defined enforces a foreign key constraint. This
will prevent
the constraint from being violated during index
construction. */
i1=MET_lookup_partner(tdbb,
relation, &idx, (UCHAR*) work->dfw_name);
partner_relation =
MET_lookup_relation_id(tdbb,idx.idx_primary_relation,TRUE);

i2 = CCH_exclusive(tdbb, LCK_EX,
LCK_NO_WAIT);
//<multi connection meta data handling>
//error is not created even there is
multi connection
/* if (MET_lookup_partner(tdbb, relation,
&idx, (UCHAR*) work->dfw_name) &&
(partner_relation =
MET_lookup_relation_id( tdbb,
idx.idx_primary_relation,
TRUE))
&& !CCH_exclusive(tdbb,
LCK_EX, LCK_NO_WAIT))
{
ERR_post(gds_no_meta_update,
gds_arg_gds, gds_obj_in_use,
gds_arg_string,
partner_relation->rel_name,
0);
*/

/* CVC: Currently, the server doesn't enforce FK
creation more than at DYN level.
If DYN is bypassed, then FK creation succeeds and
operation will fail at run-time.
The aim is to check REFERENCES at DDL time instead
of DML time and behave accordingly
to ANSI SQL rules for REFERENCES rights.
For testing purposes, I'm calling SCL_check_index,
although most of the DFW ops are
carried using internal metadata structures that
are refreshed from system tables. */

/*if (partner_relation)
{*/
/* Don't bother if the master's owner is the same
than the detail's owner.
If both tables aren't defined in the same
session, partner_relation->rel_owner_name
won't be loaded hence, we need to be careful
about null pointers. */

/*if (!relation->rel_owner_name ||
!partner_relation->rel_owner_name
|| strcmp(relation->rel_owner_name,
partner_relation->rel_owner_name))
SCL_check_index(tdbb,
partner_relation->rel_name, idx.idx_id + 1,
SCL_sql_references);
}
}*/
//<multi connection meta data handling - END>
}

Thanks & reagrds,
S.Karthick

--- Daniel Rail <daniel@...> wrote:

> Hi,
>
> At September 24, 2004, 12:14, kartinku wrote:
>
>
> > Within in one transaction when I create table
> (say T2) with FK
> > other connection cannot insert records to the
> table (say T2) since it
> > is not aware of T2. It can insert records only
> when connection one is
> > commited. In that case there cannot be any data
> corruption. We checked
> > the same with multi connection , table creation in
> one connection and
> > data insertion in other.
>
> > Like FK , PK also involves index creation why
> there is exclusive
> > connection check only in FK.
>
> Sure, there wouldn't be any problems when the table
> and the FK are
> created within the same transaction. But, what
> happens if they are not
> created in the same transaction, and, to take it one
> step further, not
> on the same day? This is what Ivan is referring to.
>
> So the closest solution might be to check if the
> creation of the table
> and the creation of the FK are both within the same
> transaction(and
> probably with no COMMIT RETAINING), and then maybe
> your patch can be
> used within that context only. But, the code changes
> will have to make
> those additional checks(that both creating the table
> and FK are both
> within the same transaction). If both are not done
> within the same
> transaction, the current code has to be executed.
>
> But, this brings up another dilemma. A user would
> be asking why
> creating a table and an FK within the same
> transaction is allowed, but
> not in separate transactions when multiple
> connections to the database
> exists. And, most likely, someone will be reporting
> it as a bug,
> because of this inconsistency.
>
> I'm sure somebody from the development team will
> correct me if I'm
> wrong.
>
> --
> Best regards,
> Daniel Rail
> Senior System Engineer
> ACCRA Group Inc. (www.accra.ca)
> ACCRA Med Software Inc. (www.filopto.com)
>
>

_______________________________
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com