Re: BUG #6235: Delete fails with ON DELETE rule on inherited table

On 1/10/2011 3:35 AM, Tom Lane wrote:
>> If you delete from the base table then the DELETE succeeds (the row is
>> deleted), but the referenced row is not deleted. This might make sense to
>> someone who knows how inheritance is implemented, but it wasn't immediately
>> obvious to me. It would be nice if this worked, but if it doesn't, I think
>> the documentation should warn users about this trap.
> The reason this doesn't happpen is that inheritance expansion is done
> after rule expansion. While you could argue that rules on child tables
> should be considered too, it's not very clear to me that that would be
> well defined, and it's unlikely we'd make such a
> non-backwards-compatible change in rule behavior anyway. Personally
> I'd suggest using a trigger not a rule here, as it's not going to be
> subject to this problem and is generally the preferred way anyhow.
Thanks, I thought it might be something like that. I did end up using
triggers to do this, but it might be good to mention in the
documentation (in section 37 somewhere) how rules interact with
inheritance, to save others figuring this out. Also, if triggers are
"generally the preferred way" it would be good to explain this, too,
probably in section 37.6 - I wasn't aware of this and started with
RULEs, because they were much simpler to write.

>> The more serious problem is that if you try to delete from the derived table
>> the delete fails with an error:
>> ERROR: update or delete on table "referenced" violates foreign key
>> constraint "fk_derived_referenced" on table "derived"
>> DETAIL: Key (id)=(2) is still referenced from table "derived".
> The reason for that is that the DO ALSO action occurs before the main
> action, so you're trying to delete a "referenced" row that is in fact
> still referenced. One solution would be to declare the foreign key
> constraint as DEFERRABLE INITIALLY DEFERRED. (The same would be the
> case for a trigger, unless you made it an AFTER trigger.)
>
OK, now that you mention it, I did find a line in the documentation that
says "for ON UPDATE and ON DELETE rules, the original query is done
after the actions added by rules". However, the rule works fine as long
as the table doesn't INHERIT from anything. I'm not sure how it works,
but I think this behavior is quite counter-intuitive - I would never
have expected adding inheritance to break this RULE. I don't know enough
to suggest a way to fix this, but I can say as a user that it doesn't do
what I'd expect. Surely deleting referencing rows in other tables would
be a very common use for ON DELETE rules? I think it warrants an
explanation in the documentation, at least - again, maybe in the "rules
vs triggers" section, perhaps with some examples of scenarios in which
you'd use each.

Regards,

Evan Martin