overlapping rules can let you break referential integrity - Mailing list pgsql-bugs

Step by step how to reproduce:


-- nodes
CREATE TABLE nodes
(
    node_id                serial,
    CONSTRAINT nodes_pkey        PRIMARY KEY (node_id)
)
WITHOUT OIDS;


-- domains
CREATE TABLE domains
(
    domain_id            int NOT NULL,
    domain_is_public        bool NOT NULL default false,
    CONSTRAINT domains_pkey     PRIMARY KEY (domain_id),
    CONSTRAINT domains_domain_id_fkey FOREIGN KEY (domain_id)
        REFERENCES nodes (node_id) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE CASCADE
)
WITHOUT OIDS;


-- drop_domain: drop the node and rely on the delete cascade
CREATE OR REPLACE RULE drop_domain
AS
    ON DELETE TO domains
    DO INSTEAD DELETE FROM nodes WHERE node_id = OLD.domain_id;


-- public_domain_delete_protect: add delete protection
CREATE OR REPLACE RULE public_domain_delete_protect
AS
    ON DELETE TO domains
    WHERE    domain_is_public = true
    DO INSTEAD NOTHING;


-- version check
select version();
-- 8.1.1 on i686-pc-mingw32 yada yada (standard binary on WinXP SP2)

-- create a node
insert into nodes default values;
-- 1 row affected, normal

-- create a domain
insert into domains (domain_id, domain_is_public)
values (currval('nodes_node_id_seq'), true);
-- 1 row affected, normal

-- delete the domain
delete from domains;
-- 1 row affected, not normal
-- 0 expected because of public_domain is write protected

-- lookup nodes
select * from nodes;
-- 0 rows, normal since the write protection didn't work

-- lookup domaisn
select * from domains;
-- 1 row
-- ouch! this piece of data is now corrupt


I'm not familiar with the pgsql internals, but it looks as if:

1. delete on domains
2. rewritten as delete on nodes
   via drop_domain
3. triggers cascade delete on domains
   via foreign key
4. rewritten as do nothing <-- missing integrity check and/or rollback here
   via public_domain_delete_protect (things work fine without this step)


Best,
Denis