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

"Evan Martin" <postgresql(at)realityexists(dot)net> writes:
> Defined a RULE that deletes from a child table whenever a parent table row
> is deleted. If the parent (referencing) table INHERITS from another table
> this rule doesn't work as intended, whether you delete from the base or
> derived table.

> 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.

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

regards, tom lane