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

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.