Thread: AfterTriggerSaveEvent() Error on altered foreign key cascaded delete
pgsql-bugs,
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:
--------------------------------------------------------------------------
CREATE TABLE foo (id bigint PRIMARY KEY);
CREATE TABLE bar (id bigint PRIMARY KEY, foo_id bigint);
CREATE TABLE baz (dummy bigint);
--------------------------------------------------------------------------
CREATE TABLE foo (id bigint PRIMARY KEY);
CREATE TABLE bar (id bigint PRIMARY KEY, foo_id bigint);
CREATE TABLE baz (dummy bigint);
-- 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;
INSERT INTO foo VALUES (1);
INSERT INTO bar VALUES (1,1);
ALTER TABLE bar ALTER CONSTRAINT foo_fkey DEFERRABLE INITIALLY DEFERRED;
INSERT INTO foo VALUES (1);
INSERT INTO bar VALUES (1,1);
-- We found this inconsistency when using triggers like
-- the one below, so I've copied the general idea here:
-- the one below, so I've copied the general idea here:
CREATE OR REPLACE FUNCTION insert_baz_row() RETURNS TRIGGER AS $insert_baz_row$
BEGIN
INSERT INTO baz VALUES (1);
RETURN NULL;
END;
$insert_baz_row$ LANGUAGE plpgsql;
CREATE TRIGGER bar_trigger
AFTER INSERT OR UPDATE OR DELETE ON bar
FOR EACH ROW EXECUTE PROCEDURE insert_baz_row();
DELETE FROM foo where id = 1;
-- cleanup
DROP TRIGGER IF EXISTS bar_trigger ON bar;
DROP FUNCTION IF EXISTS insert_baz_row();
DROP TABLE IF EXISTS baz;
DROP TABLE IF EXISTS bar;
DROP TABLE IF EXISTS foo;
------------------------------------------------------------------------------------------
BEGIN
INSERT INTO baz VALUES (1);
RETURN NULL;
END;
$insert_baz_row$ LANGUAGE plpgsql;
CREATE TRIGGER bar_trigger
AFTER INSERT OR UPDATE OR DELETE ON bar
FOR EACH ROW EXECUTE PROCEDURE insert_baz_row();
DELETE FROM foo where id = 1;
-- cleanup
DROP TRIGGER IF EXISTS bar_trigger ON bar;
DROP FUNCTION IF EXISTS insert_baz_row();
DROP TABLE IF EXISTS baz;
DROP TABLE IF EXISTS bar;
DROP TABLE IF EXISTS foo;
------------------------------------------------------------------------------------------
If you run the above code, you should get something like this:
# psql -U test -h localhost -d test -f test.sql # <-- test.sql contains the above code
CREATE TABLE
CREATE TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
INSERT 0 1
INSERT 0 1
CREATE FUNCTION
CREATE TRIGGER
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""
DROP TRIGGER
DROP FUNCTION
DROP TABLE
DROP TABLE
DROP TABLE
# psql -U test -h localhost -d test -f test.sql # <-- test.sql contains the above code
CREATE TABLE
CREATE TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
INSERT 0 1
INSERT 0 1
CREATE FUNCTION
CREATE TRIGGER
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""
DROP TRIGGER
DROP FUNCTION
DROP TABLE
DROP TABLE
DROP TABLE
However, if you swap out the foreign key constraint initialization methods (see "Method A" and "Method B" above) the AfterTriggerSaveEvent() error disappears:
CREATE TABLE
CREATE TABLE
CREATE TABLE
ALTER TABLE
INSERT 0 1
INSERT 0 1
CREATE FUNCTION
CREATE TRIGGER
DELETE 1
DROP TRIGGER
DROP FUNCTION
DROP TABLE
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
ALTER TABLE
INSERT 0 1
INSERT 0 1
CREATE FUNCTION
CREATE TRIGGER
DELETE 1
DROP TRIGGER
DROP FUNCTION
DROP TABLE
DROP TABLE
DROP TABLE
Given how Method A and Method B look so similar, I would normally expect them to have the same exact behavior (i.e. the schema is the same after each "Method" has completed).
Given how Method A succeeds, and how a row appears in the baz table after the DELETE command in the code, I assume that this particular use case is intended to be possible (i.e. after-delete triggers on tables affected by cascaded delete operations with deferred referential integrity checks), but I don't know if it is intended for Method A and Method B to have the same behavior (it really looks like it, though).
This behavior relies on the deferred nature of the foreign key constraint combined with the post-delete trigger to insert rows. Making the foreign key constraint immediately apply prevents the AfterTriggerSaveEvent() error message from appearing, and making the trigger a BEFORE DELETE trigger similarly prevents the AfterTriggerSaveEvent() error.
In diagnosing this, I have been using postgresql version 9.4.9 (as provided by the Debian Wheezy 64-bit package on apt.postgresql.org) and postgresql-client version 9.4.6 (as again provided through apt.postgresql.org).
Given how Method A succeeds, and how a row appears in the baz table after the DELETE command in the code, I assume that this particular use case is intended to be possible (i.e. after-delete triggers on tables affected by cascaded delete operations with deferred referential integrity checks), but I don't know if it is intended for Method A and Method B to have the same behavior (it really looks like it, though).
This behavior relies on the deferred nature of the foreign key constraint combined with the post-delete trigger to insert rows. Making the foreign key constraint immediately apply prevents the AfterTriggerSaveEvent() error message from appearing, and making the trigger a BEFORE DELETE trigger similarly prevents the AfterTriggerSaveEvent() error.
In diagnosing this, I have been using postgresql version 9.4.9 (as provided by the Debian Wheezy 64-bit package on apt.postgresql.org) and postgresql-client version 9.4.6 (as again provided through apt.postgresql.org).
I have attached the above code to this email in case it makes it easier.
Let me know if this is enough information to go off of -- the last thing I want to do is waste your time with an incomplete bug report.
Regards,
James
Attachment
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
Hi, I am facing the exact same bug when using circular references: create table m (f1 int primary key, f2 int); create table s (f2 int primary key, f1 int); -- Method A --> no error --alter table s add constraint fkey1 foreign key (f1) references m (f1) on delete cascade initially deferred; --alter table m add constraint fkey2 foreign key (f2) references s (f2) on delete cascade initially deferred; -- Method B --> error when workaround not used alter table s add constraint fkey1 foreign key (f1) references m (f1) on delete cascade on update no action; alter table s alter constraint fkey1 deferrable initially deferred; alter table m add constraint fkey2 foreign key (f2) references s (f2) on delete cascade on update no action; alter table m alter constraint fkey2 deferrable initially deferred; -- workaround to fix tgdeferrable and tginitdeferred on delete constraints, just uncomment next line -- update pg_trigger set tgdeferrable = false, tginitdeferred = false where tgtype = 9; begin transaction; insert into m values (1,1); insert into s values (1,1); commit; delete from m where f1=1; drop table s cascade; The same AfterTriggerSaveEvent() error occurs at the delete command. I found a workaround to fix the wrong booleans after reading: https://www.postgresql.org/message-id/8559.1289949074%40sss.pgh.pa.us Is there any update on this? best Martin -- View this message in context: http://www.postgresql-archive.org/AfterTriggerSaveEvent-Error-on-altered-foreign-key-cascaded-delete-tp5927857p5958509.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Re: AfterTriggerSaveEvent() Error on altered foreign key cascaded delete
From
Tom Lane
Date:
Martin <hier-bei-mir@gmx.net> writes:
> I am facing the exact same bug when using circular references:
I see no error when I try this example in HEAD. I think possibly you
are hitting a bug we fixed last year:
Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master [a522fc3d8] 2016-10-26 17:05:06 -0400
Branch: REL9_6_STABLE Release: REL9_6_2 [445035a6e] 2016-10-26 17:05:06 -0400
Branch: REL9_5_STABLE Release: REL9_5_6 [b53c841e5] 2016-10-26 17:05:06 -0400
Branch: REL9_4_STABLE Release: REL9_4_11 [3a9a8c408] 2016-10-26 17:05:06 -0400
Fix incorrect trigger-property updating in ALTER CONSTRAINT. The code to change the deferrability properties of
aforeign-key constraint updated all the associated triggers to match; but a moment's examination of the code that
createsthose triggers in the first place shows that only some of them should track the constraint's deferrability
properties. This leads to odd failures in subsequent exercise of the foreign key, as the triggers are fired at the
wrongtimes. Fix that, and add a regression test comparing the trigger properties produced by ALTER CONSTRAINT with
those you get by creating the constraint as-intended to begin with. Per report from James Parks. Back-patch to
9.4where this ALTER functionality was introduced. Report:
<CAJ3Xv+jzJ8iNNUcp4RKW8b6Qp1xVAxHwSXVpjBNygjKxcVuE9w@mail.gmail.com>
If you're not running a current minor release, please update.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Tom Lane-2 wrote > Martin < > hier-bei-mir@ > > writes: >> I am facing the exact same bug when using circular references: > > I see no error when I try this example in HEAD. I think possibly you > are hitting a bug we fixed last year: Yes indeed, upgrading to the latest minor version works for me too. Sorry I haven't expected this to be fixed without a message on the list, not so fast after the report in any case :-) Thank you very much! -- View this message in context: http://www.postgresql-archive.org/AfterTriggerSaveEvent-Error-on-altered-foreign-key-cascaded-delete-tp5927857p5958554.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs