From: | "Tommy McDaniel" <tommstein(at)myway(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #5505: Busted referential integrity with triggers |
Date: | 2010-06-14 08:28:06 |
Message-ID: | 201006140828.o5E8S60P076700@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Fernando Cano | 2010-06-14 08:40:34 | BUG #5506: Error in the grammar of de joins |
Previous Message | Jan Merka | 2010-06-14 00:04:41 | Re: BUG #5504: cache lookup failed for function |