Understanding trigger behavior

Question

I'm having trouble with a trigger which I can't get my head around.

I have a table called TABLE_A with an AFTER INSERT trigger that executes a procedure. The procedure basically does the following three steps….

• Process the data in the TABLE_A row to create a new row in TABLE_B.
• Updates the TABLE_A row to indicate it’s been processed.
• Updates a summary TABLE_C to increment a record count.

It implements this with three sequential SQL statements. Although I've simplified the first statement, there are no intermediate steps here, just three bits of SQL one after the other:-

--Insert new record that includes the record ID of the source.
--NOTE - recIdB is an autogenerated ID from a database sequence.
INSERT INTO TABLE_B(idA, lots_of_other_fields….
VALUES (new.recordIdA, lots_of_other_values…..
RETURNING recIdB INTO recentRecIdB;

--Update original record in TABLE_A to confirm it’s been processed.
UPDATE TABLE_A SET processed = true WHERE recIdA = new.recIdA;

--Update the summary table with the new record identifier.
UPDATE TABLE_C SET newRecords = (newRecords +1), recentId = recentRecIdB;

The trigger normally runs happily for hours on end processing millions of newly inserted records. Occasionally it will go squiffy and simply not execute the 2nd two update statements.

I know that the insert into TABLE_B has worked, because I can see the record with the unique ID from TABLE_A. I know that UPDATE TABLE_A hasn't executed (not successfully anyway) because the 'processed' flag isn't set. I know that the trigger can't have failed between the INSERT and the 1st UPDATE because there is no code between the two statements i.e. nothing to fail. I can't see how UPDATE TABLE_A can possibly fail because it's so damn simple! there are no calculations to go wrong, a single row is being updated based on a unique identifier provided by the database itself - not a value I've calculated.

My only explanation for UPDATE TABLE_A not being executed is that the code failed before it got there. However I don't see how this can be the case because if the preceding INSERT INTO TABLE_B had failed then there would be no new record in TABLE_B.

My final guess is that first part of INSERT INTO TABLE_B is working (hence the new record I can see afterward) but the final part (RETURNING recIdB INTO recentRecIdB;) has failed. Is this possible? If the trigger bombs-out at this point it would explain what I'm seeing - a new record in TABLE_B but no updated record in TABLE_A

The following may also be relevant.

• The code has been working (unchanged) for years without problem. The code still works 90% of the time but now experiences periods of a few hours at a time when it never gets past the 1st statement
• TABLE_A and TABLE_B both use a record ID generated from the database as follows:-

.

recida integer NOT NULL DEFAULT nextval('table_a_recida_seq'::regclass)
recidb integer NOT NULL DEFAULT nextval('table_b_recidb_seq'::regclass)

• There are no other processes running on the database. The process adding records to TABLE_A is doing nothing else, this trigger is the only running code.
• The two updates UPDATE TABLE_B and UPDATE TABLE_C either both work or both fail together - hence my assumption that the error occurs between INSERT TABLE_B and the 1st update.

The final thing that may be relevant is that the record identifiers used by TABLE_A and TABLE_B (recIdA and recIdB shown above) are both INTEGERS and are both over 2 billion. However, they are both still several million records away from the maximum INTEGER value of 2,147,483,647. (this is a current issue and I'll be updating to BIGINT asap)

Any suggestions? I'm already looking into how to raise messages to help de-bug triggers but any ideas would be appreciated, could the INSERT statement be inserting a record correctly and then failing at RETURNING X INTO Y? Could the problem be down to the sequences approaching maximum size?

UPDATE - The source records are being inserted in blocks of INSERTS involving a couple of hundred records at a time. (several hundred INSERTS per transaction)

Answer 1

I cannot figure out what exactly is the problem, but I suggest that you add something like this after the update of table_a:

IF NOT FOUND THEN
   RAISE EXCEPTION 'row % in table_a not found!', NEW.recida;
END IF;

Then at least you can keep the inconsistency from happening.

If the trigger fails, all the effects of the statement and the trigger will be rolled back, because they are always in a single transaction.