Thread: Joined table view - multiple delete action rule
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
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
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
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
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
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