AfterTriggerSaveEvent() Error on altered foreign key cascaded delete - Mailing list pgsql-bugs

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