Rule execution bug

Started by Viacheslav N Tararinover 22 years ago2 messagesbugs
Jump to latest
#1Viacheslav N Tararin
taras@dekasoft.com.ua

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" ));
);

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Viacheslav N Tararin (#1)
Re: Rule execution bug

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