The following bug has been logged online:
Bug reference: 4396
Logged by: Stephen Cuppett
Email address: stephen.cuppett@sas.com
PostgreSQL version: 8.3.3
Operating system: RHEL5.2 x86_64
Description: Trigger event fired "UPDATE" when "DELETE" happening via
foreign key
Details:
I have the following chain of tables:
release -> feature -> testcase -> execution -> execution_history
All tables have a parent_id column to the previous table with a foreign key
specified as ON DELETE CASCADE. One to Many the whole way down.
When I delete a release, the whole chain gets deleted.
I have this trigger defined:
CREATE OR REPLACE FUNCTION execution_history()
RETURNS trigger AS
$BODY$
BEGIN
IF (TG_OP = 'UPDATE') THEN
INSERT INTO execution_history (
execution_id, reporter, complete_units,
failed_units, blocked_units,
attempted_units, created, remote_rpt, remote_addr,
reported
) VALUES (
OLD.id, OLD.reporter, OLD.complete_units,
OLD.failed_units, OLD.blocked_units,
OLD.attempted_units, OLD.updated, OLD.remote_rpt, OLD.remote_addr,
OLD.reported
);
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
CREATE TRIGGER execution_history
AFTER UPDATE
ON executions
FOR EACH ROW
EXECUTE PROCEDURE execution_history();
Yet, when I go to delete a release, I get the following error:
Query: DELETE FROM "releases" WHERE "id" IN ('3')
Warning: SQL Error: ERROR: insert or update on table "execution_history"
violates foreign key constraint "execution_history_execution_id_fkey"
DETAIL: Key (execution_id)=(5830) is not present in table "executions".
CONTEXT: SQL statement "INSERT INTO execution_history ( execution_id,
reporter, complete_units, failed_units, blocked_units, attempted_units,
created, remote_rpt, remote_addr, reported ) VALUES ( $1 , $2 , $3 , $4 , $5
, $6 , $7 , $8 , $9 , $10 )" PL/pgSQL function "execution_history" line 3 at
SQL statement
I previously didn't have the TG_OP comparison, so I added the one to ensure
it was an "UPDATE". This doesn't appear to be correct behavior?
On the release and feature tables I have both an UPDATE for every row and
INSERT for every row trigger and they are *not* also firing. So I can't
figure out why this one would?
