From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | James Parks <james(dot)parks(at)meraki(dot)net> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: AfterTriggerSaveEvent() Error on altered foreign key cascaded delete |
Date: | 2016-10-26 19:42:53 |
Message-ID: | 24945.1477510973@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
James Parks <james(dot)parks(at)meraki(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | vjchem | 2016-10-26 20:49:25 | BUG #14398: Order of Joins results in different results returned |
Previous Message | James Parks | 2016-10-26 19:21:49 | AfterTriggerSaveEvent() Error on altered foreign key cascaded delete |