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(at)Yahoo(dot)com #

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