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

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

Jeremy Radlow <jtr@sourcerers.com> writes:
> 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.

This seems to be a bug.  I haven't finished tracking it down yet, but
when you delete from A or B, the ON DELETE CASCADE trigger is going to
try to delete from B and/or C as well --- so the trigger will end up
invoking the ON DELETE rules for those tables and hence trying to insert
into delete_log.  This chain of events does indeed happen as expected,
but it seems that when we get down to the INSERT, the value being passed
for old.ax has become NULL.  Something rotten in parameter passing,
apparently.  (Deletion from C works, because that does not trigger any
cascaded deletes.)

Thanks for the report!  I'll try to fix it for 7.1...

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:
> Jeremy Radlow <jtr@sourcerers.com> writes:
>> 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.

> This seems to be a bug.

No, there's no bug.  The problem is the weird way you defined the
c_delete rule:

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

This depends on the assumption that there will be an entry in b that
matches the bx value of the c row being deleted.  Unfortunately, when
this delete is fired from an ON CASCADE DELETE from table b, the
relevant row of b is already gone (as far as this transaction is
concerned, anyway).  So the subselect yields no rows and you end up
trying to insert a null into delete_log.

Seems to me you want to record the A reference value directly in rows
of C, so that C's delete rule can look the same as B's.

            regards, tom lane

Thanks for the insight.  The problem is that logging ax is basically a
convenience for the end user.  (A is a user table)  I must reference B
from C.  If I also add an A reference to C, I want the A reference to
be automatically maintained at the database level.  And this is where
things start getting really convoluted...

I create a real C (c_real) and a view (c):

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

create view c as select * from c_real;

Then I add rules to make c masquerade as a real table which supports
inserts/deletes/updates.  And the insert rule changes ax:

create rule c_insert as
    on insert to c do instead
    insert into c_real values (new.cx, new.bx,
                         (select ax from b where b.bx = new.bx) );

Then there would be a trigger in B to change C whenever B's reference
to A changes.

I hope there's a simpler solution!

-Jeremy

At 11:52 AM 4/2/01 -0400, Tom Lane wrote:
>there's no bug.  The problem is the weird way you defined the
>c_delete rule:
>
>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));
>
>This depends on the assumption that there will be an entry in b that
>matches the bx value of the c row being deleted.  Unfortunately, when
>this delete is fired from an ON CASCADE DELETE from table b, the
>relevant row of b is already gone (as far as this transaction is
>concerned, anyway).  So the subselect yields no rows and you end up
>trying to insert a null into delete_log.
>
>Seems to me you want to record the A reference value directly in rows
>of C, so that C's delete rule can look the same as B's.