Hi!

I am developing an application which uses Firebird 1.5.2.4731 as a server.
It is running fine in about 30 installations, but recently I have run into a
serious database issue which only happens in one location, but it happens
firly often. My problem is that I can't even imagine what's causing it:

At some point the application runs a stored procedure that performs some
calculations on one table (table SOURCE with primary key field DATA_KEY).
Part of this operation involves creating a backup of the processed rows in a
different table (table BACKUP, same structure as SOURCE). That is, the
procedure does something like this:

INSERT INTO BACKUP SELECT * FROM SOURCE WHERE condition;
FOR SELECT * FROM SOURCE WHERE condition INTO :key, :field1, :field2, ... DO
BEGIN
... various processing
UPDATE SOURCE SET some_fields = value_which_invalidates_condition WHERE
DATA_KEY=:key;
END

The problem is, that at some record of the INSERT statement, Firebird
returns a "duplicate primary key" error AND COMMITS THE RECORDS BEFORE THAT.
What I don't understand is:

1) How can I get a primary key violation when basically copying a part of a
different table (with the same structure). I made certain I am not inserting
a row twice, before you ask.

2) Even if I did make some mistake and I am really violating the primary
key, I don't understand how come the database engine commits the partially
completed procedure.

Any insight would be helpful.

Thanks,

Pepak