Thread: BUG #4396: Trigger event fired "UPDATE" when "DELETE" happening via foreign key
BUG #4396: Trigger event fired "UPDATE" when "DELETE" happening via foreign key
From
"Stephen Cuppett"
Date:
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?
Re: BUG #4396: Trigger event fired "UPDATE" when "DELETE" happening via foreign key
From
Tom Lane
Date:
"Stephen Cuppett" <stephen.cuppett@sas.com> writes:
> Description: Trigger event fired "UPDATE" when "DELETE" happening via
> foreign key
You're going to need to show a complete example if you want any help
with this. (My bet is that you've overlooked a trigger someplace...)
regards, tom lane
Re: BUG #4396: Trigger event fired "UPDATE" when "DELETE" happening via foreign key
From
Tom Lane
Date:
Stephen Cuppett <Stephen.Cuppett@sas.com> writes:
> I can upload the whole schema someplace, or is attaching a few files here okay?
What would be easiest from this end is a SQL script to create the
tables, insert any test data needed, and then reproduce the problem,
starting from an empty database. A "pg_dump -s" script is usually
a good starting place for making that.
regards, tom lane
Re: BUG #4396: Trigger event fired "UPDATE" when "DELETE" happening via foreign key
From
Tom Lane
Date:
Stephen Cuppett <Stephen.Cuppett@sas.com> writes:
> When A is deleted, B is deleted, E is set null. Then, D is deleted and E is deleted.
> FOR EVERY ROW AFTER UPDATE is run on E for rows that no longer exist.
> Not sure if that's a bug or not... not sure it would be undesirable under conditions for that not to run for those
tuplesthat are already gone.
Well, not firing the trigger would be pretty bad in some cases too. For
instance if you're trying to log updates in a history table, you'd not
want to miss updates just because they were immediately obsoleted.
Looking again at your example, that's exactly what your trigger is
doing, isn't it? Why in the world has your history table got an FK
back to the live tables? That presumes that you'll never delete any
objects ... or that you're willing to lose all history about them
as soon as they're deleted, which seems a pretty odd choice.
regards, tom lane
Re: BUG #4396: Trigger event fired "UPDATE" when "DELETE" happening via foreign key
From
Stephen Cuppett
Date:
SSBjYW4gdXBsb2FkIHRoZSB3aG9sZSBzY2hlbWEgc29tZXBsYWNlLCBvciBp cyBhdHRhY2hpbmcgYSBmZXcgZmlsZXMgaGVyZSBva2F5Pw0KDQpOb3RpY2Vk IHRoYXQgaWYgSSBkZWxldGUgZnJvbSB0aGUgZmVhdHVyZSBsZXZlbCwgSSBk byBub3QgaGl0IHRoZSBwcm9ibGVtLg0KDQotLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0NClN0ZXBoZW4gQ3VwcGV0dA0KU0FT wq4gQ2VydGlmaWVkIEFkdmFuY2VkIFByb2dyYW1tZXIgZm9yIFNBUznCrg0K VGVsOiArMSA5MTkgNTMxIDA2NTkg4paqIFN0ZXBoZW4uQ3VwcGV0dEBzYXMu Y29tDQp3d3cuc2FzLmNvbQ0KDQpTQVPCriDigKYgVEhFIFBPV0VSIFRPIEtO T1fCrg0KDQoNCi0tLS0tT3JpZ2luYWwgTWVzc2FnZS0tLS0tDQpGcm9tOiBU b20gTGFuZSBbbWFpbHRvOnRnbEBzc3MucGdoLnBhLnVzXQ0KU2VudDogV2Vk bmVzZGF5LCBTZXB0ZW1iZXIgMDMsIDIwMDggMTE6NDUgQU0NClRvOiBTdGVw aGVuIEN1cHBldHQNCkNjOiBwZ3NxbC1idWdzQHBvc3RncmVzcWwub3JnDQpT dWJqZWN0OiBSZTogW0JVR1NdIEJVRyAjNDM5NjogVHJpZ2dlciBldmVudCBm aXJlZCAiVVBEQVRFIiB3aGVuICJERUxFVEUiIGhhcHBlbmluZyB2aWEgZm9y ZWlnbiBrZXkNCg0KIlN0ZXBoZW4gQ3VwcGV0dCIgPHN0ZXBoZW4uY3VwcGV0 dEBzYXMuY29tPiB3cml0ZXM6DQo+IERlc2NyaXB0aW9uOiAgICAgICAgVHJp Z2dlciBldmVudCBmaXJlZCAiVVBEQVRFIiB3aGVuICJERUxFVEUiIGhhcHBl bmluZyB2aWENCj4gZm9yZWlnbiBrZXkNCg0KWW91J3JlIGdvaW5nIHRvIG5l ZWQgdG8gc2hvdyBhIGNvbXBsZXRlIGV4YW1wbGUgaWYgeW91IHdhbnQgYW55 IGhlbHANCndpdGggdGhpcy4gIChNeSBiZXQgaXMgdGhhdCB5b3UndmUgb3Zl cmxvb2tlZCBhIHRyaWdnZXIgc29tZXBsYWNlLi4uKQ0KDQogICAgICAgICAg ICAgICAgICAgICAgICByZWdhcmRzLCB0b20gbGFuZQ0KDQo=
Re: BUG #4396: Trigger event fired "UPDATE" when "DELETE" happening via foreign key
From
Stephen Cuppett
Date:
Tm0sICBJIHRoaW5rIEkgc2VlIHdoYXQgaXMgaGFwcGVuaW5nLg0KDQpJIHN0 YXJ0ZWQgdHJhY2luZyB0aGUgdHJhbnNhY3Rpb24gYW5kIHNlZSB0aGF0IGFu b3RoZXIgaGlnaCBsZXZlbCBvYmplY3QsIHdoZW4gZGVsZXRlZCwgZG9lcyBh biBPTiBERUxFVEUgU0VUIE5VTEwsIHRoZW4gdGhlIGl0ZW0gdXBkYXRlZCBp cyBkZWxldGVkLiAgU28gdGhlIEFGVEVSIFVQREFURSBpcyBydW4gZm9yIHRo ZSByb3cuLiBldmVuIHRob3VnaCBpdCdzIGEgZGVsZXRlZCByb3cgbGF0ZXIg aW4gdGhlIHNlcXVlbmNlLi4uDQoNCg0KU28gaXQgbG9va3MgbGlrZSB0aGlz DQoNCkEgLT4gQiA8LS18DQpBIC0+IEQgLT4gRQ0KDQpXaGVyZToNCg0KICAg ICAgICBCIHJlZmVyZW5jZXMgQSBPTiBERUxFVEUgQ0FTQ0FERQ0KICAgICAg RCByZWZlcmVuY2VzIEEgT04gREVMRVRFIENBU0NBREUNCiAgICAgIEUgcmVm ZXJlbmNlcyBEIE9OIERFTEVURSBDQVNDQURFDQogICAgICBFIHJlZmVyZW5j ZXMgQiBPTiBERUxFVEUgU0VUIE5VTEwNCg0KV2hlbiBBIGlzIGRlbGV0ZWQs IEIgaXMgZGVsZXRlZCwgRSBpcyBzZXQgbnVsbC4gIFRoZW4sIEQgaXMgZGVs ZXRlZCBhbmQgRSBpcyBkZWxldGVkLg0KDQpGT1IgRVZFUlkgUk9XIEFGVEVS IFVQREFURSBpcyBydW4gb24gRSBmb3Igcm93cyB0aGF0IG5vIGxvbmdlciBl eGlzdC4NCg0KTm90IHN1cmUgaWYgdGhhdCdzIGEgYnVnIG9yIG5vdC4uLiBu b3Qgc3VyZSBpdCB3b3VsZCBiZSB1bmRlc2lyYWJsZSB1bmRlciBjb25kaXRp b25zIGZvciB0aGF0IG5vdCB0byBydW4gZm9yIHRob3NlIHR1cGxlcyB0aGF0 IGFyZSBhbHJlYWR5IGdvbmUuDQoNClN0ZXZlDQoNCi0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KU3RlcGhlbiBDdXBwZXR0 DQpTQVPCriBDZXJ0aWZpZWQgQWR2YW5jZWQgUHJvZ3JhbW1lciBmb3IgU0FT OcKuDQpUZWw6ICsxIDkxOSA1MzEgMDY1OSDilqogU3RlcGhlbi5DdXBwZXR0 QHNhcy5jb20NCnd3dy5zYXMuY29tDQoNClNBU8KuIOKApiBUSEUgUE9XRVIg VE8gS05PV8KuDQoNCg0KLS0tLS1PcmlnaW5hbCBNZXNzYWdlLS0tLS0NCkZy b206IFRvbSBMYW5lIFttYWlsdG86dGdsQHNzcy5wZ2gucGEudXNdDQpTZW50 OiBXZWRuZXNkYXksIFNlcHRlbWJlciAwMywgMjAwOCAxMjoyNCBQTQ0KVG86 IFN0ZXBoZW4gQ3VwcGV0dA0KQ2M6IHBnc3FsLWJ1Z3NAcG9zdGdyZXNxbC5v cmcNClN1YmplY3Q6IFJlOiBbQlVHU10gQlVHICM0Mzk2OiBUcmlnZ2VyIGV2 ZW50IGZpcmVkICJVUERBVEUiIHdoZW4gIkRFTEVURSIgaGFwcGVuaW5nIHZp YSBmb3JlaWduIGtleQ0KDQpTdGVwaGVuIEN1cHBldHQgPFN0ZXBoZW4uQ3Vw cGV0dEBzYXMuY29tPiB3cml0ZXM6DQo+IEkgY2FuIHVwbG9hZCB0aGUgd2hv bGUgc2NoZW1hIHNvbWVwbGFjZSwgb3IgaXMgYXR0YWNoaW5nIGEgZmV3IGZp bGVzIGhlcmUgb2theT8NCg0KV2hhdCB3b3VsZCBiZSBlYXNpZXN0IGZyb20g dGhpcyBlbmQgaXMgYSBTUUwgc2NyaXB0IHRvIGNyZWF0ZSB0aGUNCnRhYmxl cywgaW5zZXJ0IGFueSB0ZXN0IGRhdGEgbmVlZGVkLCBhbmQgdGhlbiByZXBy b2R1Y2UgdGhlIHByb2JsZW0sDQpzdGFydGluZyBmcm9tIGFuIGVtcHR5IGRh dGFiYXNlLiAgQSAicGdfZHVtcCAtcyIgc2NyaXB0IGlzIHVzdWFsbHkNCmEg Z29vZCBzdGFydGluZyBwbGFjZSBmb3IgbWFraW5nIHRoYXQuDQoNCiAgICAg ICAgICAgICAgICAgICAgICAgIHJlZ2FyZHMsIHRvbSBsYW5lDQoNCg==
Re: BUG #4396: Trigger event fired "UPDATE" when "DELETE" happening via foreign key
From
Stephen Cuppett
Date:
WWVzLiAgSW4gbXkgY2FzZSBJIHdhbnQgdGhlIGhpc3RvcnkgZ29uZSB0b28u ICBLZWVwaW5nIHRoZSBoaXN0b3J5IHdvdWxkbid0IGh1cnQsIGJ1dCBpdCdz IGp1c3QgYSBwcmVmZXJlbmNlLg0KDQpJZiBJIGRpZCBrZWVwIHRoZSBoaXN0 b3J5IEkgZGVmaW5pdGVseSBkb24ndCB3YW50IHRoYXQgbGFzdCBVUERBVEUg d2hlcmUgRSBpcyBzZXQgbnVsbC4uLiBidXQgdGhhdCdzIGVhc2lseSBjaXJj dW12ZW50ZWQgdmlhIHRoZSB0cmlnZ2VyLg0KDQpUaGFua3MgZm9yIHRoZSBw b2ludGVyIHRvIGxvb2sgZm9yIHRoZSBvdGhlciBwYXRoIQ0KDQpUaGFua3Ms DQoNClN0ZXZlDQoNCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLQ0KU3RlcGhlbiBDdXBwZXR0DQpTQVPCriBDZXJ0aWZpZWQg QWR2YW5jZWQgUHJvZ3JhbW1lciBmb3IgU0FTOcKuDQpUZWw6ICsxIDkxOSA1 MzEgMDY1OSDilqogU3RlcGhlbi5DdXBwZXR0QHNhcy5jb20NCnd3dy5zYXMu Y29tDQoNClNBU8KuIOKApiBUSEUgUE9XRVIgVE8gS05PV8KuDQoNCg0KLS0t LS1PcmlnaW5hbCBNZXNzYWdlLS0tLS0NCkZyb206IFRvbSBMYW5lIFttYWls dG86dGdsQHNzcy5wZ2gucGEudXNdDQpTZW50OiBXZWRuZXNkYXksIFNlcHRl bWJlciAwMywgMjAwOCAxOjA5IFBNDQpUbzogU3RlcGhlbiBDdXBwZXR0DQpD YzogcGdzcWwtYnVnc0Bwb3N0Z3Jlc3FsLm9yZw0KU3ViamVjdDogUmU6IFtC VUdTXSBCVUcgIzQzOTY6IFRyaWdnZXIgZXZlbnQgZmlyZWQgIlVQREFURSIg d2hlbiAiREVMRVRFIiBoYXBwZW5pbmcgdmlhIGZvcmVpZ24ga2V5DQoNClN0 ZXBoZW4gQ3VwcGV0dCA8U3RlcGhlbi5DdXBwZXR0QHNhcy5jb20+IHdyaXRl czoNCj4gV2hlbiBBIGlzIGRlbGV0ZWQsIEIgaXMgZGVsZXRlZCwgRSBpcyBz ZXQgbnVsbC4gIFRoZW4sIEQgaXMgZGVsZXRlZCBhbmQgRSBpcyBkZWxldGVk Lg0KDQo+IEZPUiBFVkVSWSBST1cgQUZURVIgVVBEQVRFIGlzIHJ1biBvbiBF IGZvciByb3dzIHRoYXQgbm8gbG9uZ2VyIGV4aXN0Lg0KDQo+IE5vdCBzdXJl IGlmIHRoYXQncyBhIGJ1ZyBvciBub3QuLi4gbm90IHN1cmUgaXQgd291bGQg YmUgdW5kZXNpcmFibGUgdW5kZXIgY29uZGl0aW9ucyBmb3IgdGhhdCBub3Qg dG8gcnVuIGZvciB0aG9zZSB0dXBsZXMgdGhhdCBhcmUgYWxyZWFkeSBnb25l Lg0KDQpXZWxsLCBub3QgZmlyaW5nIHRoZSB0cmlnZ2VyIHdvdWxkIGJlIHBy ZXR0eSBiYWQgaW4gc29tZSBjYXNlcyB0b28uICBGb3INCmluc3RhbmNlIGlm IHlvdSdyZSB0cnlpbmcgdG8gbG9nIHVwZGF0ZXMgaW4gYSBoaXN0b3J5IHRh YmxlLCB5b3UnZCBub3QNCndhbnQgdG8gbWlzcyB1cGRhdGVzIGp1c3QgYmVj YXVzZSB0aGV5IHdlcmUgaW1tZWRpYXRlbHkgb2Jzb2xldGVkLg0KDQpMb29r aW5nIGFnYWluIGF0IHlvdXIgZXhhbXBsZSwgdGhhdCdzIGV4YWN0bHkgd2hh dCB5b3VyIHRyaWdnZXIgaXMNCmRvaW5nLCBpc24ndCBpdD8gIFdoeSBpbiB0 aGUgd29ybGQgaGFzIHlvdXIgaGlzdG9yeSB0YWJsZSBnb3QgYW4gRksNCmJh Y2sgdG8gdGhlIGxpdmUgdGFibGVzPyAgVGhhdCBwcmVzdW1lcyB0aGF0IHlv dSdsbCBuZXZlciBkZWxldGUgYW55DQpvYmplY3RzIC4uLiBvciB0aGF0IHlv dSdyZSB3aWxsaW5nIHRvIGxvc2UgYWxsIGhpc3RvcnkgYWJvdXQgdGhlbQ0K YXMgc29vbiBhcyB0aGV5J3JlIGRlbGV0ZWQsIHdoaWNoIHNlZW1zIGEgcHJl dHR5IG9kZCBjaG9pY2UuDQoNCiAgICAgICAgICAgICAgICAgICAgICAgIHJl Z2FyZHMsIHRvbSBsYW5lDQoNCg==