Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group