Consider that
foodb=# \d parent
Table "public.parent"
Column | Type | Modifiers
--------+---------+--------------------------------------------------------
id | integer | not null default nextval('public.parent_id_seq'::text)
name | text |
Indexes:
"parent_pkey" primary key, btree (id)
foodb=# \d kid
Table "public.kid"
Column | Type | Modifiers
----------+---------+-----------------------------------------------------
id | integer | not null default nextval('public.kid_id_seq'::text)
parid | integer |
onlythis | boolean | not null default false
Indexes:
"kid_pkey" primary key, btree (id)
Foreign-key constraints:
"$1" FOREIGN KEY (parid) REFERENCES parent(id) ON DELETE CASCADE
Rules:
handle_delete_on_kid AS ON DELETE TO kid WHERE (NOT old.onlythis) DO
INSTEAD DELETE FROM parent WHERE (parent.id = old.parid)


INSERT INTO parent(name) VALUES('foo par');

CREATE RULE handle_delete_on_kid AS ON DELETE TO kid WHERE
(NOT old.onlythis) DO INSTEAD DELETE FROM parent
WHERE (parent.id = old.parid);

INSERT INTO kid (parid,onlythis) VALUES (1,'t');

DELETE from kid ;
DELETE 1

SELECT * from kid;
id | parid | onlythis
----+-------+----------
(0 rows)

SELECT * from parent;
id | name
----+---------
1 | foo par
(1 row)

(All good so far)

Now,

INSERT INTO kid (parid,onlythis) VALUES (1,'f');

DELETE from kid ;
DELETE 0

SELECT * from kid;
id | parid | onlythis
----+-------+----------
2 | 1 | f

Oops.... DELETE on parent or ON DELETE CASCADE did not work...

SELECT * from parent;
id | name
----+------
(0 rows)

Major Oops this time....

Referential integrity seems to have just been broken.

Of course its a rule thing, but shouldn't the trigger part
implementing RI be unaffected??

Any clues??

All of the above on
FreeBSD 5.3, pgsql 7.4.6, and
Debian 2.4.18-bf2.4, pgsql 7.4.2

Thanx

--
-Achilleus


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Achilleus Mantzios <achill (AT) matrix (DOT) gatewaynet.com> writes:
Bear in mind that the DELETEs issued by the ON DELETE CASCADE trigger
will be rewritten by this same rule.

There has been debate in the past whether this behavior is a feature or
a bug, but it's unlikely to change soon.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Tom Lane schrieb:

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings