Postgres Pro
Downloads
Home   >   mailing lists

Thread: RULE: ON DELETE doesn't stack deletes

RULE: ON DELETE doesn't stack deletes

From
Malcolm Hutty
Date:
According to the manual you can stack multiple queries in a RULE:

CREATE RULE name AS ON event
     TO object [ WHERE condition ]
     DO [ INSTEAD ] action

where action can be:

NOTHING
|
query
|
( query ; query ... )
|
[ query ; query ... ]


This seems to work provided 'query' is not "DELETE"; if it is, only
the first one is executed.

My installation is Postgres 7.2.2, using the binary package shipped in
Redhat 8.0

[malcolm@localhost STRUCT]$ uname -sr
Linux 2.4.18-18.8.0


Here is a test case:

CREATE TABLE A
(a_data int);

CREATE TABLE B
(b_data int);

CREATE VIEW V_AB AS
SELECT a_data,b_data FROM A,B;

CREATE RULE R_DEL_AB AS
ON DELETE TO V_AB
DO INSTEAD
(
DELETE FROM A WHERE a_data=OLD.a_data;
DELETE FROM B WHERE b_data=OLD.b_data;
);

INSERT INTO A values (1);
INSERT INTO B values (1);


test=# select * from V_AB;
  a_data | b_data
--------+--------
       1 |      1
(1 row)


test=#delete from v_ab;
DELETE 0

test=# select * from a;
  a_data
--------
(0 rows)

test=# select * from b;
  b_data
--------
       1
(1 row)

Re: RULE: ON DELETE doesn't stack deletes

From
Rod Taylor
Date:
Confirmed this problem on cvs-tip.

Replacing the DO INSTEAD ( DELETE....) with DO INSTEAD (INSERT...)
allows multiple insert statements which function fine using OLD.a_data
and OLD.b_data.

So it must be something else.


Could it be because once the DELETE FROM A has run the tuple no longer
exists in the view?


On Wed, 2002-11-20 at 10:49, Malcolm Hutty wrote:
> According to the manual you can stack multiple queries in a RULE:

> CREATE RULE R_DEL_AB AS
> ON DELETE TO V_AB
> DO INSTEAD
> (
> DELETE FROM A WHERE a_data=OLD.a_data;
> DELETE FROM B WHERE b_data=OLD.b_data;
> );

--
Rod Taylor <rbt@rbt.ca>

Re: RULE: ON DELETE doesn't stack deletes

From
Stephan Szabo
Date:
On Wed, 20 Nov 2002, Malcolm Hutty wrote:

> According to the manual you can stack multiple queries in a RULE:
>
> CREATE RULE name AS ON event
>      TO object [ WHERE condition ]
>      DO [ INSTEAD ] action
>
> where action can be:
>
> NOTHING
> |
> query
> |
> ( query ; query ... )
> |
> [ query ; query ... ]
>
>
> This seems to work provided 'query' is not "DELETE"; if it is, only
> the first one is executed.

I think it's something like:
delete from v_ab
 turns into something equivalent to the two statements (I don't know
what the actual form of the queries is however)
 delete from a where a_data=ANY(select a_data from v_ab);
 delete from b where b_data=ANY(select b_data from v_ab);

So when the second runs there is no matching rows (having already had all
the a rows deleted).

Re: RULE: ON DELETE doesn't stack deletes

From
Tom Lane
Date:
Rod Taylor <rbt@rbt.ca> writes:
> Could it be because once the DELETE FROM A has run the tuple no longer
> exists in the view?

Precisely.  OLD is only a macro for the view, and the first delete
changes what's in the view ...

            regards, tom lane