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
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 |