Wierd rule problem - Mailing list pgsql-bugs

From Achilleus Mantzios
Subject Wierd rule problem
Date
Msg-id Pine.LNX.4.44.0501251727320.32584-100000@matrix.gatewaynet.com
Whole thread Raw
Responses Re: Wierd rule problem  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
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

pgsql-bugs by date:

Previous
From: Sam Hahn
Date:
Subject: Re: BUG #1432: type "cube" does not exist
Next
From: Hendrik Müller
Date:
Subject: Re: BUG #1429: stats tests fails