Re: AfterTriggerSaveEvent() Error on altered foreign key cascaded delete

James Parks <james.parks@meraki.net> writes:
> I *believe* we've found a slight inconsistency with the handling of foreign
> keys in the situation of cascaded deletes. Here is a POC to motivate the
> discussion:
> ...
> -- Method A
> -- ALTER TABLE bar ADD CONSTRAINT foo_fkey FOREIGN KEY (foo_id) REFERENCES
> foo (id) ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY
> DEFERRED;
> -- Method B
> ALTER TABLE bar ADD CONSTRAINT foo_fkey FOREIGN KEY (foo_id) REFERENCES foo
> (id) ON DELETE CASCADE ON UPDATE NO ACTION;
> ALTER TABLE bar ALTER CONSTRAINT foo_fkey DEFERRABLE INITIALLY DEFERRED;

> If you run the above code, you should get something like this:
> psql:test.sql:24: ERROR:  AfterTriggerSaveEvent() called outside of query
> CONTEXT:  SQL statement "DELETE FROM ONLY "public"."bar" WHERE $1
> OPERATOR(pg_catalog.=) "foo_id""

> However, if you swap out the foreign key constraint initialization methods
> (see "Method A" and "Method B" above) the AfterTriggerSaveEvent() error
> disappears:

Hm.  A quick test suggests that Method B leaves the deferrability flags
for the FK's triggers set incorrectly.  That's a bug for sure.  I'm not
certain offhand if the error message indicates an additional problem
(ie, should the triggers work when set up this way?)

            regards, tom lane