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

From Tom Lane
Subject Re: Cascading deletes with rules in 7.0.3: why doesn't this work?
Date
Msg-id 8005.986226746@sss.pgh.pa.us
Whole thread Raw
In response to Re: Cascading deletes with rules in 7.0.3: why doesn't this work?  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Cascading deletes with rules in 7.0.3: why doesn't this work?
List pgsql-general
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

pgsql-general by date:

Previous
From: Ben
Date:
Subject: Re: Log file to sql database
Next
From: "Richard Huxton"
Date:
Subject: Query rewriting: updates