Subject RE: [firebird-support] RE: Duplicate primary keys v1.5.1
Author Rick DeBay
> my guess is that you created the table in the same transaction as you
ran the procedure

Nope. I created the table using DataBase Workbench, and then I used
JBoss to bulk load the data.
I never modify the schema through the application server. I don't even
let my EJBs do that under the covers.

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Friday, October 22, 2004 9:58 PM
Subject: Re: [firebird-support] RE: Duplicate primary keys v1.5.1

At 02:23 PM 22/10/2004 -0400, you wrote:

>I used a stored procedure to import a lot of data, and got quite a
>surprise when I viewed it. Every imported record was in the table,
>including duplicates! Looking at the statistics for the primary key,
>shows that it wasn't used. I wasn't aware that a PK could be turned
>off, or how to turn it back on now that it's off. Gfix -v -n didn't
>return any messages.

A PK can't be "turned off" except by dropping it. You can
deactivate/activate indexes using ALTER INDEX xxx INACTIVE |
ACTIVE. However, you can't deactivate the indexes that the system
to enforce constraints.

Try this:
create table testpk
(id integer not null,
data varchar(10),
constraint pk_id primary key (id)
using asc index pk_testpk);
alter index pk_testpk inactive;

ISC ERROR CODE:335544351

unsuccessful metadata update
action cancelled by trigger (3) to preserve data integrity
Cannot deactivate primary index

(must send that isc message through to Dmitry. It seems to have
an extra "S"...)

As for your "problem", my guess is that you created the table in the
transaction as you ran the procedure, without committing the DDL.
the transaction could see the entries in RDB$RELATIONS, RDB$FIELDS and
RDB$RELATION_FIELDS, it allowed the inserts into the uncommitted
tables; but, at that point, the system couldn't apply the constraint
create the index, because the metadata for the table were uncommitted.

It would have excepted on the duplicates when the transaction was
committed, so apparently you never did commit the transaction until
you deleted the rows. In that case, when the transaction was finally
committed, the table was empty, the constraint was finally created and
there was nothing amiss. There was probably no need to drop and
the constraint.

btw, it's a little known fact that gstat works by reading directly from
database file, not by connecting to the database. Hence, its reports
not in any transaction context - it can't distinguish between committed
uncommitted data, i.e. it's a dinkum "dirty read". The stats you showed

are quite consistent with what I'm guessing you did.

Just in case you've missed it in the rush - you're not meant to create
metadata and add data to the structures in the same transaction. DDL
DML should *always* be in separate transactions. The next ODS is going
enforce that, I believe. Widespread abuse of the EXECUTE STATEMENT
to pass DDL through stored procedures has augmented the risks of people
doing things they didn't oughta. (The rule about not using DDL in
procedure modules is actually there by design.)


Yahoo! Groups Links