Wierd rule problem

From: Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Wierd rule problem
Date: 2005-01-25 15:41:23
Message-ID: Pine.LNX.4.44.0501251727320.32584-100000@matrix.gatewaynet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2005-01-25 16:22:27 Re: Wierd rule problem
Previous Message Michael Meskes 2005-01-25 12:52:33 Re: BUG #1440: ecpg seg faults