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, it
>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 creates
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);
commit;
alter index pk_testpk inactive;

ISC ERROR CODE:335544351

ISC ERROR MESSAGE:
unsuccessful metadata update
MODIFY RDB$INDICESS failed
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 acquired
an extra "S"...)

As for your "problem", my guess is that you created the table in the same
transaction as you ran the procedure, without committing the DDL. Because
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 and
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 after
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 recreate
the constraint.

btw, it's a little known fact that gstat works by reading directly from the
database file, not by connecting to the database. Hence, its reports are
not in any transaction context - it can't distinguish between committed and
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 and
DML should *always* be in separate transactions. The next ODS is going to
enforce that, I believe. Widespread abuse of the EXECUTE STATEMENT syntax
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.)

./hb