Re: Joined table view - multiple delete action rule

Lieven Van Acker wrote:
> Hi all,
>
> I'm coding a DB and I noticed the following strange thing:
>
> CREATE TABLE a (x integer PRIMARY KEY,y integer);
> CREATE TABLE b (x integer REFERENCES a, z integer, PRIMARY KEY (x,y))
>
> CREATE VIEW ab AS
>     SELECT a.x, a.y, b.z
>     FROM a,b
>     WHERE a.x=b.x;
>
> /* this -insert- seems to work */
>
> CREATE RULE ab_ins AS ON INSERT TO ab DO INSTEAD (
>     INSERT INTO a(x,y) VALUES (new.x, new.y);
>     INSERT INTO b(x,z) VALUES (new.x, new.z);
> );
>
> /* this -delete- does not work */
>
> CREATE RULE ab_del AS ON DELETE TO ab DO INSTEAD (
>     DELETE FROM b WHERE (x=old.x) AND (y=old.y);
>     DELETE FROM a WHERE (x=old.x);
> );
>
> Anyone has an explanation for this? I'm using PostgreSQL 7.0.3.

    It's leaving the tuples in "a" while deleting those from "b",
    right?

    Explanation is that the queries  generated  by  the  rewriter
    both  join  in  the  entire  view and that there is a command
    counter increment done between the deletes from "b" and  "a".
    Thus,  the  second delete doesn't see the "b" tuples any more
    and cannot find the tuples to delete.

    Not a bug, just a design issue about generic query rewriting.
    You  might  solve  the  problem  with a referential integrity
    constraint that does a cascaded delete from "b".


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com