BUG #6235: Delete fails with ON DELETE rule on inherited table - Mailing list pgsql-bugs

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;