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
