Thread: BUG #5505: Busted referential integrity with triggers

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.

"Tommy McDaniel" <tommstein@myway.com> writes:
> Let us also create a trigger to disable UPDATEs on table_2:
> ...
> And, we have now broken referential integrity.

Yup, this is not a bug, it's a feature.  Triggers fire on
referential-integrity updates.  (If they didn't, you could not for
example have a logging trigger log RI actions.)  If you don't want
to break RI, you'd better think more carefully about what your
trigger does.

            regards, tom lane

I can understand firing the triggers. But what's up with not checking that =
the foreign key constraint is met? If the user has to manually ensure that =
values maintain referential integrity, why have foreign keys at all? The wh=
ole point of foreign keys is to make the database ensure referential integr=
ity is maintained instead of having to do it manually.

Tommy McDaniel




-----Original Message-----
From: "Tom Lane" [tgl@sss.pgh.pa.us]
Date: 06/14/2010 08:13 AM
To: "Tommy McDaniel" <tommstein@myway.com>
CC: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #5505: Busted referential integrity with triggers=
=20

"Tommy McDaniel" <tommstein@myway.com> writes:
> Let us also create a trigger to disable UPDATEs on table_2:
> ...
> And, we have now broken referential integrity.

Yup, this is not a bug, it's a feature.  Triggers fire on
referential-integrity updates.  (If they didn't, you could not for
example have a logging trigger log RI actions.)  If you don't want
to break RI, you'd better think more carefully about what your
trigger does.

            regards, tom lane

"Tommy McDaniel" <tommstein@myway.com> writes:
> I can understand firing the triggers. But what's up with not checking that the foreign key constraint is met? If the
userhas to manually ensure that values maintain referential integrity, why have foreign keys at all? The whole point of
foreignkeys is to make the database ensure referential integrity is maintained instead of having to do it manually. 

[ shrug... ]  The database is doing its best.  Do you really want us to
incur the extra overhead of checking that a trigger didn't screw things
up?  Exactly how far should that go?  For instance, maybe we have to
check that the trigger didn't queue a subsequent event that will make
the undesired change after we look?  I can assure you that far more
people would complain about the useless overhead induced by rechecking
than will complain about the fact that they can write triggers that will
fire on RI updates.

            regards, tom lane