Wierd rule problem

Started by Achilleas Mantziosabout 21 years ago3 messagesbugs
Jump to latest
#1Achilleas Mantzios
achill@matrix.gatewaynet.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Achilleas Mantzios (#1)
Re: Wierd rule problem

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

#3Hendrik Müller
user@invalid.domain
In reply to: Tom Lane (#2)
Re: Wierd rule problem

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?