The following bug has been logged online:
Bug reference: 5505
Logged by: Tommy McDaniel
Email address: tommst...@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.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
