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);

-- 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);

-- We found this inconsistency when using triggers like
-- 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;
------------------------------------------------------------------------------------------

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

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

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).

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