Thread: BUG #4992: "BEFORE DELETE" trigger puts the database in an inconsistent state

The following bug has been logged online:

Bug reference:      4992
Logged by:          Stratsimir Kolchevski
Email address:      stratsimir@bastun.net
PostgreSQL version: 8.4.0
Operating system:   Linux
Description:        "BEFORE DELETE" trigger puts the database in an
inconsistent state
Details:

http://www.postgresql.org/docs/8.4/static/plpgsql-trigger.html:
"Row-level triggers fired BEFORE can return null to signal the trigger
manager to skip the rest of the operation for this row (i.e., subsequent
triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this
row)"

In the following simplified example table "b" has a record that does not
exist in "a", although we have a FOREIGN KEY on the "i" column.

postgres=# CREATE TABLE a(i INTEGER PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for
table "a"
CREATE TABLE
postgres=# CREATE TABLE b(i INTEGER REFERENCES a(i) ON DELETE CASCADE);
CREATE TABLE
postgres=# CREATE OR REPLACE FUNCTION test_func() RETURNS TRIGGER AS $$
postgres$# BEGIN
postgres$# RETURN NULL;
postgres$# END
postgres$# $$ LANGUAGE 'plpgsql';
CREATE FUNCTION
postgres=# CREATE TRIGGER test_trig BEFORE DELETE ON b FOR EACH ROW EXECUTE
PROCEDURE test_func();
CREATE TRIGGER
postgres=# INSERT INTO a(i) VALUES(1);
INSERT 0 1
postgres=# INSERT INTO b(i) VALUES(1);
INSERT 0 1
postgres=# DELETE FROM a;
DELETE 1

And the result is:

postgres=# SELECT * FROM b;
 i
---
 1
(1 row)

postgres=# SELECT * FROM a;
 i
---
(0 rows)

"Stratsimir Kolchevski" <stratsimir@bastun.net> writes:
> [ BEFORE DELETE triggers can suppress foreign key updates too ]

This is not a bug, it's operating as designed.  If we didn't do that
it would foreclose many useful applications of triggers.

            regards, tom lane