Il giorno gio, 19/07/2007 alle 22.55 +1000, Helen Borrie ha scritto:

> During restore, activating the constraint indexes is one of the last
> things to happen. If the restore fails at that point, the restore
> will abort. You will have your database but it will be in an
> uncertain state with regard to any of your indexes. Make sure you
> create a verbose log of restores and **check it** before deleting the
> old database and kicking in the new one.

Thanks Helen,

yes, sure. The automated scripts I mentioned do surely have some problem
checking the restored db, and I'll try to fix them...

> (Please don't say you are using gbak -R for restores...)

Uhm, what do you mean here?

>
> >In the other cases the unique contraint index is active, but it
> >nevertheless allowed the insertion of dups.
>
> If you are talking about an index on a character column then No. If
> the index for the UNIQUE constraint is active, then somehow you are
> letting in data that contains subtle differences. One space
> character will do it. On CHAR fields, one *trailing* space will do it.

No, it's been a long time since I last used other-than-int PKs, and in
this case, there's no characters fields even. My tables are of the
following kind:

CREATE TABLE StatisticTable
(
Data Date_T NOT NULL,
IDA LargeID_T NOT NULL,
IDO LargeID_T NOT NULL,
IDM LargeID_T NOT NULL,
IDT SmallID_T NOT NULL,
IDN SmallID_T NOT NULL,
IDP LargeID_T NOT NULL,
IDZ LargeID_T NOT NULL,
IDCT SmallID_T NOT NULL,

Quantita Quantity_T,
Tempo Time_T
);

CREATE UNIQUE INDEX StatisticTable_P ON StatisticTable
(IDA,IDO,IDM,IDT,IDN,IDP,IDZ,IDCT,Data);

> I'm sure that using SPs isn't causing the duplicates, per se. The
> disabled constraint index would certainly, regardless of whether your
> inserts were happening via SPs or DSQL. So would lost index pages
> (thanks to forced writes being off). And, if you've got user-entered
> data in constraint columns, don't use CHAR types. One tap on the
> space bar will cause a duplicated value to become a
> non-duplicate...only one problem that goes along with non-atomic keys.

The table is updated *only* by a procedure (a set of SPs), that reads
another "tree" of tables and maintain a statistic, updated once every
while. So there's no user interaction, and there's just one process that
wakes up and runs the procedure.

As said, the SP in question uses this kind of code

INSERT INTO StatByABC (A,B,C,SUMOFX,SUMOFY)
VALUES (:A, :B, :C, :SUMOFX, :SUMOFY)
WHEN SQLCODE -803 DO
UPDATE StatByABC
SET SUMOFX = SUMOFX + :SUMOFX,
SUMOFY = SUMOFY + :SUMOFY
WHERE A=:A AND B=:B AND C=:C;

and I was referring to this when I said that, with a missing or
corrupted unique index on (A,B,C) this is clearly the culprit in the
presence of dups key.

The point is thus understanding how, at some point, that piece of code
could introduce a dup key: as said, I have two different cases, one
where there's no daily backup/restore cycle, and where I see the index
is there, and active, but with dup keys (deactivating/reactivating raise
an error, of course); other case where the restore happened, and
deactivated the index (and thus the next "statistical day" is full of
dups).

Thank you for your advice,
bye, lele.