Thread: Joined table view - multiple delete action rule

Joined table view - multiple delete action rule

From
Lieven Van Acker
Date:
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.

Greetings,

Lieven


Re: Joined table view - multiple delete action rule

From
Jan Wieck
Date:
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


Re: Joined table view - multiple delete action rule

From
Lieven Van Acker
Date:
Hi Andrew,

I'm sorry but I was a but sloppy in typing. The right version is

CREATE TABLE a (x integer PRIMARY KEY,y integer);
CREATE TABLE b (x integer REFERENCES a, z integer, PRIMARY KEY (x,z))

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 (z=old.z);
    DELETE FROM a WHERE (x=old.x);
);

So I already got the answer:

the query rewriter only takes the first action after which the joined row
doesn't exist anymore and thus the second delete doesn't affect the tables
anymore.

I'm now trying to rewrite this as a cascading delete rule on table a;

Greetings

Lieven


Re: Joined table view - multiple delete action rule

From
Lieven Van Acker
Date:
Hi Jan and others,

thanks for the answer, this clears up the symptom.

In trying to rewrite the rules, I'm still facing the same problem.
I'll try to simplify the rules and tables (it's emulating the OO concept as
a is the parent and b and c are inherited from a)

CREATE TABLE a (x integer PRIMARY KEY,y integer);
CREATE TABLE b (x integer REFERENCES a, z integer, PRIMARY KEY (x));
CREATE TABLE c (x integer REFERENCES a, v integer, PRIMARY KEY(x));

CREATE VIEW ab AS
    SELECT a.x, a.y, b.z
    FROM a,b
    WHERE a.x=b.x;

CREATE VIEW ac AS
    SELECT a.x, a.y, c.v
    FROM a,c
    WHERE a.x=c.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);
);

/* cascading delete on a to b and c */

CREATE RULE a_del AS ON DELETE TO a DO (
    DELETE FROM b WHERE (x=old.x);
    DELETE FROM c WHERE (x=old.x);
);

/* delete on view doesn't work */

CREATE RULE ab_del AS ON DELETE TO ab DO INSTEAD
    DELETE FROM a WHERE (x=old.x)
;

The last rule seems to have the same effect as the original rule where I
implemented the cascading delete on the delete rule for the ab-view.
So I suppose the query rewriter will end up executing the same sequence of
queries.

Now, is there a way to implement this delete on the joined view?

Thanks,

Lieven


Re: Joined table view - multiple delete action rule

From
Jan Wieck
Date:
Lieven Van Acker wrote:
> Hi Jan and others,
>
> thanks for the answer, this clears up the symptom.
>
> In trying to rewrite the rules, I'm still facing the same problem.
> I'll try to simplify the rules and tables (it's emulating the OO concept as
> a is the parent and b and c are inherited from a)
>
> CREATE TABLE a (x integer PRIMARY KEY,y integer);
> CREATE TABLE b (x integer REFERENCES a, z integer, PRIMARY KEY (x));
> CREATE TABLE c (x integer REFERENCES a, v integer, PRIMARY KEY(x));
>
> CREATE VIEW ab AS
>     SELECT a.x, a.y, b.z
>     FROM a,b
>     WHERE a.x=b.x;
>
> CREATE VIEW ac AS
>     SELECT a.x, a.y, c.v
>     FROM a,c
>     WHERE a.x=c.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);
> );
>
> /* cascading delete on a to b and c */
>
> CREATE RULE a_del AS ON DELETE TO a DO (
>     DELETE FROM b WHERE (x=old.x);
>     DELETE FROM c WHERE (x=old.x);
> );
>
> /* delete on view doesn't work */
>
> CREATE RULE ab_del AS ON DELETE TO ab DO INSTEAD
>     DELETE FROM a WHERE (x=old.x)
> ;
>
> The last rule seems to have the same effect as the original rule where I
> implemented the cascading delete on the delete rule for the ab-view.
> So I suppose the query rewriter will end up executing the same sequence of
> queries.
>
> Now, is there a way to implement this delete on the joined view?

    That's  not  what I suggested, it's still using rules for the
    cascaded delete. I meant to setup a  FOREIGN  KEY  constraint
    with  an  ON  DELETE CASCADE referential action. Add to table
    "b" and "c"

        ON DELETE CASCADE

    after the REFERENCES keyword and leave out the  entire  a_del
    rule.


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


Re: Joined table view - multiple delete action rule

From
"Gyozo Papp"
Date:
Hello,

I came up against the same (or similar) difficulty some month ago.
Then Tom Lane suggested me to write a simple sql or plpgsql function which handles the multiple delete action.
(BTW, maybe you don't want to use the on delete cascade referential integrity in some cases.)

Sorry, but I can't find anywhere the answer sent me.
But here's the source code of the function I wrote:

create function f_privateauto_del(ID) returns BOOLEAN as
'DECLARE ID INTEGER;
BEGIN
 SELECT INTO ID  c_id FROM t_one WHERE t_one.c_id = $1;
 IF NOT FOUND THEN RAISE EXCEPTION ''Illegal parameter''; END IF; -- this isn't necessary

 DELETE FROM t_one WHERE oid = $1;
 DELETE FROM t_two WHERE c_id = ID;
 RETURN true::BOOLEAN;
END;'
language 'plpgsql';

You can figure out from my original letter (below) how much your problems  is similar to the one I had.

Papp Gyozo
- pgerzson@freestart.hu