Обсуждение: Wierd rule problem

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

Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> 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);

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

Tom Lane schrieb:
> 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.
Why postgres does not show this trigger for a better understanding?