BUG #5505: Busted referential integrity with triggers - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5505
Logged by:          Tommy McDaniel
Email address:      tommstein@myway.com
PostgreSQL version: 8.4.4
Operating system:   Kubuntu 9.10
Description:        Busted referential integrity with triggers
Details:

Let us create a table as follows:

CREATE TABLE table_1 (
  field_1 character varying(20) PRIMARY KEY
);

Let us create another table as follows:

CREATE TABLE table_2 (
  field_2 character varying(20) PRIMARY KEY REFERENCES table_1 ON UPDATE
CASCADE
);

Let us also create a trigger to disable UPDATEs on table_2:

CREATE FUNCTION cancel_update() RETURNS trigger AS $$
  BEGIN
    RETURN NULL;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER cancel_update_trigger BEFORE UPDATE ON table_2
  FOR EACH ROW EXECUTE PROCEDURE cancel_update();

Let us now insert some data:

INSERT INTO table_1 VALUES ('val_1');

INSERT INTO table_2 VALUES ('val_1');

It does what we expect:

testdb=# SELECT * FROM table_1;
 field_1
---------
 val_1
(1 row)

testdb=# SELECT * FROM table_2;
 field_2
---------
 val_1
(1 row)

Now we decide to change the value in table_1:

UPDATE table_1 SET field_1 = 'val_2' WHERE field_1 = 'val_1';

Now let's see what values we have in the database:

testdb=# SELECT * FROM table_1;
 field_1
---------
 val_2
(1 row)

testdb=# SELECT * FROM table_2;
 field_2
---------
 val_1
(1 row)

And, we have now broken referential integrity. I expected that ON UPDATE
CASCADE would ignore the trigger. Failing that, I would still expect the
foreign key constraint to be checked and raise an error. Neither appears to
be happening, so we're silently getting busted referential integrity. This
makes me sad.