Skip site navigation (1) Skip section navigation (2)

BUG #5505: Busted referential integrity with triggers

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 (view raw or flat)
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.

Responses

pgsql-bugs by date

Next:From: Fernando CanoDate: 2010-06-14 08:40:34
Subject: BUG #5506: Error in the grammar of de joins
Previous:From: Jan MerkaDate: 2010-06-14 00:04:41
Subject: Re: BUG #5504: cache lookup failed for function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group