Cascading deletes with rules in 7.0.3: why doesn't this work?

From: Jeremy Radlow <jtr(at)sourcerers(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Cascading deletes with rules in 7.0.3: why doesn't this work?
Date: 2001-04-02 07:19:12
Message-ID: 4.2.2.20010402022030.0282b100@galaga.dreamhost.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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);

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Marek PUBLICEWICZ 2001-04-02 07:50:38 - dumping a tables' 'subtree'
Previous Message Thomas Lockhart 2001-04-02 06:49:53 Re: Call for platforms