Thread: Foreign key constaint can be broken

Dear,

I found a way to break a foreign key constraint in PostgreSQL. I discussed
this bug on a dutch discussion board and multiple people tried this on
multiple versions.

When I create the following tables:


CREATE TABLE a
(
id SERIAL NOT NULL,
foo CHAR(100) NOT NULL,
PRIMARY KEY(id)
);

CREATE TABLE b
(
id INT NOT NULL,
bar CHAR(20) NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(id) REFERENCES a(id) ON DELETE CASCADE
);

and add the following rule to table b:

CREATE OR REPLACE RULE delete_b AS ON DELETE
TO b
DO INSTEAD
DELETE FROM a WHERE OLD.id = id;

When you try to delete a row on table b:
DELETE FROM b WHERE id = 1;
The record from table a disappears, but the record in table b is still
there. Of course this is a very stupid construction, but I would expect some
kind of error / warning message instead. Now it is possible to corrupt your
data.

Best regards,
Mark Kazemier

Mark Kazemier <mark.kazemier@gmail.com> writes:
> I found a way to break a foreign key constraint in PostgreSQL
> [ ie, make a rule that defeats an ON DELETE CASCADE operation ]

This isn't a bug, it's just the way things work.  Rules (and triggers)
apply to the commands that implement foreign key updates, so a poorly
written rule can make those queries do the wrong thing.  The rule can
make your regular queries do the wrong thing too, so it's not like you'd
be fine if it were done some other way.  There are a number of real
applications that would be broken if rules/triggers *didn't* apply to
FK queries --- for example, using a trigger to implement logging --- so
we've concluded this is the most useful way for it to be done.

            regards, tom lane

>> I found a way to break a foreign key constraint in PostgreSQL
>> [ ie, make a rule that defeats an ON DELETE CASCADE operation ]
>
> This isn't a bug, it's just the way things work.  Rules (and triggers)
> apply to the commands that implement foreign key updates, so a poorly
> written rule can make those queries do the wrong thing.  The rule can
> make your regular queries do the wrong thing too, so it's not like you'd
> be fine if it were done some other way.  There are a number of real
> applications that would be broken if rules/triggers *didn't* apply to
> FK queries --- for example, using a trigger to implement logging --- so
> we've concluded this is the most useful way for it to be done.

It may suggest that a rule may have a optionnal specifier to tell the
context in which it should be applied, for instance :

   CREATE RULE ... ON [ ALL | INTERNAL | EXTERNAL ] UPDATE TO ...

Where "INTERNAL" would tag foreign key stuff while "EXTERNAL" would be
only the user stuff.

I'm not sure it would not add to the confusion, though.

--
Fabien.