BUG #5505: Busted referential integrity with triggers

The following bug has been logged online:

Bug reference: 5505
Logged by: Tommy McDaniel
Email address: tommstein(at)myway(dot)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.