Cascading deletes with rules in 7.0.3: why doesn't this work? - Mailing list pgsql-general

From Jeremy Radlow
Subject Cascading deletes with rules in 7.0.3: why doesn't this work?
Date
Msg-id 4.2.2.20010402022030.0282b100@galaga.dreamhost.com
Whole thread Raw
Responses Re: Cascading deletes with rules in 7.0.3: why doesn't this work?
List pgsql-general
In the following example, I'm trying to log all deletions from any of three
tables.  The only information I'm interested in recording is the index from
table A which the deleted row referenced.

When I try to delete a row from A or B:

testdb=> delete from b;
ERROR:  ExecAppend: Fail to add null value in not null attribute ax
testdb=> delete from c;
DELETE 1
testdb=> delete from b;
DELETE 1

What's really odd is that if I drop the *_delete rules, and try 'delete
from a' in the same psql session, I get that ExecAppend error.  But
'delete from a' works in a new session:

testdb#1=> delete from a;
ERROR:  ExecAppend: Fail to add null value in not null attribute ax
testdb#1=> drop rule a_delete;
DROP
testdb#1=> drop rule b_delete;
DROP
testdb#1=> drop rule c_delete;
DROP
testdb#1=>
[1]+  Stopped       psql -d testdb -U tester
postgres# psql -d testdb -U tester
testdb#2=> delete from a;
DELETE 1
testdb#2=> \q
postgres# fg
testdb#1=> insert into a values (1);
INSERT 1961282 1
testdb#1=> insert into b values (1,1);
INSERT 1961283 1
testdb#1=> insert into c values (1,1);
INSERT 1961284 1
testdb#1=> delete from a;
ERROR:  ExecAppend: Fail to add null value in not null attribute ax
testdb#1=>


Does anyone know what the problem is?

Thanks,

Jeremy

-- cut here --

create table a (
     ax serial,
     primary key (ax)
);

create table b (
     bx serial,
     ax int references a on delete cascade,
     primary key (bx)
);

create table c (
     cx serial,
     bx int references b on delete cascade,
     primary key (cx)
);

create table delete_log (
    ax int not null default '0',
    delete_timestamp timestamp default current_timestamp
);

create rule a_delete as
on delete to a do
    insert into delete_log (ax) values (old.ax);

create rule b_delete as
on delete to b do
    insert into delete_log (ax) values (old.ax);

create rule c_delete as
on delete to c do
    insert into delete_log (ax)
           values ((select ax from b where b.bx = old.bx));


insert into a values (1);
insert into b values (1,1);
insert into c values (1,1);


pgsql-general by date:

Previous
From: will trillich
Date:
Subject: Re: function to operate on same fields, different records?
Next
From: Marek PUBLICEWICZ
Date:
Subject: - dumping a tables' 'subtree'