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?