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

The following bug has been logged online:

Bug reference:      6235
Logged by:          Evan Martin
Email address:      postgresql@realityexists.net
PostgreSQL version: 9.1.1
Operating system:   Windows 7 x64
Description:        Delete fails with ON DELETE rule on inherited table
Details:

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

There is no other row in the parent table that would violate the foreign key
- it's being referenced only by the row that's being deleted. I would expect
this to succeed and to delete the referenced row.

The following script illustrates the problem:



-- Drop

DROP TABLE IF EXISTS base CASCADE;
DROP TABLE IF EXISTS referenced CASCADE;

-- Schema

CREATE TABLE referenced
(
  id serial NOT NULL,
  value character varying(100),
  CONSTRAINT pk_referenced PRIMARY KEY (id)
);

CREATE TABLE base
(
  id serial NOT NULL,
  name character varying(100),
  CONSTRAINT pk_base PRIMARY KEY (id)
);

CREATE TABLE derived
(
  derived_referenced_id integer,
  CONSTRAINT pk_derived PRIMARY KEY (id),
  CONSTRAINT fk_derived_referenced FOREIGN KEY (derived_referenced_id)
REFERENCES referenced (id)
)
INHERITS (base);

-- The rule

CREATE OR REPLACE RULE rl_derived_delete_referenced
AS ON DELETE TO derived DO ALSO
DELETE FROM referenced r WHERE r.id = old.derived_referenced_id;


-- Some test data

INSERT INTO referenced (id, value)
VALUES (1, 'referenced 1');

INSERT INTO referenced (id, value)
VALUES (2, 'referenced 2');


INSERT INTO derived (id, name, derived_referenced_id)
VALUES (10, 'derived 10', 1);

INSERT INTO derived (id, name, derived_referenced_id)
VALUES (20, 'derived 20', 2);

-- Issue 1: delete from base - deletes the "base" and "derived" rows, but
not "referenced"

DELETE FROM base
WHERE id = 10;

SELECT *
FROM referenced;

-- Issue 2: delete from derived - fails with:
-- update or delete on table "referenced" violates foreign key constraint
"fk_derived_referenced" on table "derived"

DELETE FROM derived
WHERE id = 20;

"Evan Martin" <postgresql@realityexists.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

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