Skip site navigation (1) Skip section navigation (2)

BUG #4396: Trigger event fired "UPDATE" when "DELETE" happening via foreign key

From: "Stephen Cuppett" <stephen(dot)cuppett(at)sas(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4396: Trigger event fired "UPDATE" when "DELETE" happening via foreign key
Date: 2008-09-03 13:42:35
Message-ID: 200809031342.m83DgZ77008023@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      4396
Logged by:          Stephen Cuppett
Email address:      stephen(dot)cuppett(at)sas(dot)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?

Responses

pgsql-bugs by date

Next:From: Rudolf LeitgebDate: 2008-09-03 14:31:30
Subject: BUG #4397: crash in tab-complete.c
Previous:From: DanielDate: 2008-09-03 09:03:05
Subject: BUG #4395: internal account lookup faulure

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group