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