Thread: Rule execution bug

For the next schema rule "d_analysis_plan" perform only first DELETE
statement, second statement not performed.

Environment:
---------------------------------
PostgreSQL 7.4beta4
CPU: 2PIII800
ASP Linux 7.2 2.4.19-13 smp
gcc 2.96

SCHEMA:
------------------------------------------
create table gl.T_ACCOUNT (
ID                   INTEGER                        not null,
PLAN_ID              INTEGER                        not null,
CODE                 ltree                          not null check (
CODE != ''::ltree),
DESCRIPTION          VARCHAR(255)                   not null check (
DESCRIPTION != ''),
STATUS_ID            INTEGER                        not null,
VERSION              SMALLINT                       not null
);

create table gl.T_ACCOUNT_PLAN (
ID                   INTEGER                        not null,
CODE                 VARCHAR(17)                    not null check (
CODE != '' ),
DESCRIPTION          VARCHAR(255)                   not null check (
DESCRIPTION != ''),
VERSION              SMALLINT                       not null
);

create table gl.T_SUB_PLAN (
ID                   INTEGER                        not null,
ACCOUNT_ID           INTEGER                        not null,
MIRROR_ID            INTEGER
);

alter table gl.T_ACCOUNT
    add constraint FK1_ACCOUNT foreign key (PLAN_ID)
       references gl.T_ACCOUNT_PLAN (ID);

alter table gl.T_SUB_PLAN
    add constraint FK1_SUB_PLAN foreign key (ACCOUNT_ID)
       references gl.T_ACCOUNT (ID);

alter table gl.T_SUB_PLAN
    add constraint FK2_SUB_PLAN foreign key (ID)
       references gl.T_ACCOUNT_PLAN (ID);


CREATE RULE d_analysis_plan AS  ON DELETE TO analysis_plan DO INSTEAD
(DELETE
    FROM gl.t_sub_plan
   WHERE (t_sub_plan.id = old.id );

  DELETE
    FROM gl.t_account_plan
   WHERE ((t_account_plan.id = old.id )
     AND (t_account_plan."version" = old."version" ));
);

Viacheslav N Tararin <taras@dekasoft.com.ua> writes:
> For the next schema rule "d_analysis_plan" perform only first DELETE
> statement, second statement not performed.

You did not show us the definition of "analysis_plan", but I'll bet it's
a view joining t_sub_plan and t_account_plan.  If so, this is not a bug.
After the first DELETE, there is no longer anything in the view matching
the id, and so the second DELETE finds nothing to delete.

You might have better luck using ON DELETE CASCADE foreign-key
references to accomplish the results you want.

            regards, tom lane